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

Leave a Reply

Your email address will not be published. Required fields are marked *