Access SSIS package activities and variable through .NET application

Introduction

Hi All,<o:p>

<o:p> 

Here is some cool stuff to set variables defined at SSIS package through .NET application. <o:p>

As I shown how to get list of SSIS packages and executing them manually using .NET application. Probably developer wants more control on SSIS package rather than execution only, like disabling specific activity in SSIS package, setting variable of SSIS package all these through .NET application. How to achieve this…? To know the answer please follow me.      <o:p>

What follow is answers to above questions,<o:p>

1.      Import following assemblies to your application<o:p>

System.Data.SqlTypes;<o:p>

Microsoft.SqlServer.Dts.Runtime;<o:p>

Microsoft.SqlServer.Management.Smo;<o:p>

Microsoft.SqlServer.Management.Smo.Agent;<o:p>

Microsoft.SqlServer.Management.Common;<o:p>

System.Data.SqlClient;<o:p>

2.      Add following segment of code which will return collection of SSIS packages deployed on SQL Server<o:p>

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();<o:p>

<o:p> 

PackageInfos pInfos = app.GetPackageInfos("\\", ConfigurationSettings.AppSettings["ServerIp"].ToString(), "", "");<o:p>

   * ServerIp is subjected to SQL Server IP address                             <o:p>

3.      Now we will add package info (here name of SSIS Package) to drop down list, so that user can select SSIS package and execute the same.<o:p>

       foreach (PackageInfo pInfo in pInfos)<o:p>

                    {<o:p>

                        <o:p>

    if (pInfo.Name != "Maintenance Plans")<o:p>

                            ComboBoxJob.Items.Add(pInfo.Name);<o:p>

<o:p> 

                 } <o:p>

4.      Following code segment shows you how to execute package. This code segment will get invoked when user will click “Execute” button shown at screen shots.<o:p>

        Microsoft.SqlServer.Dts.Runtime.Application app;<o:p>

        app = new Microsoft.SqlServer.Dts.Runtime.Application();<o:p>

            <o:p>

  DTSExecResult pkgResults_Sql;<o:p>

<o:p> 

        Package pkgIn = new Package();<o:p>

<o:p> 

pkgIn = app.LoadFromSqlServer("\\"+ComboBoxJob.SelectedItem.            ToString().Trim(),ConfigurationSettings.AppSettings["ServerIp"].ToString(), "", "", null);<o:p>

<o:p> 

  // This statement set “Test” variable to 12<o:p>

<o:p> 

pkg.Variables["Test"].Value = 12;  <o:p>

<o:p> 

//This statement gets activity present at index 2 in SSIS package and //allows you to modify properties. For example I am disabling activity //present at index 2. So that when this package will execute activity at //index 2 won’t be running  <o:p>

<o:p> 

TaskHost th=(TaskHost)pkgIn.Executables[2];<o:p>

<o:p> 

th.Disable =true;<o:p>

     pkgResults_Sql = pkgIn.Execute();<o:p>

     //Message box will show either Success/Failure <o:p>

     MessageBox.Show(pkgResults_Sql.ToString());   <o:p>

5.      For performing the entire functionality user must have respective permission on MSDB database.<o:p>