Using Syniti Connect with Collect

Syniti Connect

Using Syniti Connect with Collect

September 2017 - Applies to DSP 6.6.1 and above

Introduction

Starting with DSP 6.6.1, Syniti Connect is now integrated into the platform allowing it to be used from Collect, Integrate and page events.  This article shows how data can be read into the DSP using Collect and Syniti Connect.

The DSP installation manual, requirements and sizing guide, and online help have been updated to include Syniti Connect information.

Please note that Syniti Connect is an add-on for DSP at extra cost and you will need to engage your Syniti account manager if you wish to use Syniti Connect.

Prerequisites

If you plan to build the content yourself, you will require the following

1. A DSP instance.  This must be version 6.6.1 or above.
2. Access to an external system.  In the example below we use a Salesforce tenant.
3. A Boomi account provisioned by Syniti.  The account must have a minimum of 2 licensed connections.  One connection is required for Salesforce, and one connection for the target SQL Server database.
4. A Boomi local Atom.

We assume the reader has a basic working knowledge of DSP, SQL Server Management Studio, and Boomi development.

Configuring DSP & Syniti Connect to import Salesforce Account Data

The following steps describe how to build and configure a process to bring Salesforce Account data into the DSP.  The steps are applicable to any external system, the only difference will be in the Boomi processes that are created.

Create and register a target database

In SQL Server Management Studio create a new database.  In this example, we will extract data from an instance of Salesforce with North American content, so we called the database dgSalesforceNA.

Register the target database in DSP by navigating to Common -> Configuration -> Data Source Registry.  The data source type should be Target System Database.

Create the target data model

Tables must exist in the target database (in our example, dgSalesforceNA) before executing IGUC processes.  One way to do this is to use the Syniti Connect content from Syniti that builds a DSP system type and target tables using metadata imported from a system (Salesforce, SuccessFactors, etc.).

If you don’t have a system type and target tables created then they need to be created.  This creation can be done manually or via a System Type.  In our example, we use the DDL in Appendix A to create an Account target table that matches the Salesforce Summer ‘17 release data structure.

Register Boomi account

The Boomi account being used must be provisioned by Syniti as it requires specific content in it.  Registering the account in DSP is done by adding a new data source, navigate to Common -> Configuration -> Data Source Registry to add the account. The data source type should be IG Universal Connect.

Once you have entered your account ID and credentials, you can select a default atom.  The default atom can be overridden in Collect.

Build and deploy the Boomi Process

Prior to building the Boomi processes for Collect you must configure the Syniti Connect template provided by Syniti in your Boomi account.  Refer to this document for configuring and using the Syniti Connect template - Configuring and Using the Syniti Connect Template.  If you have a limited number of Boomi connections the template can be configured using the connection that will be used for the target SQL Server database (in our example, dgSalesforeNA).

Log into your Boomi account and create connections for your external system (in our case Salesforce) and your target SQL Server database.

Replicate the Syniti Connect template (as described in the template article above) and save it with a name following the guidelines in the Syniti Connect best practices document - Syniti Connect Best Practices.  We used the name COL SFDC Account Query.

Add a connector to query the Account data from Salesforce.

Add a connector to insert data into the Account table into your target database.

In the database operation we used the Import feature to dynamically build the database profile to match the table structure and configure a dynamic INSERT.

NOTE: Refer to the KB article Boomi Data Profile Generates Invalid SQL for a workaround that you may need. 

Add a Map shape to map the Salesforce data to the database table.

The attributes on the source and target will have the same name making the map straightforward (you could use Boomi suggest).  Some target columns might not have a matching attribute from the source profile, these can be left unmapped.

Save and test the process.  When executing the process in test mode, you must provide a UUID as the value for the dynamic process property LinkID (this is described in the template article referenced above).  If you have more than 100 Accounts in Salesforce you will have to limit the number of objects returned in the Salesforce connector operation to 100 or less.

The process should execute successfully.

Once the process has been successfully tested, it must be deployed.  Only deployed processes can be used with Syniti Connect.  If you set the query limit for testing, ensure you remove the limit before deploying.

Register and execute the Boomi Process via Collect

Navigate to Collect -> Targets and add the target database previously registered in Common.

Add the previously registered Boomi account as a source, select IG Universal Connect as the connection type.  At this point, you can specify a system type.  If you have created a system type and the target tables for it, select your system type, if not, it can be left blank and accept any warnings related to the system type.  We left the system type empty.

The Atom can be overridden on the Advanced Settings tab of the Target Source vertical view, this can be useful when switching between development, test, and production environments.

For the Target Source, click on the Tables button to add a table for this source.

When adding a table you must type the name exactly to match the name of the table in the target database.  The table must exist in the target database.

On the Advanced Settings tab of the vertical view select the Boomi process built for this package.

Save, then click Build and Refresh.

DSP executes the Boomi Process which reads data from Salesforce and inserts it into the Account table.  DSP shows the duration and record count if it executes successfully.

If the process does not execute, ensure you have selected an Atom that is attached to the Boomi environment you deployed the process into.

Appendix A - DDL to create Account target table

CREATE TABLE [dbo].[Account](

[Id] [nvarchar](18) NULL,

[IsDeleted] [nvarchar](5) NULL,

[MasterRecordId] [nvarchar](18) NULL,

[Name] [nvarchar](255) NULL,

[Type] [nvarchar](40) NULL,

[ParentId] [nvarchar](18) NULL,

[BillingStreet] [nvarchar](255) NULL,

[BillingCity] [nvarchar](40) NULL,

[BillingState] [nvarchar](80) NULL,

[BillingPostalCode] [nvarchar](20) NULL,

[BillingCountry] [nvarchar](80) NULL,

[BillingLatitude] [decimal](18, 15) NULL,

[BillingLongitude] [decimal](18, 15) NULL,

[BillingGeocodeAccuracy] [nvarchar](40) NULL,

[ShippingStreet] [nvarchar](255) NULL,

[ShippingCity] [nvarchar](40) NULL,

[ShippingState] [nvarchar](80) NULL,

[ShippingPostalCode] [nvarchar](20) NULL,

[ShippingCountry] [nvarchar](80) NULL,

[ShippingLatitude] [decimal](18, 15) NULL,

[ShippingLongitude] [decimal](18, 15) NULL,

[ShippingGeocodeAccuracy] [nvarchar](40) NULL,

[Phone] [nvarchar](40) NULL,

[Fax] [nvarchar](40) NULL,

[AccountNumber] [nvarchar](40) NULL,

[Website] [nvarchar](255) NULL,

[PhotoUrl] [nvarchar](255) NULL,

[Sic] [nvarchar](20) NULL,

[Industry] [nvarchar](40) NULL,

[AnnualRevenue] [decimal](18, 0) NULL,

[NumberOfEmployees] [int] NULL,

[Ownership] [nvarchar](40) NULL,

[TickerSymbol] [nvarchar](20) NULL,

[Description] [nvarchar](max) NULL,

[Rating] [nvarchar](40) NULL,

[Site] [nvarchar](80) NULL,

[OwnerId] [nvarchar](18) NULL,

[CreatedDate] [nvarchar](20) NULL,

[CreatedById] [nvarchar](18) NULL,

[LastModifiedDate] [nvarchar](20) NULL,

[LastModifiedById] [nvarchar](18) NULL,

[SystemModstamp] [nvarchar](20) NULL,

[LastActivityDate] [nvarchar](20) NULL,

[LastViewedDate] [nvarchar](20) NULL,

[LastReferencedDate] [nvarchar](20) NULL,

[Jigsaw] [nvarchar](20) NULL,

[JigsawCompanyId] [nvarchar](20) NULL,

[CleanStatus] [nvarchar](40) NULL,

[AccountSource] [nvarchar](40) NULL,

[DunsNumber] [nvarchar](9) NULL,

[Tradestyle] [nvarchar](255) NULL,

[NaicsCode] [nvarchar](8) NULL,

[NaicsDesc] [nvarchar](120) NULL,

[YearStarted] [nvarchar](4) NULL,

[SicDesc] [nvarchar](80) NULL,

[DandbCompanyId] [nvarchar](18) NULL,

[CustomerPriority__c] [nvarchar](255) NULL,

[SLA__c] [nvarchar](255) NULL,

[Active__c] [nvarchar](255) NULL,

[NumberofLocations__c] [decimal](3, 0) NULL,

[UpsellOpportunity__c] [nvarchar](255) NULL,

[SLASerialNumber__c] [nvarchar](10) NULL,

[SLAExpirationDate__c] [nvarchar](20) NULL

)