Click here to Skip to main content
15,861,340 members
Articles / Database Development / SQL Server

Execute SQL Server 2005 Integration Services package from C#

Rate me:
Please Sign up or sign in to vote.
4.60/5 (24 votes)
25 May 20062 min read 338.3K   2.3K   81   38
Learn how to load and execute a SQL Server 2005 Integration Services package from C# code

Introduction

There are many ways to execute a SQL Server 2005 Integration Services (SSIS) package.  You can use the command line utility DTExec.exe or its window equivalent, DTExecUI.exe. A package can be executed within SQL Server Business Intelligence Studio (Visual Studio) or from a SQL Server Agent Job Step. A package can also be executed from .NET code!

Example SSIS Package

For the purposes of demonstration, I created a simple package that takes some data out of a SQL Server AdventureWorks database and dumps it into a flatfile. The package also has one variable.

Sample screenshot

Sample screenshot

In any kind of "real world" scenario, your package will usually be driven by a configuration file.  An SSIS configuration file is an XML file with a .dtsConfig extension that contains settings you can apply to a package (without actually changing or editing the package). In my example files, you can edit the configuration file with your SQL Server and flat file connection information and run the package. You'll never have to edit the actual package file. Here's a very good tutorial on configuration file syntax. Configurations can also be stored in a SQL Server table, but I won't cover that here.

Let's Start Coding...

You need to add a reference to Microsoft.SQLServer.ManagedDTS.dll. I believe that this DLL is only installed on a machine that has SQL Server components installed. 

Sample screenshot

The amount of code to execute a SSIS package is surprisingly small and concise. Notice that I added a using directive for the Microsoft.SqlServer.Dts.Runtime namespace.

C#
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;

namespace ExecuteSSIS
{
  class Program
  {
    static void Main(string[] args)
      {
        Application app = new Application();
        //
        // Load package from file system
        //
        Package package = app.LoadPackage("c:\\ExamplePackage.dtsx", null);
        package.ImportConfigurationFile("c:\\ExamplePackage.dtsConfig");
        Variables vars = package.Variables;
        vars["MyVariable"].Value = "value from c#";

        DTSExecResult result = package.Execute();

        Console.WriteLine("Package Execution results: {0}",result.ToString());

        //
        // Load package from SQL Server
        //
        Package package2 = app.LoadFromSqlServer(
            "ExamplePackage","server_name", "sa", "your_password", null);

        package2.ImportConfigurationFile("c:\\ExamplePackage.dtsConfig");
        Variables vars2 = package2.Variables;
        vars2["MyVariable"].Value = "value from c# again";

        DTSExecResult result2 = package2.Execute();

        Console.WriteLine("Package Execution results: {0}", 
             result2.ToString());
     }
  }
 }

First, you create an Application object, which provides access to the DTS (Integration Services) runtime.  Then you use the Application object to load a package from either the file system or from SQL Server, I've demonstrated both. Once you have the package loaded into a Package object, you call the ImportConfigurationFile() method to load and apply the configuration file to the package. The Package object also has a Variables collection that provides access to the package's variable. Finally, to actually execute a package, call the Execute() method.

Conclusion

This article was meant to quickly demonstrate how to load and execute a package. There is much, much more you can do with the managed DTS namespace. There are objects and additional namespaces that allow you to load and inspect packages or even create new packages from .NET code.

History

  • 25th May, 2006: Initial post

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


Written By
Web Developer
United States United States
Technical Architect
Sungard HE

Comments and Discussions

 
GeneralMy vote of 5 Pin
Kanasz Robert25-Sep-12 22:47
professionalKanasz Robert25-Sep-12 22:47 
Generalerror when attempting to load ExamplePackage.dtsx Pin
Roger C Moore6-Aug-09 10:49
Roger C Moore6-Aug-09 10:49 
Generalfaster extraction and loading on SSIS. Pin
blackpower2k725-Apr-09 19:54
blackpower2k725-Apr-09 19:54 
GeneralSSIS Pin
raj kumar Arakala4-Jan-09 22:37
raj kumar Arakala4-Jan-09 22:37 
GeneralMy vote of 1 Pin
Roberthchapman10-Dec-08 5:43
Roberthchapman10-Dec-08 5:43 
QuestionRunning SSIS Package on remote SQL get an error : Pin
Yonathan Masovich2-Nov-08 22:10
Yonathan Masovich2-Nov-08 22:10 
QuestionRunning the SSIS from a remote PC Pin
JosephL18-Oct-08 5:39
JosephL18-Oct-08 5:39 
QuestionCan we use this code to execute a package which is on a remote machine ??? Pin
ash.shastry17-Sep-08 7:18
ash.shastry17-Sep-08 7:18 
QuestionCan we control the execution of Package Pin
Sanmukheswar Rao3-Sep-08 21:17
Sanmukheswar Rao3-Sep-08 21:17 
QuestionIs there a way to catch errors in code, while package fails ? Pin
Yonathan Masovich12-Aug-08 1:06
Yonathan Masovich12-Aug-08 1:06 
AnswerRe: Is there a way to catch errors in code, while package fails ? Pin
ilievboyan15-Aug-08 3:38
ilievboyan15-Aug-08 3:38 
GeneralRe: Is there a way to catch errors in code, while package fails ? Pin
ilievboyan15-Aug-08 3:43
ilievboyan15-Aug-08 3:43 
Generala little problem Pin
aliagayleon8-Aug-08 10:47
aliagayleon8-Aug-08 10:47 
QuestionAnyone have the code to do this in VB? Pin
REBMCAD10-Jul-08 8:18
REBMCAD10-Jul-08 8:18 
QuestionIt's very good!But how about the excel files? Pin
Chaney.Wu12-Mar-08 0:25
Chaney.Wu12-Mar-08 0:25 
GeneralGood article Pin
David Catriel3-Mar-08 6:01
David Catriel3-Mar-08 6:01 
Generalhelp needed in case of ssis package.. Pin
tasumisra11-Dec-07 1:02
tasumisra11-Dec-07 1:02 
GeneralRe: help needed in case of ssis package.. Pin
James_Isisw15-Dec-07 21:56
James_Isisw15-Dec-07 21:56 
QuestionSSiS Problem Urgent!! Pin
Papai.T10-Oct-07 22:29
Papai.T10-Oct-07 22:29 
GeneralRun SSIS package from C# using Windows authentication Pin
AlisterN5-Aug-07 15:38
AlisterN5-Aug-07 15:38 
QuestionExecuting SSIS package always gets failure Pin
Brillientking3-Jul-07 19:31
Brillientking3-Jul-07 19:31 
AnswerRe: Executing SSIS package always gets failure Pin
saom6-Jul-07 11:15
saom6-Jul-07 11:15 
QuestionWhat if Configuration is type DTSConfigurationType.SqlServer PinPopular
JMB12289-May-07 11:07
JMB12289-May-07 11:07 
QuestionError creating application Pin
cibercharlie9-May-07 4:44
cibercharlie9-May-07 4:44 
AnswerRe: Error creating application Pin
Roger C Moore6-Aug-09 10:06
Roger C Moore6-Aug-09 10:06 
Charlie, you need to run on a machine that has SQL Server installed because only these machines have the required Microsoft.SQLServer.ManagedDTS.dll installed on them.

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.