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

SQL Server Administration
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.

1 Like
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.

1 Like
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.

1 Like
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.