Thanks for the outline.
I was wondering how if you had an example or knew of an example for number 3.
I haven't done that before. I would think you would just be creating a string with the usual connection string properties (i.e. catalog, user, password, etc.)
In the Connection Managers Tab at the bottom of the Control Flow design surface, right-click and create a new OLE DB Connection. Go through the wizard to create it. Once created, click on the new connection in the Connection Managers Tab and press F4 (or right-click and go to Properties). In the Properties pane expand the Expressions tree item. You'll want to create a new Expression for the ConnectionString property. Have you worked with SSIS expressions before? Here is a decent demo
that makes use of the Derived Column transformation to demo expressions. You can ignore the Deived COlumn piece nd just focus on what he's doing with Expressions. The expression language is the same as what you'll be using. In the end your expression will resemble your typical connection string with the server-name variable your Foreach loop uses thrown in to substitute for the server in the connection string.
Where would the ole db source handle the insert into the ole db destination?
This will happen inside the Data Flow Task noted in steps 5, 6 and 7 in my outline.
__________________________________________________________________________________________________There are no special teachers of virtue, because virtue is taught by the whole community. --Plato