Click here to Skip to main content
15,860,859 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.2K   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

 
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 
QuestionWhat about calling a package from ASP.NET Pin
FatihSahin19-Dec-06 13:27
FatihSahin19-Dec-06 13:27 
AnswerRe: What about calling a package from ASP.NET Pin
cdhughes12-Feb-07 5:28
cdhughes12-Feb-07 5:28 
GeneralRe: What about calling a package from ASP.NET Pin
ABlokha7718-May-07 1:56
ABlokha7718-May-07 1:56 
GeneralOutstanding! Pin
Wbehning6-Nov-06 12:44
Wbehning6-Nov-06 12:44 
GeneralThanks. Pin
Slackshot16-Oct-06 5:54
Slackshot16-Oct-06 5:54 
You saved me much headache. Had to incorporate some SSIS packages that a DBA made, into my app, that gathers information across our network. Now I don't have to run them myself. Just make a timer.. and wham.
GeneralThanks Pin
Fergal Boden7-Sep-06 0:07
Fergal Boden7-Sep-06 0:07 
General[OLE DB Destination [306]] Error: The AcquireConnection method call to the connection manager failed with error code 0xC0202009. Pin
Balayc29-Jul-06 1:11
Balayc29-Jul-06 1:11 
GeneralWrong references Pin
vavigli20-Jul-06 10:38
vavigli20-Jul-06 10:38 
QuestionRun Time Error While Execute SSIS Package from C#(Windows Application) Pin
Deepu M.I5-Jun-06 15:31
Deepu M.I5-Jun-06 15:31 
AnswerRe: Run Time Error While Execute SSIS Package from C#(Windows Application) Pin
Jeff Modzel6-Jun-06 3:58
Jeff Modzel6-Jun-06 3:58 
GeneralPassing global variables back to C# from SSIS Pin
Nigel-Findlater30-May-06 20:53
Nigel-Findlater30-May-06 20:53 
GeneralRe: Passing global variables back to C# from SSIS Pin
Jeff Modzel31-May-06 2:43
Jeff Modzel31-May-06 2:43 
AnswerRe: Passing global variables back to C# from SSIS Pin
Jeff Modzel1-Jun-06 7:44
Jeff Modzel1-Jun-06 7:44 

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.