Installation

To install and run SSIS Integration Toolkit for Salesforce.com, your system must have the following components installed. 

  • A supported SSIS design-time or run-time, which can be one of the following:
    • SSIS 2016 (since v5.0)
    • SSIS 2014
    • SSIS 2012
    • SSIS 2008 R2
    • SSIS 2008
    • SSIS 2005

    For SSIS runtime, the installation should be done by using the corresponding SQL Server installation media (most likely in CD/DVD format), and you must select the "Integration Services" component during the installation (as shown below).

    Integration Services Runtime Selection

    Note that when using SQL Server 2014, a cumulative update is required (a recent service pack, such as one of the following, is more preferred) in order to run our software during runtime.

    For SSIS design-time, you should be installing the version of SSDT (SQL Server Data Tools) or BIDS (Business Intelligence Development Studio) that aligns with the SQL Server version that you plan to use for your final deployment (the runtime).

    • When targeting SSIS 2016, you would use SSDT for Visual Studio 2015 available for download at https://msdn.microsoft.com/mt186501.aspx
      • Note that this SSDT installation can be used to target SSIS 2014 or 2012 as well.
    • When targeting SSIS 2014, you can use either one of the following:
    • When targeting SSIS 2012, you can use any one of the following:
    • When targeting SSIS 2008 R2 or earlier, you would have to install the Business Intelligence Development Studio that is shipped with the installation media of the corresponding SQL Server version.

    Starting in the first quarter of calendar year 2016, Salesforce began disabling the TLS 1.0 encryption protocol. Once disabled, any inbound connections to or outbound connections from Salesforce will need to use the TLS 1.1 or TLS 1.2 encryption protocol. In order to suppoft TLS 1.2 on SQL Server 2008 R2 or earlier, you need to install a hotfix at the following URLs.

    1. https://support.microsoft.com/kb/3154517 (Windows Vista and Windows Server 2008)
    2. https://support.microsoft.com/kb/3154518 (Windows 7 and Windows Server 2008 R2)
    3. https://support.microsoft.com/kb/3154519 (Windows Server 2012)
    4. https://support.microsoft.com/kb/3154520 (Windows 8.1 and Windows Server 2012 R2)
    5. https://support.microsoft.com/kb/3156421 (Windows 10, Build 1511)
    • Note that no equivalent hotfix is available for Windows 10, Build 1507, an update of the operating system will be needed. The hotfix is built-in for Windows 10, Build 1607 and Windows Server 2016. For further details about this particular security change, please visit Salesforce announcement at https://help.salesforce.com/articleView?id=000221207

    Note that the above hotfix is only useful if you are using v7.0 release or later. Without the hotfix on the relevant Windows version (or if you are using a version prior to v7.0), your connection from SSIS 2008 R2 or earlier will stop working once the SSL security option is activated in Salesforce, indicating "The requested security protocol is not supported.". As an alternative to the hotfix installation, you can upgrade to SSIS 2012 or later.

  • .NET Framework
  • Windows Installer 4.5
    • If you are using Windows Server 2008, Windows Server 2012 family of operating systems or later (including Windows Vista, Windows 7, Windows 8, Windows Server 2012 R2, Windows Server 2016), you do not need to do anything since the latest Windows Installer has been installed by the operating system.
    • If you are using Windows Server 2003 family of operating systems (including Windows XP), you should install Windows Installer 4.5 by downloading it from Microsoft website.

When you have confirmed that your system satisfies the above prerequisites, you can navigate to KingswaySoft website at http://www.kingswaysoft.com to download the installation package. In the download page, you will find two download links. One is for x86 and one is for x64 system. Make sure to choose and download the right bit for your system. 

Download Options

Once you click the download link, save the zip file which contains the .msi file. Double click the .msi file to start the setup. A security warning may appear. You can safely accept the warning and continue the setup

The SSIS Integration Toolkit for Salesforce Setup Wizard will appear. Click ‘Next’ to continue. 

Salesforce Integration Installation

Select the checkbox to agree to the terms of the End-User License Agreement. Click 'Next' to continue.

Salesforce Integration Installation

Next, choose one of the three setup types that best suits your needs.

Salesforce Integration Installation

Click ‘Install’ to being the installation.

Salesforce Integration Installation

To complete the installation, select 'Finish'.

Salesforce Integration Installation

Setup a Salesforce Connection using Salesforce Connection Manager

SSIS Integration Toolkit Salesforce Connection Manager is an SSIS connection manager that can be used to establish connections with Salesforce Server. 

Salesforce connection manager allows you to specify how you want to connect to Salesforce.com. 

To add a Salesforce connection to your SSIS package, right-click in Connection Managers area, and choose "New Connection..." from the context menu. You will be prompted the following window, where you can choose "Salesforce (KingswaySoft)" or "Salesforce" (deprecated) item to add the connection.

New Connection

Add Salesforce Connection Manager

Note that since v7.0 release, you can see two Salesforce connection managers available from us in the list. We recommend you use the "Salesforce (KingswaySoft)" one to avoid any potential conflicts with other vendor solutions. The two connection manager types work exactly the same, but the "Salesforce" one is now considered a deprecated connection after v7.0 release.

Salesforce Connection Manager contains the following four pages which configure how to connect to Salesforce.com service.

  • Connection
  • Proxy Server
  • Optional Settings
  • More Info

Connection page

The Connection page of Salesforce Connection Manager allows you to specify the general settings of the connection.

Salesfoce Connection Manager

User Name

User Name option allows you to specify the user account used to connect to Salesforce.com for your integration purpose.

Password

Password option allows you to specify a password for the above user in order to be able to login to Salesforce.com service.

NOTE:  The Password is not included in the connection manager's ConnectionString property by default. This is by design for security reason. However, you can include it in your ConnectionString if you want to parameterize your connection manager. The format would be Password=myPassword;  (make sure you have a semicolon as the last character). It can be anywhere in the ConnectionString.

Security Token

Security Token option is used to specify the security token that you have requested in Salesforce.com. If you do not have a security token yet, you must request one by resetting your security token. To do so, login to Salesforce.com, click Your Name | My Settings | Personal | Reset Security Token, and then click the Reset My Security Token button. The new security token is sent via email to the email address on your Salesforce user record. Note that there is no way to retrieve your security token from Salesforce. In case that you have lost it, you must request a new one by resetting it again. If you ever need to do so, make sure that you update the security token setting of any other applications that might be using the same authentication. 

NOTE: The Security Token is not included in the connection manager's ConnectionString property by default. This is by design for security reasons. However, you can include it in your ConnectionString if you want to parameterize your connection manager. The format would be SecurityToken=mySecurityToken;  (make sure you have a semicolon as the last character). It can be anywhere in the ConnectionString.

Instance Type

Instance Type option allows to specify what Salesforce instance you are connecting to. There are three options available.

  • Production
  • Sandbox
  • Other

For the first two options, we will automatically populate the Service URL option below. In the case the " Other" option is selected, you can specify an alternate Service URL which may not be a default one. This can be used for the case that you are connecting to a special Salesforce deployment including the situation that you use your own custom domain for Salesforce access.

Service URL

Using this option, you can provide an alternate URL that will be used to post transactions to your Salesforce instance. This field is only available when the Instance Type option above has been selected as "Other", for the other two options (Production and Sandbox), the Service URL is automatically populated using a default value.

Timeout (secs)

Timeout (secs) option allows you to specify the maximum number of seconds that Salesforce connection manager will wait while trying to establish a connection with Salesforce service. 

Test Connection

After you have provided all connection information, you may click "Test Connection" button to test if you can successfully login to Salesforce.com. 

Proxy Server page

The Proxy Server page of Salesforce Connection Manager allows you to specify a proxy server in order to connect to Salesforce service.

Salesfoce Connection Manager

Proxy Mode (since v6.0)

Proxy Mode option allows to specify how you want to configure the proxy server setting. There are three options available.

  • No Proxy
  • Auto-detect (Using system configured proxy)
  • Manual
Proxy Server

Using Proxy Server option, you can provide a proxy server to connect to Salesforce.com.

Port

The Port option allows you to specify the port number of the proxy server for the connection.

Username (Proxy Server Authentication)

Username option (under Proxy Server Authentication) allows you to specify the proxy user account.

Password (Proxy Server Authentication)

Password option (under Proxy Server Authentication) allows you to specify the proxy user's password.

NOTE:  The Proxy Password is not included in the connection manager's ConnectionString property by default. This is by design for security reasons. However, you can include it in your ConnectionString if you want to parameterize your connection manager. The format would be ProxyPassword=myProxyPassword;  (make sure you have a semicolon as the last character). It can be anywhere in the ConnectionString.

Advanced Settings page

The Advanced Settings page of Salesforce Connection Manager allows you to specify some advanced options when connecting to Salesforce service.

Salesfoce Connection Manager
Allow Save on Duplicate (since v5.0)

When this option is enabled, the service calls made to Salesforce will contain a header option which indicates that duplicates are allowed.

Allow Field Truncation (since v5.0)

When this option is enabled, the service calls made to Salesforce will contain a header option which indicates that field truncation is allowed.

Assignment Rule (since v4.4)

Using this option, you can specify the assignment rule to be fired when lead or case is created using Salesforce destination component. The input of this field should be the ID of the assignment rule in Salesforce. You can get an assignment rule's ID by navigating to the assignment rule record in Salesforce, the ID should be the last part of the URL, which generally consists of 18 characters. 

Retry on Intermittent Errors (since v4.1)

This is an option designed to help recover from possible intermittent outages or disruption of service so the integration does not have to be stopped because of such temporary issues. Enabling this option will allow service calls to be retried upon certain types of failure. A service call may be retried up to 3 times before an exception is fired. Retries occur after 0 seconds, 15 seconds, and 60 seconds.

Warning: Although we have designed our retry feature carefully so that the retry should only happen when it is deemed to be safe to do so. However, in some occasions, such retry service calls could result in the creation of duplicate data.

Note that between v2.0 SR-2 and v4.0 SR-1 (inclusive), the retry implementation was enabled by default, and there was no option to turn it off until the v4.1 release.

Request Limits (24-hour period) (since v3.1)

Using this option, you can specify how many API requests that the toolkit can send to Salesforce.com in a 24-hour period of time. When the limit is reached by a Salesforce source component, it will report a warning message indicating the limit has been reached. When the limit is reached by a Salesforce destination component, it may behave differently depending on how you have setup Error Handling for the destination component. It may fail the entire component, or redirect the input rows to its error output.

More Info page

More Info page shows some basic information about the toolkit. In this page, you can find the version information of the toolkit.

Salesfoce Connection Manager

Add Salesforce Components to SSIS Toolbox

SSIS Integration Toolkit for Salesforce includes two data flow components. They must be shown in SSIS toolbox before you can use (drag and drop) them in SSIS data flow task.

  • If you are using SQL Server 2008 R2 or earlier, you must add them to SSIS toolbox by following the instructions below.
  • If you are using SQL Server 2012 or later, you should not need to do so, as SQL Server 2012 (or a later version) automatically lists all available pipeline components by scanning the system. Under certain circumstances, your SSIS toolbox might appear empty, in which case, you should click the SSIS Toolbox button (shown below) on the top-right corner of your Control Flow or Data Flow view.

    SSIS Toolbox

Add Salesforce Data Flow Components to SSIS Toolbox (SQL Server 2008 R2 or earlier)

In BIDS 2008 or 2005 (depending on which SQL Server version you are using), create a new data flow if you do not have one yet and switch to SSIS data flow page. Right-click on the toolbox area to bring up the context menu and select "Choose Items..." option as shown below.

Choose Items

You will now be presented with a window called "Choose Toolbox Items". Switch to "SSIS Data Flow Items" tab, and select Salesforce Source, Salesforce Destination and Salesforce Wave Destination (new since v7.0) components from the list. 

Add SSIS Components

You can now use the two components by dragging and dropping either of them to the design surface of your SSIS data flow task. 

Use of Salesforce Source Component

Salesforce Source Component is an SSIS data flow pipeline component that can be used to read / retrieve data from your Salesforce.com instance. Salesforce source component supports using either a Salesforce object, or an object query statement as the data source. 

Salesforce Source Component includes the following two pages to configure how you want to read data from Salesforce.

  • General
  • Columns

General page

The General page of Salesforce Source Component allows you to specify the general settings of the component. 

Salesforce Source Editor

Salesforce Connection Manager

Salesforce source component requires a Salesforce connection in order to connect to your Salesforce.com instance. The Salesforce Connection Manager option will show all Salesforce connection managers that have been created in the current SSIS package.

Batch Size

Batch Size option allows you to specify how many records you want to retrieve each time. The default value is 1000. The maximum allowed value is 2000. This parameter should not be too small for performance reason. Note that the component will automatically page through all records based on the object or object query you have specified below, regardless the value of Batch Size option.

Source Type

Source Type option allows you to specify whether you want read data from Salesforce object, or use a snippet of Salesforce object query. Using Object option, you have the flexibility of visually picking which fields that you want to read from Salesforce object. When ObjectQuery is used, you can leverage the capabilities and flexibilities of SOQL (Salesforce Object Query Language), such as JOIN, filtering criteria, etc. Your business requirements should influence which option to choose.

Include Deleted / Archived

Include Deleted / Archived option specifies whether those deleted or archived Salesforce records should be returned.

Use Bulk API (since v4.2)

Use Bulk API option specifies whether you want to use Salesforce Bulk API to read data from your Salesforce organization. This option can be used when you have significantly large number of records in Salesforce system which requires an extraordinary long time if they are read synchronously. 

Note that when this option is enabled, the Include Deleted / Archived option above will have no effect, as Salesforce Bulk API does not support reading deleted or archived records.

PK Chunking Header (since v6.0)

PK Chunking Header allows to use automatic primary key (PK) chunking for a bulk query job, which can be used to handle large data set extracts.

The supported field values are:

  • TRUE - Enables PK chunking with the default chunk size (100,000), starting from the first record ID in the queried table.
  • FALSE - Disables PK chunking. If no value provided to PK Chunking Header property, the default is FALSE.
  • chunkSize - Specifies the number of records within the ID boundaries for each chunk. The maximum size is 250,000.
  • parent - Specifies the parent object when you’re enabling PK chunking for queries on sharing objects.
  • startRow - Specifies the 15-character or 18-character record ID to be used as the lower boundary for the first chunk. 

Note that this option will only be available when the Use Bulk API option is enabled. 

Output Timezone (since v4.3)

Output Timezone  option specifies how Salesforce datetime values are retrieved. There are 3 options available:

  • Timezone of Connection User (Default)
  • Adjust to UTC
  • Adjust to Local System Timezone
Source Object

Source Object option is only available when Source Type option has been chosen as Object. When you click the drop down button of this option, the toolkit will automatically retrieve the list of all available Salesforce objects, so that you can pick one. 

Object Query

When the above Source Type option has been chosen as ObjectQuery, you will be presented a text editor. You can use this to enter a snippet of object query to retrieve data from your Salesforce.com instance.

Salesforce Source Editor

There are several advantages of using ObjectQuery. First, you can use SOQL (Salesforce Object Query Language) to build complex query that retrieves data from Salesforce. You may include fields from parent object(s) in your query to read data from the parent object(s) directly. Secondly, using ObjectQuery option, you can apply WHERE clause to filter the data that is returned from Salesforce.com instance, so that you only work with the data that you are interested in. 

SSIS Integration Toolkit for Salesforce has the most sophisticated support of SOQL. You can write very complex SOQL queries, including using child-to-parent relationship, aggregation functions and datetime functions in the queries. The following is a supported SOQL query which includes the usage of child-to-parent relationship.

SELECT c.Id, 
         c.FirstName, 
         c.LastName, 
         c.Account.Name, 
         c.Account.NumberOfEmployees
  FROM Contact c

The following query with aggregation functions is also supported.

SELECT CALENDAR_YEAR(CreatedDate) CreatedYear, SUM(Amount) TotalAmount
  FROM Opportunity
  GROUP BY CALENDAR_YEAR(CreatedDate)

Due to the constraints of SSIS, we do not support reading data from child objects in object query statement. For example, the following query is not supported.

SELECT Id, 
         Name, 
         (SELECT Id, Name FROM Contacts) 
  FROM Account

The object query editor comes with a toolbar, which provides some common functionality useful when editing the query statement. One of the buttons is called Insert Variables, which can be used to insert SSIS variables in the query. With this capability, you can parameterize your SOQL query so it only returns the records that you are interested in.

Salesforce Source Editor

Since v4.2, we have enhanced the support of SSIS variables in object query, so you can now use SSIS variables in whichever way you would like to use, and you are not limited to use SSIS variables in conditions only.

Salesforce Source Editor

It should be noted, when using SOQL aggregation functions, datetime functions in the SELECT clause, you would typically provide an alias for each function, otherwise your output field name will be in exprN format, where N is the sequence number of such function.

Refresh Salesforce Metadata Button

Since v2.0, we added a new feature to Salesforce source component, which is a button called "Refresh Salesforce Metadata" on Salesforce Source Editor window. By clicking this button, the component will retrieve the latest metadata from Salesforce and update each field. This feature works by performing the following three actions.

  • Update any existing object fields to the latest metadata
  • Add any new object fields that have recently been created in Salesforce
  • Remove any object fields that have recently been deleted from Salesforce

After clicking this button, you will receive the following screen once the refresh is done.

Refresh Metadata

The Refresh Salesforce Metadata feature has been designed to minimize any additional rework. Particularly, the component will only update all existing fields to the latest Salesforce metadata without actually deleting and re-creating them. This would otherwise cause rework if you have downstream pipeline component mapped to such fields.

Columns page

The Columns page of Salesforce Source Component shows you all available columns from the object or object query that you have defined in General page. 

When the Source Type option has been chosen as Object in General page, you will notice that the grid in Columns page has a checkbox column on the left. This allows you to specify what fields you want to read from the chosen Salesforce object.

Salesforce Source Editor

On the top left of the grid, you can see a checkbox, which can be used to toggle the selection of all available Salesforce fields. This is a productive way to select or unselect all available Salesforce fields. 

NOTE:  As a general best practice, only select the Salesforce fields that are needed for the downstream pipeline components. This would not only speed up Salesforce queries, but also save resources for SSIS engine. 

When the Source Type option has been chosen as ObjectQuery in the General page, you will not see any checkboxes in the grid. The reason is, object query itself defines what to read from Salesforce. 

Salesforce Source Editor

Use of Salesforce Destination Component

Salesforce Destination Component is an SSIS data flow pipeline component that can be used to write data to your Salesforce.com instance. You may create, updatedelete, or upsert Salesforce records using the Salesforce Destination Component. 

Salesforce Destination Component includes the following three pages to configure how SSIS writes data to Salesforce.com.

  • General
  • Columns
  • Error Handling

General page

General page allows you to specify the general settings of the destination component. 

Salesforce Destination Editor

Salesforce Connection Manager

Salesforce destination component requires a Salesforce connection in order to connect to your Salesforce.com instance. The Salesforce Connection Manager option will show all Salesforce connection managers that are available in the current SSIS package.

Batch Size

Batch Size option allows you to specify how many records you want to write to Salesforce with each service call.

When Use Bulk API option is not checked, the default value of Batch Size is 200, and the maximum allowed value is also 200. 

When Use Bulk API option is checked, the default value of Batch Size is 5,000, and the maximum allowed value is 10,000.

Action

Action option specifies how the data should be written to Salesforce.com. There are 4 action types available. 

  • Create - Create new record(s) in Salesforce
  • Update - Update existing record(s) in Salesforce
  • Delete - Delete record(s) from Salesforce
  • Upsert - Update an existing record in Salesforce if matching record can be found. Otherwise, create a new record using the information from upstream pipeline components. 
Destination Object

Destination Object option specifies which Salesforce object to write the data. When the option is clicked, SSIS Integration Toolkit will retrieve a list of all available Salesforce objects for the selected Salesforce connection.

Upsert External ID Field

Upsert External ID Field option specifies the External ID field for Upsert action. It is required when Upsert action is used. You may use the dropdown list to select an External ID field.

Use Bulk API (since v3.0)

Use Bulk API option dictates whether you want to use Salesforce Bulk API to write to your Salesforce organization. By default, this option is not selected. When using Salesforce Bulk API, your data will be processed asynchronously, which can provide some significant performance improvement, and also using Salesforce Bulk API, you are not throttled by Salesforce daily API limits. However before v6.0, our software does not get immediate results back from Salesforce so the SalesforceRecordId field will always be empty in the Default Output when Bulk API is used. This is no longer the case after our v6.0 release.

When the option is enabled, the following bulk API related options become available for configuration.

Write batch files with UTF-8 encoding (since v5.1)

When enabled, the batch files will be created using UTF-8 encoding, which can help improve working with non-ANSI characters in case there are any. We generally recommend turning on this option for best encoding comparability.

Bulk Data Format

There are 4 options available.

  • ZIP
  • ZIP_CSV
  • XML
  • ZIP_XML
We generally recommend the ZIP_CSV option, as the format will create the smallest batch files for the same amount of data to be processed.
Concurrency Mode (since v3.2)

There are two options available.

  • Parallel - this means that the batch file will be processed in a paralleled fashion at the Salesforce side, this will provide some better processing performance, but it also means that the sequence of the submitted batches is not respected when they are processed by Salesforce.
  • Serial - this means that the batch file will be processed in a serial fashion at the Salesforce side.
Bulk JobId Variable

Bulk JobId Variable option allows you to specify an SSIS variable that you can write the bulk job's ID in order to track the status of the job in Salesforce later.

Remove Invalid Characters (since v5.1)

When enabled, the Remove Invalid Characters option will remove any invalid characters from the input which can avoid an XML exception when the components tries to construct the SOAP request to be sent to Salesforce. Those invalid characters are usually not accepted by Salesforce even posted. 

Ignore Null-Valued Fields (since v5.1)

The Ignore Null-Valued Fields option allows you to ignore any fields that have a null value. By ignoring a field, the null value will not be posted to the Salesforce server. This can help avoid the situation that you overwrite non-empty values with an empty value, if your requirement dictates so.

Send datetime values in UTC Format (since v4.1)

Enabling this option allows you to send date time values to Salesforce in UTC format. If left unchecked, any date time value passed into Salesforce will be treated as integration user's local time.

Refresh Salesforce Metadata Button

Since v2.0, we added a new feature to Salesforce destination component, which is a button called "Refresh Salesforce Metadata" on Salesforce Destination Editor window. By clicking this button, the component will retrieve the latest metadata from Salesforce and update each field. This feature works by performing the following three actions.

  • Update any existing object fields to the latest metadata
  • Add any new object fields that have recently been created in Salesforce
  • Remove any object fields that have recently been deleted from Salesforce

After clicking this button, you will receive the following screen once the refresh is done.

Refresh Metadata

Map Unmapped Fields Button (since v3.0)

By clicking this button, the component will map any unmapped Salesforce fields by matching their names with the input columns from upstream components. This is useful when your source component has recently added more columns, in which case you can use this button to automatically establish the association between input columns and unmapped Salesforce fields.

After clicking this button, you will receive the following message.

Map Unmapped Fields

Clear All Mappings Button (since v4.1)

By clicking this button, the component will reset all your mappings in the destination component.

Columns page

The Columns page of Salesforce Destination Component allows you to map input columns to Salesforce fields. 

In Columns page, you will see a grid that contains five columns as below.

  • Input Column - You can select an input column from upstream component for the corresponding Salesforce field.
  • Destination Salesforce Field - The Salesforce field that you are writing data. 
  • Text Lookup - This is a feature that we introduced since v3.0. This option is only available for Salesforce lookup fields. When this option is selected, the component can perform lookup based on a text value of the target object. For further information about how to use Text Lookup feature, please refer to Working with the Text Lookup Feature section below.
  • Data Type - The target Salesforce field's data type.
  • Unmap - This column can be used to unmap the field from the upstream input column, or otherwise it can be used to map the field to an upstream input column by matching its name if the field is not currently mapped.

Salesforce Destination Editor

Working with Text Lookup Feature

Since v3.0, we added support for Text Lookup feature, which allows you to perform lookup based on the text values of the target object. To configure Text Lookup feature, click the ellipse button in Text Lookup column which is available in Salesforce destination component's mapping page. You will be presented with the following screen.

Text Lookup Screenshot

You can configure the Text Lookup setting by toggling Use Text Lookup option. When you have checked Use Text Lookup option, you will see a list of the target objects for the involved lookup field, and you can configure a different lookup strategy for each target object.

Choose Target Field(s) - Using this option, you can specifically choose which text field should be used for the lookup purpose. When you choose this option, you will be presented with a list of the target objects for the lookup field, and you can choose to use different lookup strategy for each target object. In this list you will be able to see the following options.

  • Opt Out? (since v4.4) - When chosen, the lookup object will be excluded from being used for text lookup purpose.
  • Target text/integer field - You can choose a text or integer field that will be used for the lookup purpose.
  • Optional default value (if no match) (since v4.3) - When specified, the component will use this default value to perform the lookup should the input value lookup fail.

NOTE: In order for Text Lookup feature to work efficiently, we populate an internal cache when a lookup is first requested. The cache is created by querying data from the target object, which would consume some Salesforce API calls.

In addition to the above settings, Text Lookup feature also offers the following additional advanced options since our v4.0 release.

  • Ignore case (since v4.0) - When chosen, the Text Lookup will perform a case-insensitive lookup. For instance, "ABC Company" will be treated the same as "abc company".
  • Report error on duplicates (since v4.0) - When chosen, the Text Lookup feature will report an error when a duplicate is encountered at the time the lookup cache is populated.
  • Skip record if lookup fails (since v5.4) - When enabled, the Text Lookup feature will skip the row when the lookup fails. Note that when this option is enabled, the skipped rows will not be sent to either the Default Output or the Error Output of the destination component.
  • Cache Strategy (since v4.2) - You can choose from one of the two options:
    • Full Cache - When chosen, the component will populate a full cache of all records from the lookup object before the lookup. This is generally the preferred option for text lookup feature, particularly when the number of records in lookup entity is small. This option is the default mode when v4.1 or an earlier version is used.
    • Partial Cache - When chosen, the component will gradually build up lookup cache as the data load progresses. This is the preferred option when the number of records in lookup objects is significantly large. For instance, if you have more than a few hundreds of thousands of records in lookup entity, and you are only processing a few dozens of records for your primary object, Partial Cache mode would provide better performance in this kind of case.

NOTE: Prior to v4.0, if duplicates are found when populating the lookup cache, the first one will be chosen.

Error Handling page

The Error Handling page allows you to specify how errors should be handled when they happen. 

Salesforce Destination Editor

There are three options available. 

  1. Fail on error
  2. Redirect rows to error output
  3. Ignore error

When Redirect rows to error output option is selected, the error handling behavior might be different depending on the version of our software you are using.

  • If you are using v4.0 or a later version, it will only redirect the rows that have failed to the error output (the successful ones will be directed to the Default Output of the destination component which is a new output in v4.0)
  • If you are using v3.2 or an earlier version, it will redirect all rows to the error output including those that have succeeded and those that have failed. In case you need to further process the source records after the destination component (such as logging those records to a database table, or writing to a different Salesforce object using a subset of available fields) when using v3.2 or an earlier version, you must choose the "Redirect rows to error output" as the Error Handling option. Then attach a Conditional Split component to the Salesforce destination component. In the Conditional Split component, evaluate the ErrorCode column (an output field added by the Salesforce destination component) and check to see whether it has actually erred out. The Conditional Split should typically have two output branches, one is ErrorCode == -1, which is the success path, and the other one is ErrorCode != -1, which is the failure path indicating that an error has occurred when writing to the Salesforce.

In the error output, you can see the following columns:

  • ID (version 3.2 or earlier) - Contains the newly created Salesforce  record's ID, which you can use to write to log or further process using additional data flow components. Note that this column has been moved to the Default Output of the destination component in v4.0 which has a new name called SalesforceRecordId.
  • ErrorCode(version 3.2 or earlier) - Contains the error code that is reported by Salesforce.com server or the component itself. Note that this column has been removed since v4.0.
  • ErrorMessage (new column name since v4.0) - Contains the error message that is reported by Salesforce.com server or the component itself

NOTE:  Use extra caution when selecting the Ignore error option, since the component will remain silent for any errors that have occurred. 

In the Error Handling page, there is also an option (since v4.2) that can be used to enable or disable the following output fields for the destination component.

  • SalesforceRecordId - Contains the newly created Salesforce record's ID, which you can use to write to log or further process using additional data flow components.
  • IsNew - Contains value to indicate whether it is a newly created Salesforce record, or an existing one. This is useful when you use Upsert action.

NOTE:   If you don't plan to use any of those fields for any further processing, it is generally recommended to turn them off, so you don't get any warning from SSIS by complaining that those fields are never used, and it should also provide a slightly better performance by doing so. Note again this feature is only available since v4.2.

In addition to the above settings, the Error Handling page also offers the following an Additional Outputs option since our v6.0 release.

The "Skipped Rows" output can be useful when the Text Lookup is set up to Skip Records if Lookup Fails or the error-handling mechanism is chosen as “Ignore error” in the Error Handling page.

Use of Salesforce Wave Destination Component

Salesforce Wave Destination Component is an SSIS data flow pipeline component added since v7.0 release that can be used to write data to the Salesforce.com Wave Analytics platform. Salesforce Wave Destination Component includes the following two pages to configure how SSIS writes data to Salesforce Wave Analytics.

  • General
  • Columns

General page

General page allows you to specify the general settings of the destination component. 

Salesforce Wave Destination Component

Connection Manager

Salesforce Wave Destination component requires a Salesforce connection in order to connect to your Salesforce.com instance. The Connection Manager option will show all Salesforce connection managers that are available in the current SSIS package.

Operation

Operation option allows you to Indicate which operation to use when you’re loading data into the dataset. There are 4 operations available.

  • Append - Append all incoming rows to the dataset (The dataset will be created if it doesn’t exist).
  • Delete - Delete the rows from the dataset. When using the Delete option, there must be one field that is selected as the unique identifier which has the IsUniqueId property set to true in the columns page , and the field must be mapped so that it has an input value .
  • Overwrite - Create a dataset with the given data, and replace the dataset if it exists.
  • Upsert - Insert or update rows in the dataset (The dataset will be created if it doesn’t exist) . When using the Upsert option, there must be one field that is selected as the unique identifier which has the IsUniqueId property set to true in the columns page, and the field must be mapped so that it has an input value.
App Name

App Name option specifies the name of the app that contains the dataset, this option is as known as EdgemartContainer in Salesforce Wave terminology. According to Salesforce documentation:

  • If the name is omitted when you’re creating a dataset, the name of the user’s private app is used.
  • If the name is omitted for an existing dataset, the system resolves the app name.
  • If the name is specified for an existing dataset, the name is required to match the name of the current app that contains the dataset.
Dataset Name

Dataset Name option specifies the alias of a dataset (also known as EdgemartAlias), which must be unique across an organization.

Dataset Display Name

Datset Display Name option specifies the display name for the dataset (also known as EdgemartLabel).

Fully Qualified Name

Fully Qualified Name option specifies the fully qualified name of the dataset.

Notification Email

The email address to send notifications to. Can be up to 255 characters and can contain only one email address. Defaults to the current user’s email address if not provided. 

Notification Mode

The Notification Mode option specifies when to send notifications about the data upload to Salesforce Wave Analytics. There are 4 options available:

  • Always - Always send notifications.
  • Never - Never send notifications.
  • Failures - Send notifications only if the upload process failed.
  • Warnings - Send notifications if warnings or errors occurred during the upload.
Reset Columns Button

By clicking this button, the component will present you a window from which you can specify how you would like to reset columns. There are 3 options.

  • Reset all columns
  • Reset and populate new columns from the input component
  • Reset and populate new columns from an existing CSV file

When choosing the last option, you have to provide a CSV file which contains a header row so that we can detect the columns available in the file.

Map Unmapped Fields Button

By clicking this button, the component will map any unmapped destination fields by matching their names with the input columns from upstream components. This is useful when your source component has recently added more columns, in which case you can use this button to automatically establish the association between input columns and unmapped destination fields.

Clear All Mappings Button

By clicking this button, the component will reset all your mappings in the destination component.

Columns page

The Columns page of Salesforce Wave Destination Component allows you to define your Wave Analytics data schema and also map input columns to the Wave Analytics fields. 

In Columns page, you will see a grid that contains four columns as below.

  • Input Column - You can select an input column to receive data from SSIS upstream component(s).
  • Target Field - The Wave Analytics field that you are writing data to.
  • Data Type - The Wave Analytics field's data type described in SSIS terminology.
  • Unmap - This column can be used to unmap the field from the upstream input column, or otherwise it can be used to map the field to an upstream input column by matching its name if the field is not currently mapped.

Salesforce Wave Destination Component (Columns page)

In the Columns page, you can use the toolbox on the right portion of the window to add, change or remove destination fields. Here you can also define the destination field's metadata.

There are also two additional function buttons that can be used to export and import columns. Note that when using the Import External Columns option, all existing columns will be removed from the component before the import is actually performed.

License Manager

SSIS Integration Toolkit comes with a license manager program which helps you manage and activate the product license key to be used for the toolkit.

Without a commercial license, SSIS Integration Toolkit will operate under the Developer License which is free to use for development or evaluation purpose. Under the developer license, you can use the software within the development tool (SSDT-BI, BIDS, or Visual Studio). 

KingswaySoft License Manager

The only limitation with the free developer license is the inability to run the software outside of the development tool (SSDT-BI, BIDS, or Visual Studio). If you would like to run the software outside the development tool, such as running SSIS packages on a scheduled basis or from a command line, you will need to acquire a license from us.

If you want to test out the functionality by scheduling your SSIS packages, a trial license can be requested. To do so, you can launch License Manager program, then click "Change License Key" button, where you can request a free trial license after filling out the necessary Licensee Information.

KingswaySoft License Manager

If you have received a product license key from us after placing an order through our online shopping cart system, you can also click "Change License Key" button and enter the product license key in order to activate the software to use the fully-featured commercial license. 

To request a free trial license or activate a product license key that you have received, you can use Web Service option to complete the process by sending the request to our license server directly. An Internet connection is required when Web Service option is used. This is the option that we recommend.

Alternatively, you can choose the Email option so that the license manager will generate an email for you which you can send to us. The Email option should only be used if your system has no Internet access. It requires manual processing so please expect to wait for 24 to 48 hours before receiving a license file from us. Once you have received the license file from us from through email, you can save it to a local file, which you can then install by clicking "Install License File..." button in License Manager.

If you have acquired a perpetual license from us, once the software has been activated, your license manager will be shown as something like below.

KingswaySoft License Manager

Since v2.0, you can see your Support Expiry Date or Maintenance Expiry Date in the License Manager program if you are using a perpetual license. By default, your perpetual license comes with a one-year maintenance and upgrade, which entitles you to use any version of the software released before your Support Expiry Date or Maintenance Expiry Date. You can extend it by entering a new maintenance license key that you have acquired from us.

KingswaySoft License Manager

If your commercial license is a subscription license, you will not see the Support Expiry Date or Maintenance Expiry Date option in the License Manager program, since your subscription license comes with maintenance and upgrade for the entire subscription period.

NOTE: Starting from v2.0, you must run License Manager program under a local administrative account due to the privileges required to write license file to the system.

Known Limitations

The following are some limitations of the software which are either by-design or due to the constraints of Salesforce.com or SSIS platform:

  • Address and Geolocation compound fields cannot be used after SELECT clause directly in SOQL query. If you do so, you would run into an error such as "Error: Field 'BillingAddress' was not found for object 'Account'". Note that Address and Geolocation compound fields can be used in WHERE or ORDER BY clauses.
  • Parent-to-Child relationship fields cannot be used after SELECT clause directly in SOQL query. The other way around (child-to-parent) is supported. 
  • Text Lookup is currently designed to only supports lookup on text and integer fields, and only one field can be used for the lookup purpose. For other more advanced scenarios, such as lookup on a non-text and non-integration field, or lookup on more than one field, you would need to consider a different implementation, such as SSIS Merge Join component, or something equivalent.

Contact Us

If you need any further assistance with the toolkit, please do not hesitate to contact us.

%MCEPASTEBIN%
%MCEPASTEBIN%
%MCEPASTEBIN%
%MCEPASTEBIN%