UMG Developer (3/16/2009)
Jonathan Kehayias (3/13/2009)
The correct way to do this is to create a SQL Agent Job that runs the SSIS/DTS package, and then start that job using sp_start_job. SQLCLR is not needed for this kind of task. You can use the builtin MSDB roles to control security around this, and or impersonation to escalate priviledges as necessary.
I know about that option, but I don't like having to create a SQL Agent Job for every SSIS package just to be able to run it on the server, and the other problem is that we usually need to wait in the procedure until the SSIS package completes. I suppose we could setup a loop calling sp_help_job to find out when it finished and if it errored. Is there something I have missed, an option or something, with sp_start_job that will have it not continue the procedure until the job finishes?
Using xp_cmdshell works well for this without any extra code, but it does open up a big hole.
I agree. We have processes that need to call out to launch SSIS packages and wait for them to complete. Calling to xp_cmdshell is the easy way to syncronously launch and wait for a SSIS package. If there were a SQLCLR way to do it that avoided xp_cmdshell, then that would be great.
... And I'm not too interested in the solution that was mentioned that involves loading up every DLL under the sun so if someone has an alternative that is simpler... maybe...?