SSIS Salesforce Destination
PreviousNext

SSIS Salesforce Destination can be used to load large amount of data from any source to Salesforce.com without any programming. You can use simple drag and drop mapping to perform Bulk Insert/Upsert/Update and Delete operations.

Download SSIS PowerPack

Content

Video Tutorial

Step-By-Step

In this tutorial you will learn how to Upsert Operation(Insert + Update) in SalesForce Storage in a Single Operation (In this case its from JSON Source). It uses Key based Lookup.
  1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
  2. Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
  3. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
    SSIS Data Flow Task - Drag and Drop
  4. Double click on the Data Flow task to see Data Flow designer surface.
  5. Here, In Visual Studio, drag and drop the ZS JSON Source (REST API or File) and SalesForce Destination in the design surface and join the components with the blue arrow.
    SSIS SalesForce Destination - Drag and Drop
  6. Now, we need SalesForce Storage connection. Click here for create SalesForce Connection.

How to insert if there are no matching rows and update if there are using SalesForce Destination.

  1. Double click on JSON Source to configure it.
  2. Set AccessMode to File path or web URL, select JSON file with valid data.
    SSIS SalesForce Destination From JSON Source - Configure
  3. Click on Preview button to see Data Preview.
  4. Click on OK button to save JSON Source configure setting UI.
  5. Now, double click on SalesForce Destination to configure it.
  6. In the Connection Manager, select SalesForce Connection we have created before.
    SSIS SalesForce Destination - Connection Manager
  7. Now, In the Component Properties, Set Operation to UpdateOrInsert, select TableName and UpsertKeyField.
    SSIS Salesforce Destination Settings (Bulk Insert, Upsert, Update, Delete)
  8. In the Columns Mappings Tab, Map Primary Column and other you want to update or insert records.
    SSIS Salesforce Destination Mappings
  9. Now, Click on OK button to save SalesForce Destination configure setting UI.
  10. From the SSIS toolbox drag and drop Trash Destination on the data flow designer surface.
    SSIS Trash Destination - Drag and Drop
  11. Now single click on the SalesForce Destination, once you see blue arrow from source ... connect it to New and Update Records Trash Destination and for Red arrow connect it to Bad Records Trash Destination.
    SSIS SalesForce Destination Join Trash Destination
  12. Thats all, you can Run or Execute your package.
    SSIS Salesforce Destination - Upsert Example (Bulk Insert or Update)

Properties

Property Name Description
Operation Action you like to perform

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Insert [0] Insert
Update [1] Update
UpdateOrInsert [2] UpdateOrInsert
Delete [3] Delete
AssignmentRuleId ID of AssignmentRule you want to fire after new Lead or Case is created. This option is only valid for object type Lead and Case. To fire default assignment rule set UseDefaultAssignmentRule=true. To find out AssignmentRule ID, navigate to Assignment Rule page and get ID from URL (e.g. 01Q610000008e5x). For more information check this link https://help.salesforce.com/articleView?id=editing_assignment_rules.htm
UseDefaultAssignmentRule If you want to fire default Assignment rule after Case, Lead or Account, set this option to true. Either AssignmentRuleID or this option can be used. If you set this option to true and also specify AssignmentRuleID property then it may throw error
UpsertKeyField Key field based on which you want to perform Upsert action. This can be Id, External ID or IdLookup field type.
EnableBulkApiMode Enable BULK API mode for large amount of data load. This mode may be slower for smaller set of data but if you have large amount of data then choose this mode to speedup data loading.
MaxRowsPerJob Maximum rows per job. This option is useful to avoid memory related errors when EnableBulkApiMode=true. Reduce or increase this limit as per you need. For example if you getting OutOfMemory error then reduce this count.
ConcurrencyMode When you set EnableBulkApiMode=true by default all batches are executed in parallel mode on salesforce side, this might cause issue in some cases due to row locking. If you get error about row locks then try to change this to Serial mode see it helps. This option is ignored if you set BulkApiVersion=V2.

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Default [0] Default
Parallel [1] Parallel
Serial [2] Serial
BulkApiVersion Bulk API Version to use for data processing. API V2 is very different than V1 so test performance with each version and use the one fits your need. V2 is better if you facing Locking issue.

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Default [0] Default
V1 [1] V1
V2 [2] V2
LoggingMode LoggingMode determines how much information is logged during Package Execution. Set Logging mode to Debugging for maximum log.

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Normal [0] Normal
Medium [1] Medium
Detailed [2] Detailed
Debugging [3] Debugging
PrefixTimestamp When you enable this property it will prefix timestamp before Log messages.
DoNotCreateOutputs Enable / Disable Response Output. Checking this option can remove unwanted warnings about unused Columns and many times speedup performance so if output is not needed set this option to True so its removed.
TableName Target object name where you want like to write data.
BatchSize How many rows you want to send in each request.. This setting is ignored when EnableBulkApiMode=true. In BulkApi mode batch size is fixed at 10000 rows per batch.
IgnoreFieldsIfInputNull Set this option to True if you wish to ignore fields if input value is NULL. By default target field is set to NULL if input value is NULL.
FieldsToSetNullIfInputNull Comma separated CRM entity field names which you like to set as NULL when input value is NULL. This option is ignored if IgnoreFieldsIfInputNull is not set to True.
EnableParallelProcessing Enables sending requests using multiple threads to speed up processing. Try to adjust MaxParallelThreads property to test performance gain.
MaxParallelThreads Max Parallel Threads to use to process requests. This property is only valid if you set EnableParallelProcessing=True. Valid values are 1 to 40.

Setting UI

SSIS SalesForce Destination - Setting UI
SSIS SalesForce Destination - Setting UI

See Also

References

Articles / Tutorials

Click here to see all articles for [SSIS Salesforce Destination] category
SSIS PowerPack v2.9 released (Upsert Destination, Salesforce BULK API and more…)

SSIS PowerPack v2.9 released (Upsert Destination, Salesforce BULK API and more…)

What’s New In this release we focused on mainly providing high quality and super fast Upsert Destination (Bulk Update, Insert and Delete / Merge) for SQL Server. We also added Salesforce BULK API support for Salesforce Destination. Full Notes: https://zappysys.com/onlinehelp/ssis-powerpack/scr/release-notes.htm Version 2.9.0.10210 [Feb 08, 2019] New Features/Improvements NEW: Amazon S3 CSV File Source, FTP, Azure – […]


Load data in Salesforce using SSIS €“ Insert, Upsert, Delete, Update

Load data in Salesforce using SSIS €“ Insert, Upsert, Delete, Update

Introduction In this article we’re going to focus on load data in Salesforce using SSIS (Bulk Insert, Update, Delete and Upsert). If you like to know how to read from Salesforce then refer to previous post here. For Demo purpose we will use AdventuresWorks sample database as our source but you can use your own […]



Copyrights reserved. ZappySys LLC.