Accessing ADO.Net Connection Managers from an SSIS script task / script component is pretty easy – you just need to cast the object returned from AcquireConnection() to the appropriate class (i.e. SqlConnection if you’re using SQL Native Client).
SqlConnection conn = (SqlConnection)Dts.Connections["adonet"].AcquireConnection(null);
If you can’t use ADO.Net for some reason, and are using OLEDB connection managers, it’s a little trickier. Since the AcquireConnection() method of the OLEDB connection manager returns a native COM object, I didn’t think there was a way to make this work, but today someone showed me how to do it!
By casting the Connection Manager’s InnerObject to the IDTSConnectionManagerDatabaseParameters100 interface (IDTSxxx90 in 2005), you can call the GetConnectionForSchema() method to return an OleDbConnection object.
2008 (C#):
ConnectionManager cm = Dts.Connections["oledb"];
IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject as IDTSConnectionManagerDatabaseParameters100;
OleDbConnection conn = cmParams.GetConnectionForSchema() as OleDbConnection;
2005 (VB) :
Dim cm As ConnectionManager
Dim cmParam As Wrapper.IDTSConnectionManagerDatabaseParameters90
Dim conn As OleDb.OleDbConnection
cm = Dts.Connections("oledb")
cmParam = CType(cm.InnerObject, Wrapper.IDTSConnectionManagerDatabaseParameters90)
conn = CType(cmParam.GetConnectionForSchema(), OleDb.OleDbConnection)
<br/>.csharpcode, .csharpcode pre<br/>{<br/> font-size: small;<br/> color: black;<br/> font-family: consolas, "Courier New", courier, monospace;<br/> background-color: #ffffff;<br/> /*white-space: pre;*/<br/>}<br/>.csharpcode pre { margin: 0em; }<br/>.csharpcode .rem { color: #008000; }<br/>.csharpcode .kwrd { color: #0000ff; }<br/>.csharpcode .str { color: #006080; }<br/>.csharpcode .op { color: #0000c0; }<br/>.csharpcode .preproc { color: #cc6633; }<br/>.csharpcode .asp { background-color: #ffff00; }<br/>.csharpcode .html { color: #800000; }<br/>.csharpcode .attr { color: #ff0000; }<br/>.csharpcode .alt <br/>{<br/> background-color: #f4f4f4;<br/> width: 100%;<br/> margin: 0em;<br/>}<br/>.csharpcode .lnum { color: #606060; }
Note, you’ll need to add a reference to the Microsoft.SqlServer.DTSRuntimeWrap assembly to get the IDTSConnectionManagerDatabaseParameters100 interface. If you’re doing this in a script task, you’ll need to prefix the Microsoft.SqlServer.Dts.Runtime.Wrapper namespace (or use fully qualified names) so that it doesn’t conflict with the namespace for the VSTA proxy classes.
Keep in mind that there are a couple of limitations with this approach:
- You won’t be able to enlist in the current transaction
- This connection doesn’t honor the “retain same connection” setting
ADO.Net is still the recommended connection manager type for scripts, but I found this to be a nice work around.
