Deployment Solution

 View Only

Documenting DS6.x Jobs 

Nov 21, 2011 05:14 AM

In preparation for our DS6.x to 7.x upgrade path, I'm looking at how to document DS6.x jobs. Ideally, I'd like to be able to point a SQL Script at a DS6.x Job and have the entire job nicely documented as an HTML report.

Looking at our job trees, I need to document first and foremost,

  1. 'Copy File' tasks
  2. 'Run Script' tasks

These make up the bulk of our DS6.x jobs. Our imaging tasks are typically done with automation 'Run Scripts'.  After a couple of hours I've been able to come up with some SQL which documents a Copy Task as two tables. The first table gives the job details (copy src, dst etc) and the second documents the return code behaviour.


Chucking this up to connect so I don't lose it. ;-)
-- T-SQL to export a Copy task (task type 12) as readble tables 
-- The ultimate aim idea is to be able to export jobs in a nice format so that  
-- DS6.x Jobs can be fully documented.  
-- Uses: DS7 upgrades, and perhaps automating much of image build and software delivery documentation
DECLARE @EventName VARCHAR(100) 
DECLARE @ConditionIndex INT 

SET @EventName='Copy_Sample' 
SET @EventID=(SELECT TOP 1 event_id 
              FROM   event 
              WHERE  name LIKE 'Copy_Sample') 
-- First let's just look at the first task in the first condition 
SET @TaskIndex=0 
SET @ConditionIndex=0 

--handler=0  means on success stop 
--handler=1 means on success continue 
--handler=xxx means run job xxx 
SELECT local_file  'Source', 
       remote_file 'Destination', 
       CASE download_directory 
         WHEN 0 THEN 'File' 
         ELSE 'Directory' 
       END         'Copy Type', 
         WHEN download_directory = 0 THEN 'N/A' 
         WHEN recurse_dir = 0 THEN 'No' 
         ELSE 'Yes' 
       END         'Copy Subfolders', 
       CASE aclient_to_download 
         WHEN 0 THEN 'Use DS' 
         ELSE 'Direct' 
       END         'Copy Method', 
         WHEN username IS NULL THEN 'N/A' 
         ELSE username 
       END         'Copy Credential', 
       CASE allow_automation 
         WHEN 0 THEN 'No' 
         ELSE 'Yes' 
       END         'Allow Automation' 
FROM   copyfile_task 
WHERE  event_id = @EventID 
       AND task_seq = @TaskIndex 
       AND cond_seq = @ConditionIndex 

SELECT CAST(ret_code AS VARCHAR(100)) AS [Code], 
       CASE handler 
         WHEN 0 THEN 'Stop' 
         WHEN 1 THEN 'Continue' 
         ELSE 'Execute: ' + (SELECT name 
                             FROM   event 
                             WHERE  event_id = handler) 
       END                            AS [Response], 
         WHEN ret_code = 0 THEN 'Success' 
         WHEN result = 1 THEN 'Success' 
         ELSE 'Failure' 
       END                            AS [Result], 
         WHEN ( Len(status) > 0 ) THEN status 
         ELSE '' 
       END                            AS [Return Code Text (Status)] 
FROM   task_return_handlers 
WHERE  event_id = @EventID 
       AND task_seq = @TaskIndex 
       AND cond_seq = @ConditionIndex 
SELECT 'Other', 
       (SELECT CASE on_fail 
                 WHEN 0 THEN 'Stop' 
                 WHEN 1 THEN 'Continue' 
                 ELSE 'Execute: ' + (SELECT name 
                                     FROM   event 
                                     WHERE  event_id = on_fail) 
        FROM   task 
        WHERE  event_id = @EventID 
               AND task_seq = @TaskIndex 
               AND cond_seq = @ConditionIndex), 
I'll probably convert this into a function to export a copy task as HTML. I'll keep chucking progress into this blog posting, and throw out any working 'task documenters' as articles.
I'll see what I can do in future updates to remove the extra line breaks that appear in the SQL above...
Kind Regards,

0 Favorited
0 Files

Tags and Keywords


Nov 21, 2011 10:50 AM

Now.... have the basis of some SQL which will iterate through the tasks and conditions which make up a job. Abstracted out the SQL which documents copy tasks into an SP.

So, now just need to create the SPs for other task types to insert into this wrapper....


 DECLARE @EventName varchar(100)
DECLARE @EventID int
DECLARE @TaskIndex int
DECLARE @ConditionIndex int
DECLARE @TaskType int

DECLARE @MaxTask int
DECLARE @MaxCondition int

SET @EventName='Copy_Sample'
SET @EventID=(select top 1 event_id from event where name like 'Copy_Sample')

SET @MaxCondition =(select max(cond_seq) from task where event_id=@EventID)

-- For each condition sequence we have a number of tasks.
-- so we need to look at each condition, and find the number of tasks within each one

DECLARE @i int -- iterates through tasks
DECLARE @j int -- iterates through conditions

SET @j=0 

WHILE @j<=@MaxCondition
  --find the number of tasks for this specific condition
  SET @i=0 
  SET @MaxTask =(select max(task_seq) from task where event_id=@EventID and cond_seq=@j)
  WHILE @i<=@MaxTask
	SET @Tasktype=(SELECT task_type from task where event_id=@EventID and cond_seq=@j and task_seq=@i)
    SELECT 'Job: ' + @EventName + '  Condition: ' + (Select name from event_condition where event_id=@EventID and cond_seq=@j) + '  Task: ' + cast((@i+1) as varchar) + ' of ' + cast((@maxtask+1) as varchar ) 
    IF @TaskType=12 EXEC [Custom_Doc_CopyTask] @EventID,@i,@j
    SET @i=@i+1
SET @j=@j+1

Related Entries and Links

No Related Resource entered.