Incrementally Loading Data from Salesforce

As a business owner and someone who is responsible for sales, reporting in SalesForce.com is challenging to say the least. The reporting is tightly restricted to a few objects and the interface can't create the beautiful reports that those in the data industry are used to seeing. Most importantly, I can't merge the data with my existing data sources like data from accounting, HR or our other internal systems. This is why most people pull data from SalesForce.com into a central reporting database to do more advanced reporting. This blog shows you my method for pulling data into a single repository for further reporting.

The Incremental Load Pattern Control Table

The first thing you'll need for any incremental load in SSIS is create a table to hold operational data called a control table. This control table in my case uses the below script to manage the ETL.

Here's an example of what my table looks like after the Case object has been loaded.

The SSIS ETL

1. Determining when the last load of the package ran (reading the control table)
2. Reading the data out of SalesForce
3. Setting the last load date in the control table

Steps 1 and 3 will be Execute SQL tasks and the second step will be a standard Data Flow task. For Step 1, use an Execute SQL task that queries your control table as shown in the below query. Save the results of the query by changing the Result Set option to Single Row. Lastly, go to the Result Set tab and save the result to a variable called dtLastLoadDate.

SELECT LastLoadDate
FROM SalesForceControlTable
WHERE SourceObject = 'Case'

Next, add a Data Flow task. You can use the Task Factory SalesForce.com Source for SSIS to retreive data out of SalesForce.com. If you're in Visual Studio, Task Factory is free to use. In the below screenshot, you can see that I selected the Case entity from SalesForce. The query below the entity then automatically populates. I also wanted to retrieve only an incremental amount of data (never retrieve the same rows twice unless they've changed). So, I add the following WHERE clause to the SOQL query to only retreive rows that have changed in SalesForce.com since the latest time I ran this package. I personally prefer to do a date range so I'm protected from not missing rows that might be changed while the package is running. The StartTime variable you see below does just that. This WHERE clause is asking, "Which cases have been changed since the last time I ran the package until when I started this package?"

WHERE LastModifiedDate > <@User::dtLastLoadDate>
and LastModifiedDate < <@System::StartTime>

In my case, I sent the data to a Task Factory Upsert Destination. Doing so allows me to conditionally update or insert the data.

Now that the hard part of the package is complete, add a final Execute SQL task to set the date on the control table back to the curent run of the pacakge.  I use the below query to set that date to the start date of the package. The question mark is substituted with the StartTime variable in the Variable tab.

UPDATE SalesForceControlTable
SET LastLoadDate = ?
WHERE SourceObject = 'Lead'

Once run, the data flow only pulls all the rows over the first time. After that, subsequent runs of the package only pull over changed rows.

The complete control flow tab looks like the below. This same pattern can be used to extract data incrementally for nearly any data source.

In the below video Shawn Harrison walks through the complete example end-to-end.