Well, I got that part to work.
I had to put quotes at the beginning and the end of the expression.
After that, it evaluated properly.
Now the last part:
1. Load the list of instances you want to iterate over into SSIS however you're doing that now.
2. Add a Foreach loop to your Control Flow and configure it to iterate over your list of instances however you're doing that now.
3. Setup an OLE DB Connection object and setup an Expression for the ConnectionString property to have it point to the current server in your Foreach loop.
4. Setup another OLE DB Connection object and have it point to the DB where your "master" table resides.
5. Add a Data Flow Task to your Control Flow.
6. In your Data Flow Task, use an OLE DB Source to get the data from each server
7. In your Data Flow Task, use an OLE DB Destination to load the data from the OLE DB Source into a table.
If I have an OLE DB source which is dynamically set #6 and a OLE db destination #7, how am I running a query?
For every server, I need to:
1) Run a query
2) Take the resultset of that query and place it into the OLE destination table.
Thanks guys for your help in this.