Connect to multiple DB servers programatically

[wbaltz]

I have an environment where I need to execute a single SQL statement on multiple DBs across multiple DB Servers.  I have the multiple DB part figured out using the Dynamic Input tool.   What I'm now trying to do is streamline my workflow to only have a single Input Data tool and then loop through a list of DB servers to query the data.

What I have been able to get working is using a Macro output, along with Dropdown/Action interface tool to change the name of the server Alias name in the connection string (see attached image showing this).  What I would like to know is, is it possible to leverage this concept to cycle through a list of server alias names I pass into the macro somehow based on the alias names of my already established Data Connections.

It seems that some sort of Batch Macro with a control Parameter is the way to go here, I just can't seem to figure out how I can (if it is even possible) pass the connection alias names into the control parameter

[JoeS · Accepted solution]

@wbaltz  You are right, it is the control parameter you need to use.

What you will need to do is remove the list box and replace that with the control parameter.

Then outside the macro in the workflow you are using it in, as the data stream, send in the list of alias' you want to batch through.

The action you have should be able to remain the same.

[wbaltz]

Wow, thanks @JoeS it really was that simple!  I wasn't grasping how the Control Parameter actually worked and didn't realize that once saved with a Control Parameter, the macro would allow a flow of data into it when executing.  Your recommendation pointed me in the right direction and it worked perfectly....thanks!

[JoeS]

You're welcome. Once you have grasped batch macros they become incredibly useful.

Having the data itself drive the process can be extremely handy!