Automate your SalesForce SSIS Staging Packages with C#

This week I’ve been making some changes in one of my data warehouse projects, with the aim of making it a long lived and easy to maintain solution.

One of the big changes that I made was the way I was staging the data from my different sources, and this meant re-working a whole bunch of SSIS packages. Specifically there were a bunch that pull data from SalesForce which needed to be cleaned up.

So I got thinking, “all of these staging tables are loaded the same way, surely I should be able to automate this”.

In this post I’m going to show you a tool that I wrote in C# which connects to SalesForce, downloads metadata on all your objects, then generates staging tables and finally SSIS packages to load these tables.

The Manual Process

As I was re-working my packages, I was also recreating a lot of the staging tables. This was a very tedious process, as I had to go back and forth looking at the mapping of SalesForce types to their counterpart in SQL Server.

After creating the staging tables, the process of loading them from SalesForce is always the same:

1. Truncate staging table
2. Populate staging table with records from SalesForce (for a subset of column)

Down the line I have plans to extend this, so I’m only loading changed objects from SalesForce, which would result in the following process:

1. Truncate staging table
2. Lookup (from a meta data store) the last date loaded
3. Populate staging table with records from SalesForce with CreatedDate or LastModified after the last date loaded
4. Update the last data loaded value

The Solution

I designed a simple application in C#, this is what it looks like:

The example shown here is looking at the Opportunity object with a number of columns selected.

Clicking Generate Table DDL will pop up the following:

And clicking Yes will then create the object in your database.

Clicking Generate Package will create the SSIS package which follows the above design pattern:

(I’m using the SalesForce Source from PragmaticWorks)

When it comes to extending the design pattern to only load new and/or changed objects all I will need to do is update my package generator and re-create the packages. Saving me the hassle of going through each package and making the change.

Future Plans

The application needs a bit of a tidy up before I can make it available for download, it also uses the PragmaticWorks assemblies for SSIS so I need to find out if I can bundle them for download.

It was an interesting exercise building this application so I’m planning on writing a few posts on how to programmatically build SSIS packages with C#.