SQL Sever Agent Job Step !! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SQL Sever Agent Job Step !!

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2008-03-02 : 08:08:19

Hi pals,

I have stored procedure which dynamically creates a job with 3 job steps which will eventually runs 3 SSIS packages in a sequence.
But i dont know why it is skipping the 2nd step and executing the 3rd step.
I can clearly observe it in the logs.

And if comment the 3rd step and re-run the job, now it is executing the 1st and 2nd step in a sequence.
I can see the log for 2nd package also.
Again i uncommented the code for calling the 3rd step which loads data into Oracle tables.
This time again it is skipping the 2nd step.
I dont know the reason why it is happening so.
It is really frustrating me a lot.
IS there any precedence/ priority given while loading data into Oracle database?

Job steps
First step loads data from staging database to ODS
Second step loads data from ODS to DataMart
Third step loads data from DataMart to Oracle Tables

Can anyone please help me out.

I have tried all options.

Here is my stored procedure.
Is anything wrong in the below stored procedure.

CREATE PROCEDURE [dbo].[spExecDTSPackage]
as
declare @jid uniqueidentifier
declare @cmd1 varchar(4000)
declare @cmd2 varchar(4000)
declare @cmd3 varchar(4000)

SET @cmd1 = '"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /F "D:\Package1.dtsx" '

SET @cmd2 = '"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /F "D:\Package2.dtsx" '

SET @cmd3 = '"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /F "D:\Package3.dtsx" '

declare @jname varchar(128)
set @jname = cast(newid() as char(36))

exec msdb.dbo.sp_add_job
@job_name = @jname,
@enabled = 1,
@delete_level = 1,
@job_id = @jid OUTPUT

exec msdb.dbo.sp_add_jobserver
@job_id = @jid,
@server_name = '(local)'

exec msdb.dbo.sp_add_jobstep
@job_id = @jid,
@step_name = N'step1',
@step_id = 1,
@on_success_action=4,
@on_success_step_id=2,
@subsystem = N'CMDEXEC',
@proxy_name = N'Proxyname',
@command = @cmd1,
@on_fail_action = 2 -- quit with failure

exec msdb.dbo.sp_add_jobstep
@job_id = @jid,
@step_name = N'step2',
@step_id = 2,
@on_success_action=4,
@on_success_step_id=3,
@subsystem = N'CMDEXEC',
@proxy_name = N'Proxyname',
@command = @cmd2

exec msdb.dbo.sp_add_jobstep
@job_id = @jid,
@step_name = N'step3',
@step_id = 3,
@on_success_action=1,
@on_success_step_id=0,
@subsystem = N'CMDEXEC',
@proxy_name = N'Proxyname',
@command = @cmd3

-- Start job
exec msdb.dbo.sp_start_job
@job_id = @jid,
@step_name = N'step1'

I have also tried out with an simple example , a job with 3 steps which basically inserts 3 recs into a table.

Here is the code and this one is executing fine.
I dont why the above code is not Functioning properly.

Any thoughts?

Thanks!

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'2D4474C9-2B4F-45C2-8640-EB8DE30A5276',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=1,
@description=N'No description available.',
@category_name=N'SRM',
@owner_login_name=N'sa',
@job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step_1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=4,
@on_success_step_id=2,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0,
@subsystem=N'TSQL',
@command=N'use ods
go
insert into test select 1,''ram''
go',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step_2',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=4,
@on_success_step_id=3,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0,
@subsystem=N'TSQL',
@command=N'use ods
go
insert into test select 2, ''ganesh''
go',
@database_name=N'master',
@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId,
@step_name=N'Step_3',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0,
@subsystem=N'TSQL',
@command=N'use ods
go
insert into test select 3,''Vinod''
go',
@database_name=N'master',
@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId,
@start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Any suggestions please let me know.

Thanks in advance!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-02 : 09:51:01

If step 2 relies on step 1 being successful, it will possibly run before step 1's package is completed. Likewise for step 3

Step 2 would execute the procedure,which then executes the package. If the package starts, the step will immediately return successful whether the job is complete or not but that doesn't mean it will wait for it to finish. I have seen on other servers that a server agent job would report success, although the DTS package it executed failed. I have also seen where the job reports success simply because the cmnd line executed the DTS job to begin--it would then go on to step 2 regardless of success of DTS package.

I would prefer to have the last step of the 1st package be used to kick of step 2. that way you know it completed or finished. Same for step 2 to 3.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources