So, have you ever needed to execute an SSIS package with an outer project reference? There are some ways to do so. This blog will tell you how you can accomplish this by using a SQL Task from your SSIS toolbox. I have written a stored procedure which will schedule/execute the SSIS package that needs to run. It also checks, in the same procedure, if the execution is ready before it finishes the task. In case of any errors, the errors will be given back to the SSIS package for logging purposes.
But if the package is executing, why wait for it to finish before finishing the task? Well, in many cases there are processes where the packages need to run in a set order, or it has dependencies. When you schedule/execute an SSIS package by SQL, it starts, and the command is successfully executed. So it is then finished. The caller SSIS package then thinks that it’s done, while the SSIS package that has been started is still running. In this example I have a simple package with an Execute SQL Task.
The task executes the stored procedure that will schedule/execute the SSIS packages that needs to start(see command below).
And then the procedure itself with some commentary notes:
So……… That is it. If you have any questions/comments/remarks, please let me know by contacting me. Also, when you are interrested in the code, please let me know! Until next time!