Create and Execute SSIS Packages using .NET

Recently, I was approached by one of my friends with a query where the requirement was to create SSIS packages using .NET, and the same packages were to be executed using .NET. All the advantages that the SSIS runtime exposes is very evident to those who use SSIS thru BIDS. But this is foreign to .NET developers, who may even not be aware of what SSIS package exactly is and how it can be created and controlled using a managed language.

In SSIS there's an interesting concept called MDDE i.e. Meta Data Driven ETL. When any attempt is made to create and control SSIS packages using any managed i.e. .NET complaint language, it more or less emulating MDDE. A very nice project can also be found on codeplex for MDDE. In MDDE, the process goes as below:

1. Create a package using BIDS
2. Keep the package generic enough to be used as a template for all the further packages
3. Set the properties you want to be configurable
4. Create an instance of the template
5. Set values for properties you want to change
6. Generate packages for your instance

In order to create packages using Visual Studio, a reference to the Microsoft.SQLServer.DTS.Runtime which is contained in the Microsoft.SQLServer.ManagedDTS.DLL file. Add a reference to this file in the .NET program, and using (C#) or import (VB) statement. Luckily, the good news for .NET developers is that SSIS publishes entire object model for package development which is as good as programatically creating an Infragistics Grid and setting it's properties.

I will not go into more details of demonstrating an entire example with code of developing SSIS package programatically, as there are already nice examples available on the Microsoft website. This really opens up a new potential for third party vendors and curious developers to come out with new products that can leverage SSIS package development using managed languages.