Get list of SSIS package and execut, from .NET application

Introduction

Hi All,<o:p>

<o:p> 

Here is some cool stuff to call SSIS package from .NET application.<o:p>

Usually SSIS packages deployed on SQL Server or may resides on file system as .dtsx file. General scenario to execute SSIS Package is to deploy on SQL Server and then attach Schedule to it for execution. But some times client may ask to execute SSIS package through user interface, since no technical knowledge required for connecting to SQL Server…?  Finding SSIS package on SQL Server…? And executing SSIS package from SQL Server…?<o:p>

Now questions are, 

How to get list of SSIS packages deployed on SQL Server…?

How to execute SSIS package manually…?<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>

     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>

     Following figures shows how application looks like.