I spent a decent amount of time trying to figure out how to get the DTS ConnectionManager to give me a SQL Connection. I used the following code to grab a SQL Connection from the connection manager:

SqlConnection sConnection = (SqlConnection)Dts.Connections[“MYDB”].AcquireConnection(null);

We pass NULL into the AquireConnection method to specify that we will not be using an existing transaction. Once the SQLConnection is instantiated, it is usually in the OPEN state and does not require you to call the Open() method it.

Easy enough. Just remember to ALWAYS use this method with an ADO.NET connection. Using anything else will result in an error in an InvalidCastException:

Error: System.InvalidCastException: Unable to cast COM object of type ‘System.__ComObject’ to class type ‘System.Data.SqlClient.SqlConnection’.