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}

Solution 1

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.

Solution 2

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