Error connecting to SSIS

Question

I have the following code written in SSIS Script The task for connecting 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); 

It returns the following exception:

{"It is not possible to pass a COM object of type" System .__ ComObject "to a class type of" System.Data.SqlClient.SqlConnection ". Instances of types that represent COM components cannot be assigned to types that do not represent COM components, but they can be passed to interfaces if the underlying COM component supports QueryInterface calls to the IID of the interface. " } System.Exception {System.InvalidCastException}

Answer 1

A pretty simple fix: I created QUAHILSQ03 Connection Manager as an OLE DB connection. Just changed it to ADO.NET and my code worked fine.

Answer 2

It seems to me that you are using OLEDB connection. Acquiring a connection method in Oledb Connection Manager returns a COM object so you get an error.

Try the following:

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

You need to use the Microsoft.SqlServer.Dts.Runtime.Wrapper namespace. Using the above approach, you cannot save transaction .

for more details, refer to this article