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)
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:
ADO.Net is still the recommended connection manager type for scripts, but I found this to be a nice work around.
Could you please explain what you did to access the DTSRuntimeWrap assembly? I'm trying to programmatically get information about an OLEDB provider that I used to connect to a SAS datasource, so ADO.NET is not an option for me.
In 2008, you should be able to simply add it as an assembly reference for your project (DTSRuntimeWrap can be found in the GAC).
In 2005, you'll need to add the assembly to the .NET framework directory. There is a good writeup on how to do this on sqlservercentral.com.
http://www.sqlservercentral.com/articles/SQL+Server+2005+-+SSIS/3180/
Wow, I didn't know that connection strings of OleDB Connection Managers can be retrieved from a script task until today. This changes everything. Hehe. Thanks a lot for this post!!!
Hey Matt Masson ,
I am getting following error...
ConnectionManager cm = Dts.Connections["oledb"];
error:The Type of namespace name could not be found???
as well as The name "DTS" does not exist in current context
Please Help
The code above works with a Script Task. For a script component, you'll need to configure your connection managers on the editor page.
See: msdn.microsoft.com/.../aa337080.aspx
Hi Matt, may I know how to call a stored procedure inside the script task with your OLEDB connection manager as shown in the example above? Thanks!
It's alright, I've discovered how to call a sp inside th script task, thanks for this great article though, it helps me alot!