As far as launching the package, I think we have three reasonably safe options. The first is xp_cmdshell, which would probably work fine and be pretty simple to implement on a queue (probably the best option in my opinion).
The second would be to create a single job agent job for each allowed broker process and have the queue processor call sp_start_job only if the job is not already running (pretty easy to check).
The third option would be to use a CLR procedure to call a web service and have the web service start the SSIS package. You would have to dive into C# a bit and create your own web service to start an SSIS package, but it is not much code. Doing it this way, you would not have to allow unsafe assemblies because the web service would be out of process.
Keep in mind that starting a job is asynchronous, and would require additional coding in both the job and the application that starts the processes, as well as creating a job for every SSIS package that needs to be run. Using xp_cmdshell is synchronous, and requires that xp_cmdshell access be enabled (requires security configuration). It does require the writing of a command line for each package, but that can also be stored in a table and pulled when needed.
BOL shows code for running SSIS packages in an application. I am hoping, though not encouraged, that it may be possible to use a CLR stored procedure to run a SSIS package inside the database by simply passing the name of the package to run. We won’t need 100’s of jobs, or enable xp_cmdshell to accomplish the task. I am, however, pragmatic and will use which ever method turns out to be the best/easiest/quickest/etc way to meet the goals of the system: to reliably import the data from numerous source systems in the most efficient way possible and to recover from hardware/network failures/outages both planned and unplanned.
Here is another option;
Add to favorites
