Click here to Skip to main content
15,509,585 members
Articles / Database Development / SQL Server
Posted 8 Oct 2006


19 bookmarked

Access SSIS package activities and variable through .NET application

Rate me:
Please Sign up or sign in to vote.
2.43/5 (4 votes)
8 Oct 20062 min read
This article will drive you, how to access and update properties and variable of SSIS package using .NET application


Hi All,<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>







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>


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>



    if (pInfo.Name != "Maintenance Plans")<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>


  DTSExecResult pkgResults_Sql;<o:p>


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


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


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


pkg.Variables["Test"].Value = 12;  <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>


TaskHost th=(TaskHost)pkgIn.Executables[2];<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>


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Written By
Web Developer
India India
I am a .NET developer cum designer. I work for Zensar Technologies Ltd. Pune (INDIA), I consult, I develop, I debug and besides that I speak at conferences and user groups and I also write. I have written some articles on WCF, WF, SSIS, Compression decompression, CLR Store Procedure, SQL Server Reporting Services 2005, Extended grid and lot more to count.
Reach to me

Comments and Discussions

GeneralSSIS execution from a different server Pin
Camilo Serna25-Jun-09 11:34
Camilo Serna25-Jun-09 11:34 
QuestionFinding and executing DTS packages not on SQL Pin
baruchl9-Oct-06 22:57
baruchl9-Oct-06 22:57 
Hi Malpani,
first of all thank you for tackling this topic.
I'm a very novice user of SSIS and I understand i can deploy the DTS files into a file server and not as part of the SQL sarver.

Do you know how to handle such scenario - exploration and execution of a package residing on the server from a .NET C# program?

And one more question on the same topic - do you know how can this be done in design time? if I have a sloution w/ a regular class library project and an SSIS project, can I somehow reference the SSIS project from the library project and execute the DTS package from there?

Many thanks,

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.