I recently discovered how to utilise the processing resources of multiple SQL Servers simultaneously through SSMS. (From a brilliant Thread on this forum). Where one registers multiple servers, from View --> Registered Servers (in SSMS), see pic below.
My Question is, is it possible to encapsulate SQL statements in an Execute Sql command container, that then utilises the resources of multiple Servers simultaneously in SSIS, just as it can be done within SSMS?
SSIS can certainly execute tasks against multiple servers at the same time, but you can't use multiple servers to share the execution of a single task. If you want the same SQL to execute against multiple server simultaneously in the same way multiserver execution works in SSMS, you must create a separate execute SQL task for each, you can't share that one task. If you wanted to change the executed SQL statement, this would mean editing all of the tasks containing that statement. But you can avoid this by making the executed SQL statement be sourced from an SSIS variable. That way, you only need to change a single variable.
To execute multiple tasks at the same time, simply drag multiple execution arrows out of the parent task. If there is no parent task, just drop the execute SQL tasks down on the design surface with no connection between them. James Serra wrote a quick blog entry about controlling parallel execution in SSIS quite a while ago, but the information is still current.
