You will need a location that is ALWAYS available, like maybe a flat file on
a file server. Set up a table that lists two columns: Servername and Status.
Set up a Data FLow that queries this 'control' table with something like:
SELECT ServerName FROM ControlTable
WHERE Status = 'Active'
Send that to a Recordset Destination (connected to an Object type variable).
Enclose your code with a For Each loop on the Control Flow, downstream of
the Data Flow above. Set the Foreach ADO Enumerator, and loop over Records in
a Table. Use the Variable Mapping page to map the one ServerName column in
the table to a variable.
Now, for the OLE DB Server Connections, you will need to set up an
Expression that uses the ServerName variable in the ConnectionString
property. Copy the whole string first and paste it into the Expression
Editor, surrounded by Quotes. Then replace the pertinent part with the
Variable.
Example:
"Provider=blah;Server=" + [User::ServerName] + ";Initial Catalog = blah..."
Getting more fancy, you could add a Password column in the Control table,
map it to a Password variable, and edit your string to pass a new password to
each server.
Good luck. Hope this helps.
=====
Todd C
Post by M BourgonWe're trying to build an SSIS package that will take a list of
servers, connect to each & run code.
However, if a server is down for whatever reason, the task fails.
What we'd like to do is make some sort of note in a table, skip that
server and go on to the next.
Any suggestions? Many thanks.
.