SQLTeam.com | Weblogs | Forums

Looping thru Servers With Dynamic Connection Fails Due to \ in Server Name


#1

Hi experts,
I’m trying to loop thru several server to collect info about those instances.
My ForEach Loop Container is failing due to the \ in the server\instance name. The values are read in from a table
which contains MyServer\Myinstance for example.

Error: The object name “MyServer\MyInstance” is not valid. The name cannot contain any of the following characters: / \ : [ ] . =

Error: The result of the expression "@[User::ServerName]" on property "\Package\Loop thru Servers.Properties[Name]" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

How can I get the connection to work for dynamic server names?
Thanks.


#2

The way I would handle this is add a script task to the ForEach Loop Container in which you change the connection string for the destination connection manager.


#3

Thanks for the suggestion, jotorre. It's the Source server name that causes the error, but I will try that.


#4

Anyone - Is it possible to delete a post or to MOVE a post? I want to repost this in another forum. Thanks


#5

I (and others) can do that for you, just post what Forum you would like it moved to.


#6

Thank you very much, Kristen. The SQl Server Administration forum would be a more appropriate place, I think. If you can move it to that one please. Thanks.


#7

I was able to get my SSIS package working, to loop thru a list of servers and collect information from each instance. But I had to use Linked Servers which is a pain, there has to be a better way.