This article deals with executing SSIS package stored in a SQL Server database using C# code.
There article is the second part on how to execute an SSIS package using C# code. In our earlier part1 we have discussed on how we can execute an SSIS package from a file syatem. In this article we will discuss on how we can execute a SSIS package that is stored in a SQL Server database.
Scenario is first we will build a SSIS package. Then we will create a console application to execute using c#.
Here is the first part of this article - http://www.dotnetfunda.com/articles/article1228-execute-ssis-package-using-csharp-code-part1-.aspx
So lets start ....
1. First create a SSIS package .. Please refer to
http://www.dotnetfunda.com/articles/article1214-conditional-split-in-ssis-.aspx and create an SSIS package which reads a file and splits the data into two files there by separating all male students into one file and all female students into another.
2. Now we will deploy the SSIS package to SQL Server. When we say in SQL Server it means it stores the packages in the MSDB database. First goto project properties and set createDeploymentUtility to true, and build the solution. Goto the deployment folder and double click on the project manifest file to deploy the SSIS package into SQL Server.
3. Once the deployment is over, Now once that is done we will create a console application to execute this SSIS package programmatically.
Start your Visual Studio and Add a console application, Right click on the References and add a reference to Microsoft.SQLServer.ManagedDTS.dll
4. Now lets add code to the console app as shown below.
5. On Executing the console app, the code executes the package and returns the result on the console window.
6. Incase we have any Configuration files or Variables used in the SSIS package we need to add few more lines of code to pass the same. Since we didnot have any configuration file or variables used in the SSIS package, it is much easier on our part.
Incase you have any configuration file defined for a package you can load the configuration details using the code below.
And in case you need to pass any variables you can do the same using the below lines of code.
Variables variables = package.Variables;
variables["MyPackageVariable"].Value = "DotNetFunda";
Isn't it so simple...
Hope you all liked this article. Do comment on this ..