OleDB Connection from ConnectionManager in Data Flow Script Component SSIS2008
Hi I'd like to know how I can connect to an oleDb Connection in the connectionManager of a script component in SSIS2008? I need to execute a sql query and populate a variable. I keep getting the following error upon execution: Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{624862CB-55F9-4A92-965F-62BC4935296A}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).
March 3rd, 2010 11:13pm

Try this: Accessing OLEDB Connection Managers in a Script.
Free Windows Admin Tool Kit Click here and download it now
March 4th, 2010 1:39am

I've tried it(C# 2008 example), but can't get it to work? Am I referencing the wrong assemblies? Has anyone used the example successfully? Thanks
March 4th, 2010 8:33am

You'll have to provide much more information than you "can't get it to work". I can't see your screen from here.
Free Windows Admin Tool Kit Click here and download it now
March 4th, 2010 9:54am

Here's the code exactly like the example: ConnectionManager cm = Dts.Connections["oledb"]; IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject as IDTSConnectionManagerDatabaseParameters100; OleDbConnection conn = cmParams.GetConnectionForSchema() as OleDbConnection; I get the following build errors: ...Dts.Connections["oledb"]; : 1. Can't find 'Dts'. The 'Dts class' doesn't exist in script component as it does in the script task I changed the code to look like this, but can't cast IDTSConnectionManager to ConnectionManager. If I don't use ConnectionManager, I can't access the InnerObject. Note: myOleDbConnection is my connection specified in ConnectionManagers. Microsoft.SqlServer.Dts.Runtime.ConnectionManager cm = (Microsoft.SqlServer.Dts.Runtime.ConnectionManager)Connections.myOleDbConnetion; Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 cmParams = (Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100)cm.InnerObject as Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100; OleDbConnection conn = (OleDbConnection)cmParams.GetConnectionForSchema();
March 4th, 2010 11:17am

You shouldn't need to use "Dts." - the "Connections" property should exist without that prefix.For more info, look here: Connecting to Data Sources in the Script Component.
Free Windows Admin Tool Kit Click here and download it now
March 4th, 2010 11:50am

Thanks. Decided to go the connectionstring route. It's not my preferred solution, but it works. Cheers
March 4th, 2010 1:02pm

Hey garriths, i have exactly the same problem. Can you explain what do you meen with the "connectionstring route" ? thanx
Free Windows Admin Tool Kit Click here and download it now
March 10th, 2011 5:50pm

I second that. I would like to know how you solved this problem. I have no issue in script task, but script componets give me trouble.
June 22nd, 2011 12:25am

He's referring to the fact that you can extract the connection string from the Connection Manager and create an unmanaged connection with it. However, I'd really like to know what kind of trouble you have in the script component. Please post a new thread with your particular situation. Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
June 22nd, 2011 12:36am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics