Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

accessing connection managers in script tasks Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2012 8:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 160, Visits: 698
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 »

Add to briefcase

Permissions Expand / Collapse