SSIS Connection Error

Question

I have the following code written into an SSIS Script Task to connect to my SQL database:

 ConnectionManager cm;
 System.Data.SqlClient.SqlConnection sqlConn;
 System.Data.SqlClient.SqlCommand sqlComm;

 cm = Dts.Connections["QUAHILSQ03"];

 sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);

However this line:

 sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);

Returns the following exception:

{"Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface."} System.Exception {System.InvalidCastException}

score 15 · Accepted Answer · answered Aug 09 '12 at 15:10

15

Pretty simple fix: I had created the QUAHILSQ03 Connection Manager as an OLE DB connection. Simply changed it to ADO.NET and my code worked fine.

answered Aug 09 '12 at 15:10

NealR

10,189
61
159
299

score 7 · Answer 2 · answered Aug 09 '12 at 02:01

Seems to me that you are using an OLEDB connection . Acquire connection method on oledb connection manager returns a COM object so you are getting the error .

Try this :

   ConnectionManager cm = Dts.Connections["QUAHILSQ03"];
   IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject 
   as IDTSConnectionManagerDatabaseParameters100;
   OleDbConnection conn = cmParams.GetConnectionForSchema() as OleDbConnection;

You need to use Microsoft.SqlServer.Dts.Runtime.Wrapper namespace With the above approach you cannot retain the transaction .

for more details refer this article

And if you chose to go the [ado.net](http://toddmcdermid.blogspot.com/2011/05/use-connections-properly-in-ssis-script.html) route, Todd McDermid's post ought to help you out — billinkc, Aug 09 '12 at 02:21

score 0 · Answer 3 · edited Jul 22 '22 at 08:24

0

sqlConn = (System.Data.SqlClient.SqlConnection)(cm.AcquireConnection(Dts.Transaction) as SqlConnection);

It works for me

edited Jul 22 '22 at 08:24

buddemat

4,552
14
29
49

answered Jul 20 '22 at 19:22

J David M Cantera

1
1

SSIS Connection Error

3 Answers3