Click here to Skip to main content
13,141,747 members (54,917 online)
Click here to Skip to main content
Add your own
alternative version


19 bookmarked
Posted 8 Oct 2006

Access SSIS package activities and variable through .NET application

, 8 Oct 2006
Rate this:
Please Sign up or sign in to vote.
This article will drive you, how to access and update properties and variable of SSIS package using .NET application
<!-- Article Starts - DO NOT ADD HTML/BODY START TAGS-->


Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><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


About the Author

Malpani Amol
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

You may also be interested in...


Comments and Discussions

GeneralSSIS execution from a different server Pin
Camilo Serna25-Jun-09 10:34
memberCamilo Serna25-Jun-09 10:34 
QuestionFinding and executing DTS packages not on SQL Pin
baruchl9-Oct-06 21:57
memberbaruchl9-Oct-06 21:57 

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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.170915.1 | Last Updated 8 Oct 2006
Article Copyright 2006 by Malpani Amol
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid