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 Database for testing.

Salesforce developer account

To develop ETLs in Salesforce using SSIS you’ll need a Salesforce developer account. If you don’t have one, don’t worry, you can create a limited version FREE developer account. Click here to Sign up for FREE developer account.

Video Tutorial – Salesforce Insert, Upsert, Delete, Update

Getting Started

In this section, we will see how to perform bulk insert, update delete and upsert operations using ZS Salesforce Destination

Configure Salesforce Connection

First, we’ll need setup a connection to read or write Salesforce data is to make sure we have a working connection in SSIS.

1. Create new or open existing SSIS Project in Visual Studio.
2. Open SSIS Package and Go to SSIS designer.
3. Right click in the connection manager panel and click New connection
   

   Create a new SSIS Salesforce Connection

4. In the connection type selection, Select the ZS-SALESFORCE Connection.
5. We’ll use this information: Username, Password, Security Token (click the link learn how to obtain Salesforce Security Token )
6. Click OK to add the connection.
   

   Salesforce using SSIS, Salesforce connection

7. Save all changes

Bulk Insert – Import data into Salesforce using SSIS

Let’s start with the a bulk insert in Salesforce using SSIS. On this section, we’re going to generate retrieve data from and OLEDB Source and proceed to Insert the records into Salesforce using ZS Salesforce Destination.

Configure SQL Server Source (OLEDB)

1. Open SSIS Package and drag Data Flow task from Control Flow SSIS Toolbox
   

   Dragging and dropping Data Flow Task into Control Flow

2. Go to data flow designer. Drag and drop an OLE DB Source
3. Open the OLE DB Source and configure as follow:
   

   Configure SQL Server Source

Configure SSIS Salesforce Destination for Bulk Insert

Once SQL Server source is configure. Now let’s configure Salesforce Destination for Bulk Insert like below.

1. Drag and drop ZS Salesforce Destination
2. Open the destination component
3. Connection Manager
   

   Salesforce using SSIS, upsert destination connection manager

4. Component Properties
   

   salesforce-using-ssis-insert-properties

5. Column Mappings
   

   salesforce-using-ssis-insert-mapping

6. Click OK
7. Drag and drop two Trash Destination components
8. Connect ZS Salesforce Destination for: New records Output and Error Output
9. Right click on the joining arrows and enable data viewers on all previous connections
10. Save all changes
11. Right click in the data flow designer, click Execute Task
    

    salesforce-using-ssis-insert-results

Bulk Update – Modify data in Salesforce using SSIS

In this section we’re going to perform a bulk update on Salesforce using SSIS. We’re going to list some information from our source, list all our Salesforce Account data, merge it and only update the data we match. In below example what we will do is Find out matching records in Salesforce and our Source System based on some External Key field (e.g. AccountNumber in our case). And then obtain Salesforce Record Id for matching AccountNumber and use it to Update target record.

Configure SQL Server Source (OLEDB Source)

1. Open SSIS Package and drag Data Flow task from Control Flow SSIS Toolbox
2. Go to data flow designer. Drag and drop OLE DB Source
3. Open the OLE DB Source and configure
   

   Salesforce using SSIS, Update OLEDB source

Configure SSIS Salesforce Source

1. Now lets Drag and drop ZS Salesforce Source
2. Open the ZS Salesforce and configure. You can select Query Mode and enter query like this because we only care about two fields for now. Use query mode whenever possible because it transfers less data.
   
   Transact-SQL

   select Id,AccountNumber from account

   1	select Id,AccountNumber from account

   

   Salesforce using SSIS, Update Salesforce source

Configure Sort Transforms

Now lets sort both previous datasets using Sort Transforms. This is needed to JOIN dataset using Merge Transform (See next section)

1. Drag and drop: 2 Sort and 1 Merge Join components
2. Connect the OLE DB Source to the first Sort component, and configure
   

   Salesforce using SSIS, Update OLEBD sort

3. Connect the Salesforce Source to the second Sort component, and configure
   

   Salesforce using SSIS,update Salesforce sort

Configure Merge Transforms

No next step would be to join previous datasets based on AccountNumber and then we will output Id from Salesforce which is needed for later step.

1. Drag and Drop Merge Transform from SSIS Toolbox like below.
   

   Drag SSIS Merge Join Transform from Toolbox

2. Connect Sorts to the Merge component (sort from OLEDB Source most be on the left)
3. Configure the Merge component
   

   Salesforce using SSIS, update merge

Configure SSIS Salesforce Destination for Bulk Update

1. Drag and drop ZS Salesforce Destination
2. Open the ZS Salesforce Destination and configure
3. Connection Manager tab
   

   Salesforce using SSIS, update destination connection manager

4. Component Properties tab
   

   Salesforce using SSIS, update destination component properties

5. For Update purposes, Salesforce Destinations needs to map the ID from the Account object. For more information, please use the following link SSIS Salesforce Destination – Update / Insert with Lookup Fields (Text to Id)
6. Column Mapping
   

   salesforce using SSIS, update destination column mappings

7. Click OK
8. Drag and drop three Trash Destination components
9. Create two connections from ZS Salesforce Destination as follows: Update records and Error Output
10. Right click on the joining arrows and enable data viewers on all previous connections
11. Save all changes
12. Right click in the data flow designer, click Execute Task
    

    Salesforce using SSIS, update results

Bulk Upsert – Import/Modify data in Salesforce using SSIS

In this section we’re going to perform a bulk upsert of 25 record’s on Salesforce using SSIS. Upsert is an operation that combines both insert an update.

ZS Salesforce Destination allows Upserts operations. You’ll need to select the Upsert as an action and indicate the Key value for the comparison.

Creating External ID field for Salesforce Upsert

For the Upsert action we have to use an Upsert Keyfield (i.e. External ID field). For more information read here External ID. Follow these steps to add a External ID field on Salesforce Accounts object:

1. Login to your Salesforce account.
2. Click on the configuration icon and select “Service Setup”
   

   Salesforce using SSIS, Salesforce edit object

3. On the new Tab, look for USER INTERFACE -> Objects And Fields -> Object Manager
4. Select Account object
5. Select Fields & Relationships
6. Click New, to create a field
   

   Salesforce using SSIS, Salesforce Fields & Configuration

7. For the field type, choose Number and click next
8. For Field Label and Field Name, type TableID. Check Unique and External ID boxes and click next
9. Leave field-level security as default, click next
10. Leave page layouts as default, click next.
11. After finishing the configuration, the field should appear in the Fields & Relationships page

Configure SQL Server Source

1. Open SSIS Package and drag Data Flow task from Control Flow SSIS Toolbox
   

   Dragging and dropping Data Flow Task into Control Flow

2. Go to data flow designer. Drag and drop OLE DB Source
3. Open the OLE DEB Source and configure as follow
   

   Salesforce using SSIS, Upsert OLE DB source

Configure SSIS Salesforce Destination for Upsert

1. Drag and drop ZS Salesforce Destination
2. Open the ZS Salesforce and configure
3. Connection Manager
   

   Salesforce using SSIS, upsert destination connection manager

4. Component Properties
   

   Salesforce using SSIS, upsert destination component properties

5. Column Mapping
   

   salesforce using SSIS, upsert destination column mapping

6. Click OK
7. Drag and drop three Trash Destination components

Configure Error Handling, Outputs for New, Updated, Failed Records

If you like to capture which rows are inserted and which rows are updated then you can drag Blue arrows coming from Salesforce destination component. You can also connect Red arrow to detect failed records and reason for error.

1. Connect ZS Salesforce Destination for: New records Output, Updated Records Output and Error Output
2. Right click on the joining arrows and enable data viewers on all previous connections
3. Right click in the data flow designer, click Execute Task
   

   Salesforce using SSIS, upsert results

Bulk Delete – Delete data in Salesforce using SSIS

In this section, we’re going to perform a bulk delete on Salesforce using SSIS ZS Salesforce Destination. In this example we’re going to list all our source customers (from previous sections), merge it wit the Salesforce Account object, retrieve the Salesforce Account Id and delete the rows. Delete Operation also requires to map Id field so we will use similar technique we used for Update.

Configure SQL Server Source (OLEDB Source)

1. Go to data flow designer. Drag and drop OLE DB Source
2. Open the OLE DEB Source and configure as follow
   

   salesforce-using-ssis-delete-oledb-source

Configure SSIS Salesforce Source

1. Drag and drop ZS Salesforce Source
2. Open the ZS Salesforce and configure.
3. You can select Query Mode and enter query like this because we only care about two fields for now. Use query mode whenever possible because it transfers less data.
   
   Transact-SQL

   select Id,AccountNumber from account

   1	select Id,AccountNumber from account

Configure Sort Transforms

Now lets sort both previous datasets using Sort Transforms. This is needed to JOIN dataset using Merge Transform (See next section)

1. Drag and drop: 2 Sort and 1 Merge Join components
2. Connect the OLE DB Source to the first Sort component, and configure
   

   Salesforce using SSIS, Update OLEBD sort

3. Connect the Salesforce Source to the second Sort component, and configure
   

   Salesforce using SSIS,update Salesforce sort

Configure Merge Transforms

No next step would be to join previous datasets based on AccountNumber and then we will output Id from Salesforce which is needed for later step.

1. Now drag Merge Transform from SSIS toolbox
   

   Drag SSIS Merge Join Transform from Toolbox

2. Connect both Sort to the Merge component (sort from OLEDB Source most be on the left)
3. Configure the Merge component
   

   salesforce-using-ssis-delete-merge

Configure SSIS Salesforce Destination for Bulk Delete

1. Drag and drop ZS Salesforce Destination
2. Connect the Merge and ZS Salesforce Destination components. Right click the arrow and enable data viewer
3. Connection Manager tab
   

   Salesforce using SSIS, delete destination connection manager

4. Component Properties tab
   

   Salesforce using SSIS, delete destination component properties

5. For Delete purposes, Salesforce Destinations needs to map the ID from the Account object.For more information, please use the following link SSIS Salesforce Destination – Update / Insert with Lookup Fields (Text to Id) 
6. Column Mapping
   

   salesforce-using-ssis-delete-destination-column-mapping

7. Click OK
8. Save all changes
9. Right click in the data flow designer, click Execute Task
   

   salesforce-using-ssis-delete-results

Error Handling

When working on Salesforce with SSIS, we recommend Trash Destination component to help debugging outputs without performing DML on destinations.

Main benefits of usage:

1. Enable data viewers and get live feedback of the data
2. Save the results on different formats (XML, JSON, plain text files).

Conclusion

In this article, We have seen how easy it is to integrate Salesforce using SSIS. Using drag and drop connector you can perform Bulk insert, update, delete and Upsert operations.

Download ZappySys SSIS PowerPack to learn more about other possible scenarios which are not discussed in this article.