Execute SQL Server 2005 Integration Services package from C#

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.

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. 

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

 Collapse

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 aVariables 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 DTSnamespace. There are objects and additional namespaces that allow you to load and inspect packages or even create new packages from .NET code