July 29, 2011 at 8:55 am
I've written a package that runs an execute sql agent job task and then sends out an email by a send mail task. Problem is it sends the email from the send mail task before the Sql Agent job has actually finished running. Anyone know how to stop the execution of subsequent tasks until the agent job has completed please?
August 1, 2011 at 12:04 am
Griffster (7/29/2011)
I've written a package that runs an execute sql agent job task and then sends out an email by a send mail task. Problem is it sends the email from the send mail task before the Sql Agent job has actually finished running. Anyone know how to stop the execution of subsequent tasks until the agent job has completed please?
Starting a SQL job is asynchronous, meaning that it doesn't wait for the job to finish. It only waits for the confirmation that the job has actually started.
So you have to implement this in a more synchronous way. Probably by polling every x seconds a result table or something similar. As soon as the expected result has been found, you can send your e-mail.
You can also scourge the net for finding synchronous code for starting a job. Probably someone has already written such a thing.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 1, 2011 at 2:21 am
Thanks for the reply. I found this post recently, which I'm trying to implement as an extra step between my sql agent job and email sends:
http://www.sqlservercentral.com/Forums/Topic1128044-148-1.aspx
What do you think?
August 1, 2011 at 3:03 am
Anyone know how to stop the execution of subsequent tasks until the agent job has completed please?
Why don't you configure the send mail task in your package itself, instead of mentioning it as second step in job.
----------
Ashish
August 1, 2011 at 3:07 am
As I run that package for many different purposes. The send mail task would become unworkable. Looks like putting this in works fine in debug mode, but it doesn't run in sequence when I run it via it's own SQL Agent job. I'm thinking this may be due to the following code only working to screen in debug rather than via the agent job.
Print convert(varchar,getdate(),113)
WAITFOR DELAY '00:00:01'
Is there some other mechanism I could use instead of print convert that would work via the agent job run?
August 1, 2011 at 3:31 am
If you saying that your first step is still running and you able to see the status of that step as as "Running" but still step 2 sending mail, then in advanced tab of the first step's properties, you can define "on success action" and "on failure action"
----------
Ashish
August 1, 2011 at 3:34 am
No I meant, in my package which runs as one step in the agent job, it sends the email before completion of the embedded execute sql agent job. I.e. my usp stored procedure is having no effect.
August 1, 2011 at 5:22 am
Griffster (8/1/2011)
As I run that package for many different purposes. The send mail task would become unworkable. Looks like putting this in works fine in debug mode, but it doesn't run in sequence when I run it via it's own SQL Agent job. I'm thinking this may be due to the following code only working to screen in debug rather than via the agent job.Print convert(varchar,getdate(),113)
WAITFOR DELAY '00:00:01'
Is there some other mechanism I could use instead of print convert that would work via the agent job run?
I would create a stored procedure that does the following:
1. Start up the job.
2. Loops over the while loop that is explained in the other forum post that you found
The Execute SQL Task will then onlyl finish if the job has finished running.
The PRINT statement can be dropped, but I would keep the WAITFOR DELAY.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 1, 2011 at 5:56 am
Koen Verbeeck (8/1/2011)
Griffster (8/1/2011)
As I run that package for many different purposes. The send mail task would become unworkable. Looks like putting this in works fine in debug mode, but it doesn't run in sequence when I run it via it's own SQL Agent job. I'm thinking this may be due to the following code only working to screen in debug rather than via the agent job.Print convert(varchar,getdate(),113)
WAITFOR DELAY '00:00:01'
Is there some other mechanism I could use instead of print convert that would work via the agent job run?
Aside from the print convert statement, this is along the lines of what my package is actually doing. Basically, I have sequence container containing all the tasks of the package.
Step 1 = Run job using a Execute SQL Server Agent Job Task.
Step 2 = Run this stored procedure using an execute sql task by executing the following statement, and assigns the value of ReturnedResult to a variable varJobResult:
declare @retcode int
exec @retcode = master.dbo.usp_wait_while_job_runs 'UpdateRSExecutionLog'
select ReturnedResult = @retcode
Step 3: I then have two precedence constraints. The first is an Expression and Constraint evaluation operation with value = success and constraint = @[User::varJobResult]==0, and the second being a Expression or Constraint with value = Failure and constraint = @[User::varJobResult]<0.
Problem is in BIDS debug, step 1 has to finish before processing goes from step 2 to step 3, but when I put it in as a new job and run that from the server, it runs step 3 before step 1 has finished.
August 1, 2011 at 6:18 am
Griffster (8/1/2011)
Aside from the print convert statement, this is along the lines of what my package is actually doing. Basically, I have sequence container containing all the tasks of the package.Step 1 = Run job using a Execute SQL Server Agent Job Task.
Step 2 = Run this stored procedure using an execute sql task by executing the following statement, and assigns the value of ReturnedResult to a variable varJobResult:
declare @retcode int
exec @retcode = master.dbo.usp_wait_while_job_runs 'UpdateRSExecutionLog'
select ReturnedResult = @retcode
Step 3: I then have two precedence constraints. The first is an Expression and Constraint evaluation operation with value = success and constraint = @[User::varJobResult]==0, and the second being a Expression or Constraint with value = Failure and constraint = @[User::varJobResult]<0.
Problem is in BIDS debug, step 1 has to finish before processing goes from step 2 to step 3, but when I put it in as a new job and run that from the server, it runs step 3 before step 1 has finished.
So you are saying the package has a different behaviour when you run it in BIDS than when you run it on the server?
That is strange. Are you sure you deployed the package correctly?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 1, 2011 at 6:20 am
Koen Verbeeck (8/1/2011)
Griffster (8/1/2011)
Aside from the print convert statement, this is along the lines of what my package is actually doing. Basically, I have sequence container containing all the tasks of the package.Step 1 = Run job using a Execute SQL Server Agent Job Task.
Step 2 = Run this stored procedure using an execute sql task by executing the following statement, and assigns the value of ReturnedResult to a variable varJobResult:
declare @retcode int
exec @retcode = master.dbo.usp_wait_while_job_runs 'UpdateRSExecutionLog'
select ReturnedResult = @retcode
Step 3: I then have two precedence constraints. The first is an Expression and Constraint evaluation operation with value = success and constraint = @[User::varJobResult]==0, and the second being a Expression or Constraint with value = Failure and constraint = @[User::varJobResult]<0.
Problem is in BIDS debug, step 1 has to finish before processing goes from step 2 to step 3, but when I put it in as a new job and run that from the server, it runs step 3 before step 1 has finished.
So you are saying the package has a different behaviour when you run it in BIDS than when you run it on the server?
That is strange. Are you sure you deployed the package correctly?
Yes, in fact I re-deployed just in case.
August 1, 2011 at 6:41 am
Does the package run correctly when you start it manually from the server?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 1, 2011 at 6:43 am
Koen Verbeeck (8/1/2011)
Does the package run correctly when you start it manually from the server?
By running it in BIDS on the server or by running the dtsx?
August 1, 2011 at 7:28 am
Griffster (8/1/2011)
Koen Verbeeck (8/1/2011)
Does the package run correctly when you start it manually from the server?By running it in BIDS on the server or by running the dtsx?
Mmm... and the plot thickens! When I rdp in to the server and right click on the deployed package, then select the particular configuration file and run it that way, it runs as I want it to. Not entirely sure I know what this tells me though!!!
August 2, 2011 at 1:56 am
STOP PRESS...it's working now! May be I hadn't updated the package after all! Thanks for your help.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply