Running SSIS Packages Programmatically, Part 3: Configurations (Package Deployment)

Package vs. Project deployment (assuming you’re running SQL 2012) is beyond the scope of this post.¬†Explaining the ins and outs of package configuration options is also beyond the scope of this post. So instead I’m going to assume you’ve already set up the package to have a configuration, and entered the necessary variables/connections/settings into it:

pkg.EnableConfigurations = true; //tells package to use configuration values instead of current settings
pkg.ImportConfigurationFile(configfileWithPath); //configuration file on filesystem

This method uses the XML Configuration file option, and presumes you’ve made one already (best done in the SSIS Designer). To use the other configuration options (SQL Server, indirect XML and SQL Server via environmental variables), a little more work must be done:

pkg.EnableConfigurations = true; //tells package to use configuration values instead of current settings

Configurations configurations = package.Configurations;
Configuration configuration = configurations[configname]; //name of configuration
configuration.ConfigurationType = configtype; //type DTSConfigurationType
configuration.ConfigurationString = configstring; //dependent on configtype
configuration.PackagePath = packagefile; //full path to package

Check out this site for more info. But back to the XML Configuration file option. This is my preferred way to work with packages in production that need runtime configuration. Since the file is XML, it’s easy to work with:

//using System.Xml;
XmlDocument ssisConfigDoc = new XmlDocument();
string configFileFull; //full path of config file
ssisConfigDoc.Load(configFileFull);
XmlNodeList nodeList = ssisConfigDoc.DocumentElement.ChildNodes;

foreach (XmlElement element in nodeList)
{
    if (element.Name == "Configuration")
    {
        switch (element.Attributes["Path"].InnerText)
        {
            case @"\Package.Variables[User::MyVar].Properties[Value]":
                element.ChildNodes[0].InnerText = variablevalue;
                break;
            case @"\Package.Connections[MyConnection].Properties[ConnectionString]":
                element.ChildNodes[0].InnerText = connectionstring;
                break;
            default:
                break;
        }
    }
}

ssisConfigDoc.Save(configFileFull);

And that’s it! We now have our config (file) set to the proper values, and ready to run. Let’s put it all together:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using System.Xml;

namespace SSIS2012.PackageRuntime
{
    public class PackageExecution
    {
        public void ExecutePackage(
            string destinationDB,
            string destinationServer,
            string pkgFolder,
            string pkgName, 
            string configfile, 
            string variableToSet,
            string newvalue
            )
        {
            //load SSIS package:
            string pkgLocation = pkgFolder + pkgName + @".dtsx";
            Application app = new Application();
            Package pkg;
            DTSExecResult pkgResults;

            MyEventListener eventListener = new MyEventListener();
            pkg = app.LoadPackage(pkgLocation, eventListener);

            //set up config:
            XmlDocument ssisConfigDoc = new XmlDocument();
            ssisConfigDoc.Load(configfile);
            XmlNodeList nodeList = ssisConfigDoc.DocumentElement.ChildNodes;

            foreach (XmlElement element in nodeList)
            {
                if (element.Name == "Configuration")
                {
                    switch (element.Attributes["Path"].InnerText)
                    {
                        case @"\Package.Variables[User::" + variableToSet + "].Properties[Value]":
                            element.ChildNodes[0].InnerText = variablevalue;
                            break;
                        default:
                            break;
                    }
                }
            }

            ssisConfigDoc.Save(configfile);

            //execute:
            pkgResults = pkg.Execute();

            switch (pkgResults)
            {
                case DTSExecResult.Success:
                    //do something here:
                    break;
                case DTSExecResult.Failure:
                    //do something here:
                    break;
                case DTSExecResult.Canceled:
                    //do something here:
                    break;
                case DTSExecResult.Completion:
                    //do something here:
                    break;
                default:
                    break;
            }
        }
    }

    class MyEventListener : DefaultEvents
    {
        public DtsObject ErrorSource;
        public string ErrorSubcomponent;
        public string ErrorDescription;
        public int ErrorCode;

        public override bool OnError(
            DtsObject source,
            int errorCode,
            string subComponent,
            string description,
            string helpFile,
            int helpContext,
            string idofInterfaceWithError)
        {
            ErrorSource = source;
            ErrorSubcomponent = subComponent;
            ErrorDescription = description;
            ErrorCode = errorCode;

            //Console.WriteLine("Error in {0}/{1} : {2}", source, subComponent, description);
            return false;
        }
    }
}

Running SSIS Packages Programmatically, Part 2: Running

Resources:

Now that we’ve got our package loaded, executing it is pretty straightforward, although there is a more parameterized way to do it:

//using Microsoft.SqlServer.Dts.Runtime;
Package pkg;
DTSExecResult pkgResults;
pkgResults = pkg.Execute();

The DTSExecResult enumerable captures a simple Success/Failure/Canceled status (technically, Completion – Success or Failure – is an enum value, but you won’t see it).

And that’s it!

Let’s put it all together:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using System.Xml;

namespace SSIS2012.PackageRuntime
{
    public class PackageExecution
    {
        public void ExecutePackage(
            string destinationDB,
            string destinationServer,
            string pkgFolder,
            string pkgName
            )
        {
            //run SSIS package:
            string pkgLocation = pkgFolder + pkgName + @".dtsx";
            Application app = new Application();
            Package pkg;
            DTSExecResult pkgResults;

            MyEventListener eventListener = new MyEventListener();
            pkg = app.LoadPackage(pkgLocation, eventListener);
            pkgResults = pkg.Execute();

            switch (pkgResults)
            {
                case DTSExecResult.Success:
                    //do something here:
                    break;
                case DTSExecResult.Failure:
                    //do something here:
                    break;
                case DTSExecResult.Canceled:
                    //do something here:
                    break;
                case DTSExecResult.Completion:
                    //do something here:
                    break;
                default:
                    break;
            }
        }
    }

    class MyEventListener : DefaultEvents
    {
        public DtsObject ErrorSource;
        public string ErrorSubcomponent;
        public string ErrorDescription;
        public int ErrorCode;

        public override bool OnError(
            DtsObject source,
            int errorCode,
            string subComponent,
            string description,
            string helpFile,
            int helpContext,
            string idofInterfaceWithError)
        {
            ErrorSource = source;
            ErrorSubcomponent = subComponent;
            ErrorDescription = description;
            ErrorCode = errorCode;

            //Console.WriteLine("Error in {0}/{1} : {2}", source, subComponent, description);
            return false;
        }
    }
}

Running SSIS Packages Programmatically, Part 1: Loading

Resources:

A brief history of the API: When SQL Server 2005 came out and introduced SSIS (replacing DTS), the developer API was presented, too. Any programmers who were working with this in depth would have seen classes like¬†IDTSComponentMetaData90,¬†IDTSVirtualInputColumnCollection90, etc. What’s the …90 suffix? SQL Server 2005 is version 9.0. Uh, oh. So that means… Yes, in SQL Server 2008, all those class names changed (IDTSComponentMetaData100,¬†IDTSVirtualInputColumnCollection100). Thankfully, they’ve stayed the same ever since. All that is to say, the code I’m writing for SQL Server 2012 will for the most part recompile without issue for SQL Serve 2008 (and R2) by just repointing to the proper assemblies. On to coding:

//using Microsoft.SqlServer.Dts.Runtime;
Application app = new Application()
Package pkg;

The Application class is the main SSIS program. If you’ve ever worked with the Interop classes for Office, you’ve seen something similar. This class will be used to load the Package class, and eventually run it, but there’s a few different ways we can do this. But we need one more class:

class MyEventListener : DefaultEvents
{
    public DtsObject ErrorSource;
    public string ErrorSubcomponent;
    public string ErrorDescription;
    public int ErrorCode;

    public override bool OnError(
        DtsObject source,
        int errorCode,
        string subComponent,
        string description,
        string helpFile,
        int helpContext,
        string idofInterfaceWithError)
    {
        ErrorSource = source;
        ErrorSubcomponent = subComponent;
        ErrorDescription = description;
        ErrorCode = errorCode;

        //Console.WriteLine("Error in {0}/{1} : {2}", source, subComponent, description);
        return false;
    }
}

(This will be discussed in a sec)

1. Load from the File System:

string pkgLocation; //package directory, file name
MyEventListener eventListener = new MyEventListener();
pkg = app.LoadPackage(pkgLocation, eventListener);

The LoadPackage method takes two parameters:

string fileName,
IDTSEvents events //can be null

The IDTSEvents interface specifies methods that are called when events during package execution fire. But this is an interface, so we’ll need an actual class. Luckily, Microsoft provides a base one to inherit from: DefaultEvents.

2. Load from the Integration Services Server, i.e. the SSISDB database. This is new for 2012.

string pkgLocation; //package directory, file name
MyEventListener eventListener = new MyEventListener();
pkg = app.LoadFromDtsServer(pkgPath, SSISServerName, eventListener);

The LoadFromDtsServer method takes three parameters:

string sPackagePath, //package path
string sServerName, //SSIS Server Name
IDTSEvents events //can be null

Otherwise the methods are the same.

2. Load from SQL Server (the old SSIS Package Store: MSDB/Server Directory):

string pkgLocation; //package directory, file name
MyEventListener eventListener = new MyEventListener();
pkg = app.LoadFromSqlServer(pkgPath, SSISServerName, eventListener);

The LoadFromSqlServer method takes four parameters:

string packagePath,
string serverName,
string serverUserName,
string serverPassword,
IDTSEvents events //can be null

Ultimately, all three get you to the same place: a package loaded into memory. What to do with it in the next post…