Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

accessing connection managers in script tasks Expand / Collapse
Posted Tuesday, October 2, 2012 8:20 AM


Group: General Forum Members
Last Login: Tuesday, January 13, 2015 9:11 AM
Points: 166, Visits: 733

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?


Post #1367072
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse