I'm trying to use an OLEDB connection in a script task. There's a fair amount of documentation on this topic, such as:
The only problem is I can't get anything to work. (Nothing like examples that produce errors.)
For example, when I mimic the code in the first link above with this VB.NET code:
Dim myConnection As SqlConnection
Dim cm As ConnectionManager
cm = Dts.Connections("DB_cm")
myConnection = DirectCast(Dts.Connections("DB_cm").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)
myConnection = DirectCast(cm.AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)
Then debug the package, I get this error on the "Directcast" line:
System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'.
Does anyone have an idea why I can't get code in a Script Task to execute which also appears in the Microsoft KB?