Click here to Skip to main content
Click here to Skip to main content

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

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.

License

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 on:-a.malpani@zensar.com

Comments and Discussions

 
GeneralSSIS execution from a different server PinmemberCamilo Serna25-Jun-09 10:34 
Hi Thanks for your information. i just have a question, is posible to handle what you wrote in that code from a different serer???
I have try for some time now to connect to a different server not the local but i keep getting the same permision error. When i saw your code i said well he is using something diferent for the conection, maybe this is how it works. so i tried but i get this warning telling me that the ConfigurationSettings.AppSettings is obsolete:
Warning 1 'System.Configuration.ConfigurationSettings.AppSettings' is obsolete: 'This method is obsolete, it has been replaced by System.Configuration!System.Configuration.ConfigurationManager.AppSettings'
 
However if i compiled this way i get the error
 
"Object reference not set to an instance of an object" right on the line:
PackageInfos packinfo = this.app.GetPackageInfos("\\", ConfigurationSettings.AppSettings["192.168.163.XXX"].ToString(), "", "");
if I erase the tostring() method will work only locally. If you can give me any light I will really apreciated.
 
THANKS,
cAMILO SERNA.
QuestionFinding and executing DTS packages not on SQL Pinmemberbaruchl9-Oct-06 21:57 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 8 Oct 2006
Article Copyright 2006 by Malpani Amol
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid