Use Connections Properly in an SSIS Script Task

By Todd McDermid, 2011/05/31

There are lots of scenarios in SSIS where you'd want to use a connection within a Script Task. Unfortunately, the closest help documentation - the comments inside the Script Task itself - give you exceptionally poor advice:
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Why is this advice bad? Let me count the ways:

The connection string is hardcoded inside the script.
The connection can't be used with Package Configurations.
The connection string (and name) imply that OLEDB connections can be used.
As presented, the connection won't participate in MSDTC transactions.

(And seriously, "use something like the following"? Hey - I write the vague blog posts here. They're supposed to be writing documentation. ;) )
The poor advice boils down to the fact that it appears to create a "managed" connection, but doesn't truly do so. In fact, if you attempt to use code in the comment as is - you're only halfway there. How do you actually use this "ConnectionManager" object anyway? There's no "Open" method, or Command object - just weird methods and properties that would be entirely unfamiliar to you even if you know .Net data access! And to add insult to injury, even if you did know where to go from here, the example provided will only cause an error, and will never work.
What is a Managed Connection?

Lets start with describing what a "managed" connection is (in the context of SQL Server Integration Services). It is simply a connection that's defined and controlled by an entry in the "Connection Managers" window at the bottom of the design surface in BIDS.

Managed connections have the following benefits:

Their properties (primarily the connection string) can be manipulated with Package Configurations.
Their properties (again, the connection string) can be manipulated with Property Expressions.
They can participate in SSIS (MSDTC) transactions.
The connection is tested by default when the package is loaded, before any (potentially unrecoverable) work is started.

But perhaps most importantly, using a managed connection within a script (properly) informs someone maintaining the package that a connection to an external resource is being used.
How is the Advice Wrong?
The comment in the Script Task counsels you to use Dts.Connections.Add to get a reference to a ConnectionManager object. This is the correct type of object - it's a managed connection. Unfortunately, using that syntax, you're creating a new one, not using one of the managed connections you've already defined in the package. Since it's an entirely new connection, you have to set up the connection string. You could pass the connection string in to the script using an SSIS variable, or read it from a file - but these are both non-standard practices. Another ETL developer or administrator familiar with SSIS won't anticipate your decision to implement a connection that way.
Additionally, since it's a brand new object, none of the regular design time facilities are available to you; Package Configurations, Property Expressions, the TransactionOption property, and runtime validation don't work.
The Right Way to Use Managed Connections
Ignore the comments in the Script Task - use managed connections properly by following these steps.
1. Create an ADO.Net Connection
Add a Connection Manager to your package the same way you'd make one for use in a Data Flow or Execute SQL Task. Right-click in the Connection Managers pane and add a new ADO.Net connection. You can use OLEDB type connections and other connection types - but they're more difficult to use and have fewer capabilities inside a Script.
2. Retrieve a Reference to the Connection
Inside your Script Task, instead of using Dts.Connections.Add, retrieve a reference to an existing connection manager by using the Dts.Connections indexer. For example, if you happen to have a connection manager named "(local).master", then use this code to retrieve a reference to the ConnectionManager object:
ConnectionManager cm = Dts.Connections["(local).master"];
3. For Connections with Providers, Acquire a Connection
(A "provider" means any connection that has code supporting and managing the connection - like an ADO, OLEDB, or Excel connection, but unlike a Flat File connection.)
Even though you now have a reference to the ConnectionManager object, you need to acquire an actual connection to the resource, and request that connection be established in a way that SSIS can manage it. This step is necessary to ensure that your work with the connection participates in transactions and connection pooling (or lack of it).
The specific details for each connection type vary, but the general method is consistent.
// For an ADO.Net ConnectionManager using a SqlClient provider
System.Data.SqlClient.SqlConnection sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
// For an ADO.Net ConnectionManager using an ODBC provider
System.Data.Odbc.OdbcConnection odbcConn = (System.Data.Odbc.OdbcConnection)cm.AcquireConnection(Dts.Transaction);
Be aware that it is NOT necessary (or advisable) to use the Open method on the connection object you just retrieved... it's already open. (Check the connection's State property if you don't believe me.)
4. Use the Connection
At this point, you can use the connection to retrieve datasets, execute commands, or do just about anything else you'd want to do with your connection.
5. Release the Connection Properly
Once you've finished your work, you need to clean up after yourself - but NOT by using the Close method on the connection! You'll need to use the ReleaseConnection method of the ConnectionManager instead.
How Hard Was That?
Hopefully not so hard as to make you want to go the other direction and use completely unmanaged connections in your scripts! To recap, here's a complete code listing of how to correctly use an ADO.Net managed connection for a SqlClient provider within a Script Task:
bool fireAgain = true;
ConnectionManager cm;
System.Data.SqlClient.SqlConnection sqlConn;
System.Data.SqlClient.SqlCommand sqlComm;
int rowsAffected;
// Retrieve the reference to the managed connection
cm = Dts.Connections["(local).master"];
// Request an open connection
sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
Dts.Events.FireInformation(0, "", "Connection is: " + sqlConn.State.ToString(), "", 0, ref fireAgain);
// Do your work
sqlComm = new System.Data.SqlClient.SqlCommand("UPDATE YourTable SET YourColumn = 'SomeValue'", sqlConn);
rowsAffected = sqlComm.ExecuteNonQuery();
// Inform SSIS you're done your work
cm.ReleaseConnection(sqlConn);

Dts.Events.FireInformation(0, "", rowsAffected.ToString() + " rows updated.", "", 0, ref fireAgain);

Dts.TaskResult = (int)ScriptResults.Success;