none
SQL Job through SQL Scripts RRS feed

  • Question

  • Hi,

    I have to create a SQL Job where I have to execute SSIS Packages. All these SQL Packages are independent and can execute in any order.

    So I have prepared a script like below

    USE [msdb]
    GO
    
    DECLARE @jobId binary(16)
    
    SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = N'xyz')
    IF (@jobId IS NOT NULL)
    BEGIN
    DECLARE @sdf TABLE(SSISNames nVARCHAR(100), ID INT IDENTITY(1,1));
    INSERT INTO @BamTables
    VALUES('dsf'),('dsd'),('sds'),
    ('XYZ'),('sdfsd')
    ;
    
    DECLARE @cnt INT = 1;
    while(@cnt<= (select count(*) from @sdf))
    begin
    Declare @SSISPackageName nvarchar(100)=(select SSISNames from @sdf where ID=@cnt);
    Declare @SSISPackageCmd nvarchar(200)=N'/SQL "\"\'+@SSISPackageName+'\'+'"" /SERVER '+'(local)'+' /CHECKPOINTING OFF /REPORTING E';
    EXEC  msdb.dbo.sp_add_jobstep @job_name=N'xyz', @step_name=@SSISPackageName,  
    		@cmdexec_success_code=0, 
    		@on_success_action=3, 
    		@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'SSIS', 
    		@command=@SSISPackageCmd, 
    		@database_name=N'master', 
    		@flags=0
    		set @cnt=@cnt+1
    		End
    		End
    
    		GO

     Some of my other Solutions can also Use this script and add SSIS Packages into this SQL Job.

    The Problem now is when I execute this job all the steps gets executed but at the ned it fails it tried to execute a non exsistemt step since I gave the onsuccessaction=3 all the steps says go to next step.is it possible can we stop the job execution once the Steps are completed

    Thanks,

    Sujith.


    Friday, August 12, 2016 4:02 PM

Answers