Access SSIS package activities and variable through .NET application

Introduction

Hi All,

 

Here is some cool stuff to set variables defined at SSIS package through .NET application.

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.      

What follow is answers to above questions,

1.      Import following assemblies to your application

System.Data.SqlTypes;

Microsoft.SqlServer.Dts.Runtime;

Microsoft.SqlServer.Management.Smo;

Microsoft.SqlServer.Management.Smo.Agent;

Microsoft.SqlServer.Management.Common;

System.Data.SqlClient;

2.      Add following segment of code which will return collection of SSIS packages deployed on SQL Server

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

 

PackageInfos pInfos = app.GetPackageInfos("\\", ConfigurationSettings.AppSettings["ServerIp"].ToString(), "", "");

   * ServerIp is subjected to SQL Server IP address                             

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.

       foreach (PackageInfo pInfo in pInfos)

                    {

                       

    if (pInfo.Name != "Maintenance Plans")

                            ComboBoxJob.Items.Add(pInfo.Name);

 

                 }

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.

        Microsoft.SqlServer.Dts.Runtime.Application app;

        app = new Microsoft.SqlServer.Dts.Runtime.Application();

           

  DTSExecResult pkgResults_Sql;

 

        Package pkgIn = new Package();

 

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

 

  // This statement set “Test” variable to 12

 

pkg.Variables["Test"].Value = 12; 

 

//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 

 

TaskHost th=(TaskHost)pkgIn.Executables[2];

 

th.Disable =true;

     pkgResults_Sql = pkgIn.Execute();

     //Message box will show either Success/Failure

     MessageBox.Show(pkgResults_Sql.ToString());  

5.      For performing the entire functionality user must have respective permission on MSDB database.