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
script task:

Dim myADONETConnectionManager As IDTSConnectionManager90 = _
Me.Connections.MyADONETConnection
Dim myADOConnection As SqlConnection = _
CType(MyADONETConnectionManager.AcquireConnection( Nothing), SqlConnection

Never mind that I was lucky enough to find
Microsoft.SQLServer.DTSRuntimeWrap.dll and
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?

TIA,
Ken Trock

PS - this was a lot easier as a VB6 program

Hello ktrock,

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:

Imports System.Data.OleDb
Public Class ScriptMain
Public Sub Main()
Try
' test the connection for what you mean by "data isn't available"
Dim conn As New OleDbConnection(Dts.Connections(0).ConnectionStrin g)
conn.Open()
conn.Close()
Catch ex As OleDbException
System.Windows.Forms.MessageBox.Show(ex.Message)
Dts.Connections(0).ConnectionString = "Data Source=.;Initial Catalog=scratch;Provider=SQLNCLI.1;Integrated
Security=SSPI;Auto Translate=False;"
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub
End Class

Can you shed more like on how you are designing the package?

Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

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
Wireless.

Ken Trock


"Kent Tegels" wrote: