July 10, 2015 at 12:20 pm
I'm building a BIDS package that runs multiple sql agent jobs on other servers. When they're all done, the BIDS job should move on to other data manipulation steps.
I'm starting the jobs fine and dandy, but the control flow fires off the job and moves on immediately. I need it to wait for completion before moving on. I thought I had a solution involving WAITFOR DELAY '00:00:10', but one of the jobs runs for about 15-20 minutes and the WAITFOR logic causes a timeout. Does anyone know what to do?
SERVER A
SERVER B
SERVER C
Think of SERVER A as the control center. It's where the dtsx will reside and it will be consumed by an agent job on that server. The dtsx will fire off the SERVER B and SERVER C agent jobs and then perform several other tasks after execution of the remote jobs is complete.
July 11, 2015 at 6:31 am
It sounds to me like you've probably got half the problem solved, which is that starting a SQL Agent job is generally asynchronous (i.e. you call sp_start_job, which immediately returns). I assume you're doing some kind of check to see if the job is still running, and if it is, waiting a period before checking again?
In terms of the timeout, how are you executing the SQL that starts the job and waits? More than likely there's a timeout setting involved, which if you set to 0 (zero) will essentially be unlimited.
Senior DBA - Gateway Ticketing Systems
Co-Founder - Do It Simply Software
July 13, 2015 at 8:36 am
I'm trying to use SSIS to create a "command central" type of deal, so I'm just using an Execute Package Task, but I could certainly use an Execute SQL Task instead if it will give me options to make this sucker work. I've been hung up on it for awhile now.
July 13, 2015 at 8:45 am
You either need an extra job step in the remote jobs so that they tell you when you've finished, or you need to poll msdb on the remote servers to find out when they've finished.
John
July 13, 2015 at 9:40 am
I am looking at msdb to find out if the job completed, but it times out. I'm asking about the timeout, not how to check.
July 13, 2015 at 9:44 am
What's happening when it times out - is it being blocked? What is the blocking process doing?
John
July 13, 2015 at 9:47 am
According to MS (https://msdn.microsoft.com/en-us/library/ms186757(v=sql.110).aspx) there aren't any parameters representing timeout. :unsure:
July 13, 2015 at 9:54 am
No, the timeout is set within the package, not by the sp_start_job stored procedure. How long before it times out - is it long enough for you to run sp_whoisactive and see what they wait type is that's causing it to run for so long?
John
July 13, 2015 at 10:57 am
Sorry maybe I was confused, are you using an Execute SQL task to start the jobs, or an Execute Package task? It sounds like the latter; how are you using the Execute Package task to start the remote agent job? Is it running a remote package that is then starting the job?
Senior DBA - Gateway Ticketing Systems
Co-Founder - Do It Simply Software
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply