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}
Answer
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.