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,
- 'Copy File' tasks
- '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 @EventID INT
DECLARE @TaskIndex INT
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',
CASE
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',
CASE
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],
CASE
WHEN ret_code = 0 THEN 'Success'
WHEN result = 1 THEN 'Success'
ELSE 'Failure'
END AS [Result],
CASE
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
UNION
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)
END
FROM task
WHERE event_id = @EventID
AND task_seq = @TaskIndex
AND cond_seq = @ConditionIndex),
'Failure',
''
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,
Ian./