SSIS API: Introduction to Control Flow objects

Resources:

Control Flow objects for the most part hew closely to the SSIS Designer, but we’ll see some classes here that have no GUI analog, and will have to do some recasting to get specific properties.

The Executable abstract class corresponds to what we see listed in the Control Flow toolbox. Like the ConnectionManager, there’s no strong typing here, and creation is controlled by name strings:

//using Microsoft.SqlServer.Dts.Runtime;
//Package pkg; - a loaded Package
//string cfType = "STOCK:BulkInsertTask"; - the name string of the control flow desired
Executable ex = pkg.Executables.Add(cfType);

Containers:
We’ve got a couple different Control Flow types – containers, and the tasks themselves. There’s only a few containers, but unfortunately there’s no generic Container type, so we have to check for each separately:

if (ex is Sequence)
{
    Sequence seq = ex as Sequence;
}
else if (ex is ForLoop)
{
    ForLoop forloop = ex as ForLoop;
}
else if (ex is ForEachLoop)
{
    ForEachLoop foreachloop = ex as ForEachLoop;
}
else if (ex is Package)
{
    Package package = ex as Package;
}
//else is taskhost of some kind:
else if (ex is TaskHost)
{
    TaskHost th = ex as TaskHost;
}

The Sequence, For and ForEach classes correspond to their GUI equivalents. The Package class itself is derived from the Executable, but obviously we wouldn’t encounter it in this context. The last, TaskHost, is a generic container class that describes any single-task Control Flow. The TaskHost class’ InnerObject property returns an object (of type object) which can be recast into one of the specific SSIS task classes. This makes it much easier to access task-specific properties and methods. Note that it isn’t necessary, however; we could also just access the TaskHost’s Properties key/value store, but we’d have to know the keys to look up.

Iterating through an existing package’s Control Flows is very straightforward: examine each of the Package’s Executables, and check if it’s a container class. If it is, recast and check its children:

public List<Executable> getAllControlFlowObjects(Executables executables)
{
    List<Executable> foundexecutables = new List<Executable>();

    foreach (Executable ex in executables)
    {
        foundexecutables.Add(ex);

        if (ex is Sequence)
        {
            Sequence seq = ex as Sequence;
            foundexecutables.AddRange(getAllControlFlowObjects(seq.Executables));
        }
        else if (ex is ForLoop)
        {
            ForLoop forloop = ex as ForLoop;
            foundexecutables.AddRange(getAllControlFlowObjects(forloop.Executables));
        }
        else if (ex is ForEachLoop)
        {
            ForEachLoop foreachloop = ex as ForEachLoop;
            foundexecutables.AddRange(getAllControlFlowObjects(foreachloop.Executables));
        }
    }

    return foundexecutables;
}

getAllControlFlowObjects(pkg.Executables);

Creation Names:
The above example used a name string with the STOCK
moniker. Many of the components (but not all) are covered by STOCK, and the name string won’t change between SSIS versions. That is, “STOCK:BulkInsertTask” will work in 2005 up through the present.

Since the Containers are all STOCK, it makes sense to handle them in this way:

  • STOCK:FORLOOP //For Loop
  • STOCK:FOREACHLOOP //For Each Loop
  • STOCK:SEQUENCE //Sequence

If we want to create a Control Flow component that isn’t “stock” or analyze existing components, we need to know the component’s “long name”, which can be retrieved from the component’s Assembly Qualified Name:

//using Microsoft.SqlServer.Dts.Tasks.BulkInsertTask;
string name = typeof(Microsoft.SqlServer.Dts.Tasks.BulkInsertTask.BulkInsertTask).AssemblyQualifiedName;

Each Control Flow specific class is in its own namespace. Here’s the namespaces we’ll need:

  • Microsoft.DataTransformationServices.Tasks.DTSProcessingTask.ASExecuteDDLTask //Analysis Services Execute DDL Task
  • Microsoft.DataTransformationServices.Tasks.DTSProcessingTask.DTSProcessingTask //Analysis Services Processing Task
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceBackupTask //Back Up Database
  • Microsoft.SqlServer.Dts.Tasks.BulkInsertTask.BulkInsertTask // Bulk Insert
  • Attunity.SqlServer.CDCControlTask.CdcControlTask //CDC Control
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceCheckIntegrityTask //Check Database Integrity
  • Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe //Data Flow
  • Microsoft.SqlServer.Dts.Tasks.DMQueryTask.DMQueryTask //Data Mining Query
  • Microsoft.SqlServer.Dts.Tasks.DataProfilingTask.DataProfilingTask //Data Profiling
  • ??? //Execute Package
  • Microsoft.SqlServer.Dts.Tasks.ExecuteProcess.ExecuteProcess //Execute Process
  • Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask //Execute SQL
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceExecuteAgentJobTask //Execute SQL Server Agent Job
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceTSQLExecuteTask //Execute T-SQL Statement
  • Microsoft.SqlServer.Dts.Tasks.ExpressionTask.ExpressionTask //Expression
  • Microsoft.SqlServer.Dts.Tasks.FileSystemTask.FileSystemTask // File System
  • Microsoft.SqlServer.Dts.Tasks.FtpTask.FtpTask // FTP
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceHistoryCleanupTask //History Cleanup
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceFileCleanupTask //Maintenance Cleanup
  • Microsoft.SqlServer.Dts.Tasks.MessageQueueTask.MessageQueueTask, Microsoft.SqlServer.MSMQTask //Message Queue
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceNotifyOperatorTask //Notify Operator
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask //Rebuild Index
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceDefragmentIndexTask //Reorganize Index
  • Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask // Script
  • Microsoft.SqlServer.Dts.Tasks.SendMailTask.SendMailTask // Send Mail
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceShrinkTask //Shrink Database
  • Microsoft.SqlServer.Dts.Tasks.TransferDatabaseTask.TransferDatabaseTask // Transfer Database
  • Microsoft.SqlServer.Dts.Tasks.TransferErrorMessagesTask.TransferErrorMessagesTask // Transfer Error Messages
  • Microsoft.SqlServer.Dts.Tasks.TransferJobsTask.TransferJobsTask // Transfer Jobs
  • Microsoft.SqlServer.Dts.Tasks.TransferLoginsTask.TransferLoginsTask // Transfer Logins
  • Microsoft.SqlServer.Dts.Tasks.TransferStoredProceduresTask.TransferStoredProceduresTask // Transfer Master Stored Procedures
  • Microsoft.SqlServer.Dts.Tasks.TransferSqlServerObjectsTask.TransferSqlServerObjectsTask // Transfer SQL Server Objects
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceUpdateStatisticsTask //Update Statistics
  • Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask // WQeb Service
  • Microsoft.SqlServer.Dts.Tasks.WmiDataReaderTask.WmiDataReaderTask // WMI Data Reader
  • Microsoft.SqlServer.Dts.Tasks.WmiEventWatcherTask.WmiEventWatcherTask // WMI Event Watcher
  • Microsoft.SqlServer.Dts.Tasks.XMLTask.XMLTask, Microsoft.SqlServer.XMLTask // XML

Notice that the Execute Package doesn’t have a corresponding class – unfortunately we can’t use this method to obtain the Creation Name for it.
This works well for creating (most) Control Flow components, but for examining an existing package, this won’t be very efficient – we’d have to create an instance of every class’ type, get the creation name, and check it against the Control Flow component that’s in the package. So the final technique is to just write down the name strings and use them directly.
[Note: to obtain the Execute Package name, create the Control Flow using the SSIS Designer, save the package as XML, and examine the document]
With the long Creation Name, we do have to worry about versioning. The following are the long name strings from SSIS 2012:

  • “Microsoft.DataTransformationServices.Tasks.DTSProcessingTask.ASExecuteDDLTask, Microsoft.SqlServer.ASTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Analysis Services Execute DDL Task
  • “Microsoft.DataTransformationServices.Tasks.DTSProcessingTask.DTSProcessingTask, Microsoft.SqlServer.ASTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Analysis Services Processing Task
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceBackupTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Back Up Database
  • “Microsoft.SqlServer.Dts.Tasks.BulkInsertTask.BulkInsertTask, Microsoft.SqlServer.BulkInsertTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Bulk Insert
  • “Attunity.SqlServer.CDCControlTask.CdcControlTask, Attunity.SqlServer.CDCControlTask, Version=1.0.0.0, Culture=neutral, PublicKeyToken=aa342389a732e31c” //CDC Control
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceCheckIntegrityTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Check Database Integrity
  • “SSIS.Pipeline.3” //Data Flow
  • “Microsoft.SqlServer.Dts.Tasks.DMQueryTask.DMQueryTask, Microsoft.SqlServer.DMQueryTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Data Mining Query
  • “Microsoft.SqlServer.Dts.Tasks.DataProfilingTask.DataProfilingTask, Microsoft.SqlServer.DataProfilingTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Data Profiling
  • “SSIS.ExecutePackageTask.3” //Execute Package
  • “Microsoft.SqlServer.Dts.Tasks.ExecuteProcess.ExecuteProcess, Microsoft.SqlServer.ExecProcTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Execute Process
  • “Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Execute SQL
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceExecuteAgentJobTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Execute SQL Server Agent Job
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceTSQLExecuteTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Execute T-SQL Statement
  • “Microsoft.SqlServer.Dts.Tasks.ExpressionTask.ExpressionTask, Microsoft.SqlServer.ExpressionTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Expression
  • “Microsoft.SqlServer.Dts.Tasks.FileSystemTask.FileSystemTask, Microsoft.SqlServer.FileSystemTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // File System
  • “Microsoft.SqlServer.Dts.Tasks.FtpTask.FtpTask, Microsoft.SqlServer.FtpTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // FTP
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceHistoryCleanupTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //History Cleanup
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceFileCleanupTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Maintenance Cleanup
  • “Microsoft.SqlServer.Dts.Tasks.MessageQueueTask.MessageQueueTask, Microsoft.SqlServer.MSMQTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Message Queue
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceNotifyOperatorTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Notify Operator
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Rebuild Index
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceDefragmentIndexTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Reorganize Index
  • “Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Script
  • “Microsoft.SqlServer.Dts.Tasks.SendMailTask.SendMailTask, Microsoft.SqlServer.SendMailTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Send Mail
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceShrinkTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Shrink Database
  • “Microsoft.SqlServer.Dts.Tasks.TransferDatabaseTask.TransferDatabaseTask, Microsoft.SqlServer.TransferDatabasesTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Transfer Database
  • “Microsoft.SqlServer.Dts.Tasks.TransferErrorMessagesTask.TransferErrorMessagesTask, Microsoft.SqlServer.TransferErrorMessagesTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Transfer Error Messages
  • “Microsoft.SqlServer.Dts.Tasks.TransferJobsTask.TransferJobsTask, Microsoft.SqlServer.TransferJobsTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Transfer Jobs
  • “Microsoft.SqlServer.Dts.Tasks.TransferLoginsTask.TransferLoginsTask, Microsoft.SqlServer.TransferLoginsTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Transfer Logins
  • “Microsoft.SqlServer.Dts.Tasks.TransferStoredProceduresTask.TransferStoredProceduresTask, Microsoft.SqlServer.TransferStoredProceduresTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Transfer Master Stored Procedures
  • “Microsoft.SqlServer.Dts.Tasks.TransferSqlServerObjectsTask.TransferSqlServerObjectsTask, Microsoft.SqlServer.TransferSqlServerObjectsTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Transfer SQL Server Objects
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceUpdateStatisticsTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Update Statistics
  • “Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask, Microsoft.SqlServer.WebServiceTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // WQeb Service
  • “Microsoft.SqlServer.Dts.Tasks.WmiDataReaderTask.WmiDataReaderTask, Microsoft.SqlServer.WMIDRTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // WMI Data Reader
  • “Microsoft.SqlServer.Dts.Tasks.WmiEventWatcherTask.WmiEventWatcherTask, Microsoft.SqlServer.WMIEWTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // WMI Event Watcher
  • “Microsoft.SqlServer.Dts.Tasks.XMLTask.XMLTask, Microsoft.SqlServer.XMLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // XML

Wiring it together:
Each of the Control Flow Containers and the Package class has a PreecedenceConstraints collection, which controls the workflow of the Control Flow space. While we could create a PrecedenceConstraint, and then join it into the collection, it’s much easier to simply use the Add method:

public override void SetFlow(ControlFlow source, ControlFlow destination)
{
    Executable sourceEx = source.Ex;
    Executable destEx = destination.Ex;
    PrecedenceConstraints constraints = pkg.PrecedenceConstraints;
    PrecedenceConstraint seqConstraint = constraints.Add(sourceEx, destEx);

}

PrecedentConstraints themselves are more complicated than shown here, but most people leave the default properties (simple constraint, progress upon success, logical AND) so we’ll leave that for another post.

SSIS API: Introduction to Connections

Resources:

Working with Connections via the API, we start to see a little bit more complexity, and some deviation from what the SSIS Designer exposes to us.

A brief aside:
Although we’re working with a .NET API, it’s really a thin veneer over the actual COM object (for instance, the collections will only implement ICollection and IEnumerable). This will explain some of the design choices, and especially many of the error messages we get.

The ConnectionManager is the class describing what the SSIS Designer calls a Connection. Also, like the Variable, the ConnectionManager is weakly typed! And the technique for creating one is a bit idiosyncratic:

//using Microsoft.SqlServer.Dts.Runtime;
//Package pkg; - a loaded Package
//string connType; - the type of connection desired
ConnectionManager newConn = pkg.Connections.Add(connType);

OK, it’s not exactly weakly typed; it’s that each of the (presumably strongly typed COM object) is covered with the same .NET class shell. Just like with the Variable class, the Package (only the Package, this time) has a Connections property to expose all existing ConnectionManagers. However, the Connections.Add() method builds a new ConnectionManager based on the input string (yes, really). The documentation lists some common types, but here’s a full list:

  • “FLATFILE” // flat file
  • “OLEDB” //OLE DB
  • “ADO” //ADO
  • “ADO.NET” //ADO.NET
  • “MSOLAP100” //Analysis Services
  • “CACHE” // Cache
  • “DQS” // DQS
  • “EXCEL” //Excel
  • “FILE” // File
  • “FTP” // FTP
  • “HTTP” // HTTP
  • “MSMQ” // MSMQ
  • “MULTIFILE” // MultiFile
  • “MULTIFLATFILE” // MultiFlatFile
  • “ODBC” // ODBC
  • “SMOServer” // SMO
  • “SMTP” // SMTP
  • “SQLMOBILE” // SQL Server Compact
  • “WMI” // WMI

What’s really going on:
The ConnectionManager .NET class may appear to be weakly typed, but it’s really a wrapper to the COM object, and the COM classes underneath almost assuredly aren’t. The .NET API’s strategy is to expose properties common to all types in the ConnectionManager class, and then serve up all the extra properties in a properties collection.
It also means that checking existing ConnectionManager objects for their types is a little tricky:

foreach (ConnectionManager connmgr in pkg.Connections)
{
    if (connmgr.CreationName == "FLATFILE")
    {
        Console.WriteLine("Package has a FlatFile Connection");
    }
}

The same strings used to create a new connection are what we look for in the CreationName property…except for ADO.NET. The ADO.NET CreationName will look like this:
“ADO.NET:System.Data.SqlClient.SqlConnection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089”

It’s no good looking for this text specially, either. Notice the “System.Data.SqlClient.SqlConnection” substring – that will obviously change, depending on the ADO.NET driver used. My suggestion here is to look for the regular strings first, and then check if the string starts with “ADO.NET:”.

Now that we have a connection, what interesting properties are there?
Name – gets/set the Connection name (otherwise it’ll be a weird-looking GUID).
ConnectionString – gets/sets the Connection’s Connection String
Properties – the DtsProperty collection for all the connection-type-specific stuff

Since the Properties collection is a key/value store, there’s no good way to find out the properties for a given Connection type. I therefore recommend simply creating the Connection you’re interested in, and iterating through them:

foreach (DtsProperty prop in connmgr.Properties)
{
    Console.WriteLine("name: " + prop.Name + ", value: " + (prop.GetValue(connmgr) == null ? "" : prop.GetValue(connmgr).ToString());
}

Once we know the property name, though, we can use it as an index:

DtsProperty formatprop = connmgr.Properties["Format"];
Console.WriteLine("name: " + formatprop.Name + ", value: " + formatprop.GetValue(connmgr));

Note the peculiar syntax here of GetValue():

When you request the value of a property by calling the GetValue method, the object that you must pass as a parameter is the object to which the property belongs. For example, if you are working with an OLE DB connection manager as shown in the following example, and you have created a DtsProperty object for its ServerName property, then you would pass the connection manager object as the parameter to the GetValue method.

which leads to silly-looking code like this:

connmgr.Properties["Format"].GetValue(connmgr);

But because of COM Interop, we’ll never do that in production code, as we need
Deterministic Garbage Cleanup:

DtsProperty formatprop = connmgr.Properties["Format"];
formatprop.GetValue(connmgr);

SSIS API: Getting started…with Variables

Resources:

Starting easy…
First things first, add a reference to the Microsoft.SqlServer.Dts.Runtime, parent of the Microsoft.SQLServer.ManagedDTS assembly (which can be found in the GAC; note the “SQL” capitalization).
Then, creating a variable is simple:

//using Microsoft.SqlServer.Dts.Runtime;
//Package pkg; - a loaded Package
Variable ssisVar = pkg.Variables.Add("myVariable", false, "User", null);

The Variables collection (found in Packages, but also Containers; more on this in a future post) contains an Add method with four parameters:
string name – variable name
bool readOnly – ReadOnly/ReadWrite
string nameSpace – namespace for variable. “User” is typical. “System” is not allowed.
Object val – design-time value for variable. Note that we can set this later

One immediate surprise is that, contrary to what we’d expect from the SSIS Designer, variables are weakly-typed! [The SSIS Designer is sneaky] Setting a value to NULL allows us to punt on making that data type choice until later:

ssisVar.Value = 3;

Once we’ve done this, we can determine the DataType of the variable (note that this is read-only).

Alternately, we can set the Variable to be the result of an
expression:

ssisVar.Value = 3;
ssisVar.EvaluateAsExpression = true;
ssisVar.Expression = "3 * 3";

though it can’t reference other Variables.

Finally, browsing for existing variables is easy:

//using Microsoft.SqlServer.Dts.Runtime;
//Package pkg; - a loaded Package
foreach (Variable variable in pkg.Variables)
{
    Console.WriteLine(variable.QualifiedName);
}

Running SSIS Packages Programmatically, Part 4: Project Deployment Model changes

Resources:

New for 2012, the Project Deployment model is very different from the existing, or Package, deployment model. Read up here if you want more info on the differences.
From the .NET perspective, there’s an entirely new API to use. The bad news is that we have to write more code, but the good news is that the API is much more fully formed. Let’s take a look:

//using Microsoft.SqlServer.Management.IntegrationServices;
SqlConnection sqlconn = new SqlConnection(connectionstring);
IntegrationServices isService = new IntegrationServices(sqlconn);

First, we need to create an instance of the IntegrationServices class, with a connection string to the server; note that the database isn’t needed to be set to SSISDB as the API will figure it out.

Next, we need to specify the Catalog, Catalog Folder, Project, and Package. If you’ve tried to execute a Project-Deployed package via T-SQL, this should look familiar:

Catalog ssisdb_cat = isService.Catalogs["MyCatalog"];
CatalogFolder ssisdb_folder = ssisdb_cat.Folders["MyCatalogFolder"];
ProjectInfo ssisdb_Project = ssisdb_folder.Projects["MyProject"];
PackageInfo pkg = ssisdb_Project.Packages["MyPackage"];

If you want, you can first check if the object exists as each collection has a Contains() method:

Catalog ssisdb_cat = null;
CatalogFolder ssisdb_folder = null;
ProjectInfo ssisdb_Project = null;
PackageInfo pkg = null;

if (isService.Catalogs.Contains("MyCatalog"))
{
    ssisdb_cat = isService.Catalogs["MyCatalog"];
    if (ssisdb_cat.Folders.Contains("MyCatalogFolder"))
    {
        ssisdb_folder = ssisdb_cat.Folders["MyCatalogFolder"];
        if (ssisdb_folder.Projects.Contains("MyProject"))
        {
            ssisdb_Project = ssisdb_folder.Projects["MyProject"];
            if (ssisdb_Project.Packages.Contains("MyPackage"))
            {
                pkg = ssisdb_Project.Packages["MyPackage"];
            }
        }
    }
}

if (pkg == null)
{
    throw new Exception("Package not found!");
}

Next step is to set the project parameters. I recommend setting the logging level to Basic:

Collection<PackageInfo.ExecutionValueParameterSet> packageparams = new Collection<PackageInfo.ExecutionValueParameterSet>();

packageparams.Add(
    new PackageInfo.ExecutionValueParameterSet
    {
        ObjectType = 50,
        ParameterName = "LOGGING_LEVEL",
        ParameterValue = 1
    }
);

A big change is that packages now default to running asynchronously. If this isn’t the desired behavior (probably not), we have to configure synchronous execution:

packageparams.Add(
    new PackageInfo.ExecutionValueParameterSet
    {
        ObjectType = 50,
        ParameterName = "SYNCHRONIZED",
        ParameterValue = 1
    }
);

…unless your package takes more than 30 seconds to run. [note: not yet tested in SQL 2014]

(BTW, these configurations can be done executing via Stored Procedure as well).

If there are parameters to set:

packageparams.Add(
    new PackageInfo.ExecutionValueParameterSet
    {
        ObjectType = 30, //package parameter:
        ParameterName = "InputFile",
        ParameterValue = inputFile
    }
);

packageparams.Add(
    new PackageInfo.ExecutionValueParameterSet
    {
        ObjectType = 20,//project parameter:
        ParameterName = "DestinationDB",
        ParameterValue = destinationDB
    }
);

Finally, execution of the package is simple:

long executionIdentifier = pkg.Execute(false, null, packageparams);

Note the return value; the Executions and Operations collections of the catalog hold info on the outcome of the package execution(again, just like with execution via Stored Procedure). This code looks for Operation Messages with
message level
120 (Errors):

ExecutionOperation execution = poisecat.Executions[executionIdentifier];
List<OperationMessage> errormsgs = execution.Messages.Where(x => x.MessageType == 120).ToList();

And that’s it!
However…switching to Project Deployment model also means that a service is executing our package, one which carries through the client’s credentials. If this is a new execution scenario, and outside machines are accessed, the dreaded-but-obscure Kerberos Double-Hop issue will come into play. Gotcha!

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 IDTSComponentMetaData90IDTSVirtualInputColumnCollection90, 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 (IDTSComponentMetaData100IDTSVirtualInputColumnCollection100). 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…

First! Oh, wait.

Link

Getting started with the SSIS .NET API:

I know what you’re thinking: why? Don’t you know about BIML? Or EzAPI? Both of these programs/libraries solve a lot of problems, and if they solve yours, great. The way I see it, there’s two reasons to look further:

  • I want to do more than BIML/EzAPI can do.
  • I want to understand how the API works. Remember, both BIML and EzAPI (and the SSIS Designer itself) use the API. Plus, maybe we want to write our own component some day.

In the following posts, I’ll be writing about working with SSIS programmatically. We’ll see how to work with the design libraries to create packages and populate them with components, or to read existing packages. We’ll look at the data flow component in particular and understand column lineages. But first…