Using connections in Script Task
-
Hi all,
Not sure what I'm donig wrong here, but I'm trying to use once of my Connections in my package in a script task.
I got this sample right from Microsoft site:
http://msdn.microsoft.com/en-us/library/ms136018.aspx
Public Sub Main()
Dim myADONETConnection As SqlClient.SqlConnection
myADONETConnection = _
DirectCast(Dts.Connections("Test ADO.NET Connection").AcquireConnection(Dts.Transaction), _
SqlClient.SqlConnection)
MsgBox(myADONETConnection.ConnectionString, _
MsgBoxStyle.Information, "ADO.NET Connection")
'Dim myFlatFileConnection As String
'myFlatFileConnection = _
' DirectCast(Dts.Connections("Test Flat File Connection").AcquireConnection'(Dts.Transaction), _
' String)
' MsgBox(myFlatFileConnection, MsgBoxStyle.Information, "Flat File Connection")
Dts.TaskResult = ScriptResults.Success
End Sub
I commented out the flat file one since I'm interested in only using the connection I have to the SQL database. I changed Test "ADO.NET Connection" to the name of my connection. The exception I get is:
Error: The script threw an 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.
Not sure what this means, but has anyone had any success doing this?
Thanks,
Strick
-
Verify what is the type of connection you are using. This doesn't look like ADO.NET connection.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Is it defined as a .net connection or an OLEDB connection, if it is the latter then the code will not work, you have to define the connection consistent with the type.
CEWII
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply