CODEWITHSUNDEEP
sqlt-sqlssis
aceinthehole
aceinthehole

Reputation: 5222

Start SSIS Asynchronously from a stored proc

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?

Upvotes: 0

Views: 698

Answers (2)

Todd McDermid
Todd McDermid

Reputation: 1680

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.

Upvotes: 1

gbn
gbn

Reputation: 432421

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)

Upvotes: 0

Related Questions