Find all Job Relationships with SSIS Config files

,

If you've ever had the pleasure of editing a job that calls other jobs, that calls even more jobs you understand what a headache that can turn into.

This script walks through the SYSJOBS and SYSJOB STEPS tables and builds the parent/child job hierarchy. It then analyzes each job step, looking for any SSIS job steps and subsequent config files used, if any. (This part can be easily changed to look for anything)

This was borne out of a need to change a rather large job hierarchy after one of our ETL servers failed and everything needed to be redirected to a new box.

Please feel free to email me if you have any questions/recommendations

raysot @ comcast DOT net

/*
Build Job Parent/Child Hierarchy using the Parent Job Name
Author: Ray Sotkiewicz, March 2011
Email: raysot @ comcast DOT Net
Use freely. 
*/
--------------------------------------------------------------

-- Find all DTSCONFIG Files associated with a Job Workflow

--------------------------------------------------------------

Declare @JobName SYSName
Set @JobName = '_BackupWorkflow: Daily Userdb'



------------------------------------------
-- Create work Table
------------------------------------------
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#JobWork') IS NOT NULL 
BEGIN
 Drop Table #JobWork 
    Create table #JobWork
    (
         Parent_Job_Name SYSNAME
        ,Job_Name SYSNAME
        ,Step_ID INT
        ,Step_Name SYSNAME
        ,Child_Job_Name SYSNAME
        ,Processed BIT
    )    
END

------------------------------------------
-- Insert Top-Level (Parent) Job Name
------------------------------------------
--INSERT #JobWork( Job_Name, Step_ID, Step_Name, Child_Job_Name, Processed) VALUES (@JobNAme, 0, '', '', 1)


------------------------------------------
-- Get Child Jobs: (Children)
------------------------------------------
INSERT #JobWork
Select     @JobName AS 'Parent_Job_Name'
        ,sj.[name] AS 'Job_Name'
        ,sjs.Step_ID
        ,sjs.step_name 
        ,substring(Command, (29), (Len(Command)-29 ) ) AS 'Child_Job_Name'
        ,0 as 'Processed'        
from msdb..sysjobs sj
INNER JOIN msdb..sysjobsteps sjs on sj.job_id = sjs.job_ID 
Where sj.name = @JobName
AND sjs.command LIKE 'EXEC msdb.dbo.sp_start_job%'
AND sjs.subsystem = 'TSQL' 

---------------------------------
-- Interim Validation
---------------------------------
--Select * from #JobWork


---------------------------------
-- Loop to find more child jobs
---------------------------------
StartLoop:

DECLARE @_JobName SYSNAME

DECLARE JOB_cursor CURSOR FOR 
Select Child_Job_Name from #JobWork Where Len(Child_Job_Name) > 0 and Processed = 0 


OPEN JOB_cursor 
FETCH NEXT FROM JOB_cursor INTO @_JOBName 


WHILE @@FETCH_STATUS = 0 
BEGIN 

 INSERT #JobWork

        Select     @_JOBName AS 'Parent_Job_Name'

         ,sj.[name] AS 'Job_Name'

                ,sjs.Step_ID

                ,sjs.step_name 

                ,substring(Command, (29), (Len(Command)-29 ) ) AS 'Child_Job_Name'

                ,0 as 'Processed'        

        from msdb..sysjobs sj

        INNER JOIN msdb..sysjobsteps sjs on sj.job_id = sjs.job_ID 

        Where sj.name = @_JobName

        AND sjs.command LIKE 'EXEC msdb.dbo.sp_start_job%'

        AND sjs.subsystem = 'TSQL' 

        

        Update #JobWork Set Processed = 1 where Child_Job_Name = @_JobName 



 FETCH NEXT FROM JOB_cursor INTO @_JobName 

END 



CLOSE JOB_cursor 

DEALLOCATE JOB_cursor



----------------------------------------

-- Trap for any new Child Jobs

----------------------------------------

If (Select COUNT(*) from #JobWork where Processed = 0) > 0

BEGIN

    Goto StartLoop

END



---------------------------------

-- Interim Validation

---------------------------------

Select Parent_Job_Name, Child_Job_Name from #JobWork



-------------------------------------------------------

-- Return Parent Job SSIS Config Files

-------------------------------------------------------

    Select    -- sj.job_id

             Left(sj.[name], 50) AS 'Job_Name'

            ,sjs.Step_ID

            ,Left(sjs.step_name, 50) AS 'Step_Name' 

            ,Left(substring(sjs.Command, (CHARINDEX('/CONFIGFILE', sjs.Command) + 13), (CHARINDEX('dtsConfig"', sjs.Command) + 9) -(CHARINDEX('/CONFIGFILE', sjs.Command) + 13) ), 256) AS 'Config_File'

            ,sjs.Command

    from msdb..sysjobs sj

    INNER JOIN msdb..sysjobsteps sjs ON sj.job_id = sjs.job_id

    Where sj.name = @JobName

    AND sjs.command LIKE '%/CONFIGFILE%'

    AND sjs.subsystem = 'SSIS' 





-------------------------------------------------------

-- Get All SSIS Job Steps with Config Files 

-------------------------------------------------------

DECLARE @_StepName SYSNAME



DECLARE Step_cursor CURSOR FOR 

Select Child_Job_Name from #JobWork Where Len(Child_Job_Name) > 0 



OPEN Step_cursor 

FETCH NEXT FROM Step_cursor INTO @_StepName 



WHILE @@FETCH_STATUS = 0 

BEGIN 

    Select    -- sj.job_id

             Left(sj.[name], 50) AS 'Job_Name'

            ,sjs.Step_ID

            ,Left(sjs.step_name, 50) AS 'Step_Name' 

            ,Left(substring(sjs.Command, (CHARINDEX('/CONFIGFILE', sjs.Command) + 13), (CHARINDEX('dtsConfig"', sjs.Command) + 9) -(CHARINDEX('/CONFIGFILE', sjs.Command) + 13) ), 256) AS 'Config_File'

            ,sjs.Command

    from msdb..sysjobs sj

    INNER JOIN msdb..sysjobsteps sjs ON sj.job_id = sjs.job_id

    Where sj.name = @_StepName

    AND sjs.command LIKE '%/CONFIGFILE%'

    AND sjs.subsystem = 'SSIS' 

         



 FETCH NEXT FROM Step_cursor INTO @_StepName 

END 



CLOSE STEP_cursor 

DEALLOCATE STEP_cursor

Rate

4 (3)

Share

Share

Rate

4 (3)