Nice article Andy. I have recently written a stored proc which looks very like yours.
1) It lives in a separate library-type database (I want it clean and fully source-controlled)
2) It uses sp_executeSQL to execute procs in SSISDB
(To avoid cross-database warnings in my database project. SSISDB is, no doubt for good reason, classed as a 'user' database and cannot therefore be added as a system database reference in a database project. I don't want to add SSISDB to source control, so we came up with this workaround.)
3) It runs the package in SYNCHRONIZED mode, then grabs the execution status from catalog.executions and THROWs an error if the status is not 'Succeeded' (7)
That's because I want the proc to fail if the package fails. Running the proc asynchronously does not capture that.
This is important to us because the proc will be executing multiple packages to run ETL jobs.
Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this
If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.