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:

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

Clicking Generate Table DDL will pop up the following:

2-table-ddlAnd clicking Yes will then create the object in your database.

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

3-package-control-flow 4-package-data-flow

(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#.

7 thoughts on “Automate your SalesForce SSIS Staging Packages with C#”

  1. hi,
    I am also trying to develop project for generation of ssis pkgs
    but I am using biml scripts ..

  2. Did you ever get a chance to finish this project? If so, could you provide the code? I have a need to access the Task Factory assemblies and would love to see how you did it. Thanks!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s