C# Code And SSIS Package

At Times we feel the necessity of accessing, modifying and running a SSIS package from our Web Application scope.
This articles details out the code for working on some of the essential portions of a SSIS package.

We are referring the package that we created and deployed in my previous article
http://akshayperception.blogspot.in/2012/05/ssis-package-deployment.html

Objectives: Modify Data Source query, Variable values and Connection Manager.
Assumption: The Data Source in the package is fetching data based on a simple query from the Employee table
Query: SELECT EmpNo, EmpName FROM tblEmployee where EmpNo in (1,3,5) 
Table: tblEmployee

EmpNo	EmpName
1	Samuel
2	Jimmy
3	Sam
4	Linda
5	Cera

Let's demonstrate this with a piece of code.

Refer the necessary SSIS Assemblies:

using Microsoft.SqlServer.DTS.Runtime;  //dll Microsof.SqlServer.ManagedDTS
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;//dll Microsoft.SqlServer.DTSPipelineWrap.dll
//Both of the above dlls can be referred from the folder DTS inside your SQL Server folder in Program files.

C# Code

Application application = new Application();
//Pass the necessary parameters to Load the SSIS package from SQL Server where you deployed
Package pkg = application.LoadFromSqlServer(, , , , null);

//Populating the package variables
pkg.Variables["EmployeeCode"].Value = 2
        
//Below line gives the handle of the Data Flow Task in our package.
//Similarly you can get the handle for any other task you wish
Executable execDFT = pkg.Executables["Data Flow Task"];

TaskHost thost =  execDFT as TaskHost;

MainPipe dataFlowTask = thost.InnerObject as MainPipe;
//In the below line we are trying to take the handle for Data Source in our Data Flow Task
//Please notice the name of the Data Source is "OLE DB Source". If you change it to some other name you can 
//access by that name too.
IDTSComponentMetaData100 component = dataFlowTask.ComponentMetaDataCollection["OLE DB Source"];
CManagedComponentWrapper instance = component.Instantiate();

//In the below line notice that we got the existing query written in the Data Source
//Note: The query for the Data Source is present under the property SqlCommand
string query = Convert.ToString(component.CustomPropertyCollection["SqlCommand"].Value);

//Below we added a where condition that we will try to add in the Data Source query.
string strWhereClause = " where EmpName like '%S%' and ";

//In the below lines we are trying to provide our custom conditions by replacing existing //where clause by the one we created at run time and setting it up in the Data Source. 
//That's really cool!
query = query.Replace("where", strWhereClause);
instance.SetComponentProperty("SqlCommand", query);

//Changing the Excel path defined in the connection manager
pkg.Connections["Excel Connection Manager"] .Properties["ServerName"].SetValue(pkg.Connections["Excel Connection Manager"], "C://MyExcel.xls");

//Executing the package
DTSExecResult result = pkg.Execute();

You just ran your package by modifying it as per your wish.