I need to start a SSIS package via a stored procedure. I chose to use 'exec dtexec' instead of starting a job to launch the package, so I can have the ability to set variable in the package. My problem is that I need the package to run asynchronously so that the stored procedure will return instead of it hanging or timing out.
What is the best practice for achieving this?
Use the stored proc to start a SQL Server Agent job that invokes the SSIS package...
You can't invoke directly from a stored proc (which is not a good idea anyway) and then have the stored proc terminate. You have to decouple the stored proc execution from the SSIS execution
Setting variables is easy though in SQL Server agent (GUI) and on command line (use /Set)
If you want Async operation with variables, I would construct a table to hold the variables you wanted to pass in, and an Agent job launched SSIS with them. Then you could use sp_start_job to launch that job asynchronously. The package could read the variables it needed from the table, or the job could read them to construct an appropriate launch command. The package could even update the table to indicate results back to BizTalk.