accessing connection managers in script tasks

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » accessing connection managers in script tasks...

accessing connection managers in script tasks

Rate Topic

Display Mode

Topic Options

Author

Message

cafescott

Posted Tuesday, October 02, 2012 8:20 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 20, 2013 2:17 PM
Points: 109, Visits: 498

Hello,

I'm trying to use an OLEDB connection in a script task. There's a fair amount of documentation on this topic, such as:
http://msdn.microsoft.com/en-us/library/ms136018.aspx#Y718
http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/dbf15acc-ed02-442d-8b56-4e170d7b89a2
http://blogs.msdn.com/b/mattm/archive/2008/08/22/accessing-oledb-connection-managers-in-a-script.aspx

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)

--OR--
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?

thanks,

Post #1367072

« Prev Topic | Next Topic »

Permissions