I am trying to use an sql server 2008 SSIS Foreach Loop Container to
run the same script against multiple SQL Servers.
I have one connection manager and I want to use this to connect to all the servers.
I have a variable set up so that the vb script task (ST current connection manager) can get a new servername on each iteration of the loop.
My problem is how to write the vb script so that it uses the same connection manager but changes the servername.
The code below looks for a Connection Manager with the variable name.
What I need is code to change the server name in the connection manager from the variable...
hope that makes sense.
any guidance appreciated.
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
' To open Help, press F1.
Public Sub Main()
' Add your code here
Dim strConnect As String
strConnect = "Data Source=" + Dts.Variables("User::ServerName").Value.ToString + ";Provider=SQLNCLI.10.1;Integrated Security=SSPI;Auto Translate=False;"
'Dts.Connections("OLEDB").ConnectionString = strConnect
Dts.TaskResult = ScriptResults.Success