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:
- Truncate staging table
- 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:
- Truncate staging table
- Lookup (from a meta data store) the last date loaded
- Populate staging table with records from SalesForce with CreatedDate or LastModified after the last date loaded
- 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#.
Reblogged this on Sutoprise Avenue, A SutoCom Source.
Can you please provide a link to download this application.
I need to create ssis packages using Taskfactory control.
Hi Rakesh, I’ll need to dig up the code from my archives – I’ll try and do it some time this week. Regards, Ken
[…] I used the Partner to build an application which dynamically generates SQL Server Integration Services packages based on SalesForce meta data. You can read more about it here. […]
hi,
I am also trying to develop project for generation of ssis pkgs
but I am using biml scripts ..
Are you trying to create packages to access SalesForce?
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!