I've had one project where I had to replace a WCF web service with a single package. This web service was running across something like 130 locations when I originally wrote it. It is up to 150 locations now.
The package needed to reach out to different MS SQL servers across all locations and perform some tasks according to the state of the data in the remote server. I Googled this and looked at StackOverflow, but nobody had exactly what I needed.
I want to put the solution in this blog, because I can never remember how to do this. I end up firing BIDS with the solution that has this package.
Here is the finished package:
The magenta overlay on the connection is from an AddIn called BIDS Helper. It is free and you can get it from http://bidshelper.codeplex.com/
First let's create a user variable called "CurrentServer."
The key for the whole is the connection. In BIDS, open the Properties window, then select the connection. It should look like this:
Click on the button with the ellipsis (the three dots), to bring up the Expressions Editor.
Click in the area below the label "Property", to use the drop down control for properties, and select "ConnectionString."
Click on the ellipsis button to invoke the Expression Builder dialog.
You will enter your connection string in the Expression textbox. Here is a generic connection string to get you started:
"Data Source=" + @[User::CurrentServer] + ";Initial Catalog=YOURSERVER;Provider=SQLNCLI10.1;Uid=YOURUSER;Pwd=YOURPASSWORD"
This is assuming that all your servers are using the same user name and password. Replace YOURSERVER, YOURUSER, and YOURPASSWORD with the appropriate values. Click on the "Evaluate Expression" button to make sure you got this right.
Click OK in the Expression Builder and the Expression Builder Editor to get back to your package. You should now see this property populated in the Properties window. You might need to expand the Expressions property to see it. Once expanded, it will look like this:
Let's now prepare the data flow task to get our server names. First let's create a varible to hold the server name recordset, which we will use to look through the servers:
Next, let's edit the "Get list of server credentials" task in the Control Flow tab. The data flow will look like this:
Edit the "Server Name Query" OLE DB source. I put something simple just to get this going. You can use the UNION technique in the screenshot, or if you are lucky, there is a database somewhere in your company that contains all of this information.
Click OK. Now, let's edit the recordset destination:
Make sure that the VariableName is using the rsServers variable that we created earlier. I've highlighted it in red above. Select the "Input Columns" tab and make sure that the ServerName column is select like so:
Next, let's edit the ForEach Loop task and configure it to use the recordset from the previous step:
Click on "Collection" on the left, then select "Foreach ADO Enumerator" from the drop down. Next we will use the variable we setup earlier:
Next, click on "Variable Mappings." Click on the space below the "Variable" label to activate the drop down. Now select the user variable that will hold the server names. In our case, this is the "CurrentServer" variable.
We are done setting this up. Now anything that we setup inside the ForEach Loop task will work on all the servers we have used from the query that gets our server names.
Now you can see why I forget how to do this. At least it is now in my blog. 
