October 23, 2008 at 5:56 am
Hello,
I have a SQL Server 2005 job that runs an SSIS package. When I run the job from Management Studio it runs successfully. I need to now incorporate this job into my stored proceudre. Once the job is initiated from the stored procedure, execution of the SP should stop until the job is done then the following lines of code in the SP should be executed. The package that is run loads data into a table that the stored procedure needs to operate on. How do I go about doing this in my stored procedure?
Thanks and Regards
October 23, 2008 at 7:46 am
Personally, I think it would be easier to incorporate the SP into the job or SSIS package than poll the job status. Any chance you can pull all the logic out of the SSIS package back into the stored procedure or is it pretty complicated?
It will take a little work, but you can use sp_help_job to get the current status of the job, then go into a loop where you wait for the status to go to idle before breaking out. Make sure you pause in the loop (use a WAITFOR) so that you don't constantly hit the server for status and spike the CPU.
Good luck!
Chad
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply