Resources:

• http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.connectionmanager.aspx

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