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}
Reasons that the package may have failed are as follows: The user account that is used to run the package under SQL Server Agent differs from the original package author. The user account does not have the required permissions to make connections or to access resources outside the SSIS package.
Error 0xC0202009 occurs when SSIS does a parameter cast in SQL Server.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With