Access to connection manager - 10-09-2007 , 11:44 AM
Hello, I have a very simple situation where I want to pump data from a source
SQL Server to another. If the data isn't available in the source then try
another source which has the same exact structure. This code from SQL 2005
BOL is supposed to allow me internal access to my connection managers in a
Dim myADONETConnectionManager As IDTSConnectionManager90 = _
Dim myADOConnection As SqlConnection = _
CType(MyADONETConnectionManager.AcquireConnection( Nothing), SqlConnection
Never mind that I was lucky enough to find
Microsoft.SQLServer.DTSPipelineWrap.dll in another folder and copied them to
the only location where references can be added from. Me.Connections is not a
valid reference in my script. Where might this be or is there an equivilent?
Does it matter that I'm trying to connect from my desktop to a SQL 2000 DB?
PS - this was a lot easier as a VB6 program
Re: Access to connection manager - 10-09-2007 , 05:01 PM
This code was probably written for a script task, not an external program.
In the case that I want to round-robin data sources like you suggest there,
I start the package with a ScriptTask that does the test. Here's a slightly
different example of the script within that task:
Public Class ScriptMain
Public Sub Main()
' test the connection for what you mean by "data isn't available"
Dim conn As New OleDbConnection(Dts.Connections(0).ConnectionStrin g)
Catch ex As OleDbException
Dts.Connections(0).ConnectionString = "Data Source=.;Initial Catalog=scratch;Provider=SQLNCLI.1;Integrated
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
Can you shed more like on how you are designing the package?
Re: Access to connection manager - 10-10-2007 , 02:09 PM
Kent, this snippet is really helpful. It's working for me. In my data flow I
have a simple source to destination pump. In my control flow I need a script
like this to determine what connection manager should act as the source.
I see you're with DevelopMentor. I think we took at least 1 course with you
guys. Do you by chance have a location in Parsippany, NJ? I'm at Verizon
"Kent Tegels" wrote: