Have you ever tried to call an SSIS (SQL Server Integration Services) package programmatically? It's just a piece of cake. Hah, you haven't tried it yet? Ok, see this MSDN article. Hey guys I am not writing this weblog just to put the links that you can easily find them using any search engine. Here are my points.

I create a simple SSIS package with only one script task and two parameters and I want to call this package using C# code. I have already built this package and you can download it from here. The parameters are:

yourName (Int32) that has the value of "Alireza" already hard coded in the package
yourAge (String) that has the value of 35 and again this value is hard coded in the package

If I run this package it displayed a simple message box that shows "Hi Alireza you will be 36 next year". Now lets call this package programmatically:

Assuming that the package is loaded on "C:\SSISProgrammatically\Package.dtsx" this piece of code can run the package successfully. (don't forget to add the Microsoft.SQLServer.ManagedDTS to your project references)

string pkgLocation;
Package pkg;
Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;
pkgLocation =
@"C:\SSISProgrammatically\Package.dtsx";
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkg = app.LoadPackage(pkgLocation, null);
pkgResults = pkg.Execute();

By now it is nothing more than the MSDN example. Here are my points:

We want to pass the variables programmatically to SSIS Package. Here is how:

string pkgLocation;
Package pkg;
Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;
pkgLocation =
@"C:\SSISProgrammatically\Package.dtsx";
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkg = app.LoadPackage(pkgLocation, null);

pkg.Variables["yourName"].Value = "Dennis MacAlistair Ritchie";
pkg.Variables["yourAge"].Value = 66;

pkgResults = pkg.Execute();

OK, It is quite easy but watch out for the  data types. How do you like this example:

 string pkgLocation;
Package pkg;
Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;
pkgLocation =
@"C:\SSISProgrammatically\Package.dtsx";
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkg = app.LoadPackage(pkgLocation, null);

pkg.Variables["yourName"].Value = "Dennis MacAlistair Ritchie";
pkg.Variables["yourAge"].Value = "Ha ha!";

pkgResults = pkg.Execute();

You will end up to a bizarre execution result. It compiles successfully and runs successfully and gives you no execution result. You can simply guess that "Ha ha!" cannot be converted to integer, but where can you track this error? Server Application log? Good guess, but this is what you find in the log:"Package "Package" failed." How much it can help I really don't know but here is my advice.

NEVER RELY ON THE IMPLICIT DATA CONVERSION. Check all your variables in the SSIS package in advance and make sure all the values that you pass to SSIS variables are exactly the same data types as the variables inside the package.

Cheers
Alireza