Configuring SSIS 2008 VB Script to use the same Connection Manager against multiple servers using a variable for the Server Name

Hi,
I am trying to use an  sql server 2008 SSIS  Foreach Loop Container to
run the same script against multiple SQL Servers.
felc 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
	End Sub

End Class

Open in new window

blossomparkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

blossomparkAuthor Commented:
manged to do this without a Script task

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.