rongjiang


Hello,

I try to create a SSIS package only use SQL server agent job tasks(some in 2000 and some in 2005) in control flow. Basically if the first secheduled job failed the package will stop. I linked the first and the second job with 'on success' precedence constraint. But when I build or run the package it always return execution success and run the second one. And I know for sure the first job is failed through enterprise manager - management - jobs

Could somebody advice what could possibly went wrong

Thanks.

Flora





Re: SSIS SQL Server Agent Job Task

JayH


I assume you are calling sp_start_job to start these scheduled jobs The sp_start_job stored procedure does not wait for the job to execute, only starts it and returns. So it is always successful even if the job itself fails. To get the return status, you would need to use a combination of sp_start_job, a loop that repeatedly calls sp_help_job and a delay until the job finishes, and then sp_help_jobhistory to get the result. That's a lot of work. Since SQL Agent jobs provide this type of control flow, you may want to consider creating a new job that does the steps you need or convert these steps into SSIS tasks.





Re: SSIS SQL Server Agent Job Task

Rafael Salas

I think he is using Execute SQL Server agent Job Task http://msdn2.microsoft.com/en-us/library/ms137858.aspx

But perhaps that task has that behavior; just send the request and come back reporting it as a succesfull.







Re: SSIS SQL Server Agent Job Task

rongjiangsame

yes, I am using SQL server agent job task. For testing purpose, I have created two dummy agent job. The first job is to insert into temptable some value with syntax error it will surely fail. The second job is to insert word 'success' to the temptable. These two job is linked by 'on success' flow in one package. The problem is, this package always run successfully and the second job always run. Is this a known bug with SSIS






Re: SSIS SQL Server Agent Job Task

JayH

rongjiangsame wrote:

yes, I am using SQL server agent job task. For testing purpose, I have created two dummy agent job. The first job is to insert into temptable some value with syntax error it will surely fail. The second job is to insert word 'success' to the temptable. These two job is linked by 'on success' flow in one package. The problem is, this package always run successfully and the second job always run. Is this a known bug with SSIS



No it isn't a bug. The documentation link Rafael provided above states that this task just calls sp_start_job. As I previously described, sp_start_job only starts the job. It doesn't wait for execution, and therefore has no idea whether the job failed or not. You would need to check for the execution status and result on your own.




Re: SSIS SQL Server Agent Job Task

rongjiangsame

Thanks Jay and Rafael. I got your point. (finallySmile) Now back to square one, here is my actual problem -

Current status - time dependency between jobs

Job one on 2005 - doing some formating through stored procedure, scheduled on 2:30 AM every day
Job two on 2005 - automatically created jobs for snapshot replication scheduled on 3:00 AM every day

Job three on 2000 - doing some complicated calculation after replication scheduled on 3:30 AM every day

Job one failed frequently due to the source file not ready etc. Without the success of Job one, Job two and three execution won't break the database but data is useless. So I was thinking to combine these jobs together and keep retry on job one until success then run the other two. (It helps to rerun job one since the source file usually get ready during the night if not ready by 2:30 AM). Because jobs are scatted on SQL 2000 and 2005 I was thinking to use SSIS package, and also because Job two is automatically created by snapshot replication, I have to use SQL server agent job task...

now besides checking the execution status of the job in SSIS ( I am new to SSIS ), did I miss any easier solution for this problem Thanks for your advice.

Rong