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!