sqlssisbids

SSIS Connection Error

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

  • 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.