April 20, 2005 at 6:53 am
Does anyone hae a 'Best Practice' on how to document a DTS Package? I would like to, for review purposes with a group, have a hard copy of the package, and all the steps within it. Is there a toll that will do this for me, so I don't have to go into each step and manually copy the contents and print it?
Thanks,
Clif
April 20, 2005 at 7:47 am
I don't know if there are any tools out there or not, but I find the best way is to implement a 'use case'. This is a UML concept that is not only good for documenting, but also for designing dts packages.
My use case includes all steps in the package and what to do on failure in each step. It makes it a lot easier to follow the logic.
I back this up with a screenshot of the dts package. I make sure that I include a lot of visual details in my packages. I divide the dts screen into squares and put comments in each box with a task in it etc. If I use any global variables or specify any settings in an ini files, I include this as text within the package.
Unfortunatley, this is all manual work. However, it will make you look at your packages and probably spot some errors in logic along the way.
April 21, 2005 at 7:49 am
I have written a VB.Net application that will read in selected DTS packages from the SQL Repository and extract all important information: i.e. Connection objects, Tasks, Steps and any Active-X scripts into a HTML window. If your Active-x script is written in VBScript, it will provide line numbers and color coding as Visual Studio does for Strings, Keyword and Constants... etc...
If you would like to try it out, send me an email at mgercevich_nospam_@gmail.com (remove '_nospam_')
Here is an example of the output from a simple package:
-Michael T. Gercevich
=======================================================================
DTS Package Export
Export Date : 4/21/2005 9:28:31 AM
Prepared By : DTS Package Exporter, Michael T. Gercevich
=======================================================================
Package Name : BRS Daily Import
Server Name : (local)
Description : Daily BRS/DRC Order Processing Routines - Parent Package
Package ID : {1A7BE362-3F69-4661-AAFB-BFB3F08F30F9}
Version ID : {4A4EC72B-D45B-4F69-996F-D1064EA4A11B}
Create Date : 1/15/2005 11:01:10 AM
Owner : FFHQ\xxxxxx
=======================================================================
=======================================================================
=== Connection Details:
=======================================================================
Connection 1: Microsoft OLE DB Provider for SQL Server
Description:
Provider : SQLOLEDB
Catalog : XYZ
Data Source: (local)
Trusted Con: True
=======================================================================
=== Step OverView:
=======================================================================
Step 1: DTSStep_DTSActiveScriptTask_CodeLibrary
Description: CodeLibrary
Enabled : False
HasWorkflow: False
Task Name : DTSTask_DTSActiveScriptTask_CodeLibrary
Step Precedence:
[No Predecesors]
Step 2: DTSStep_DTSActiveScriptTask_GlobalInitialize
Description: GlobalInitialize
Enabled : True
HasWorkflow: False
Task Name : DTSTask_DTSActiveScriptTask_GlobalInitialize
Step Precedence:
[No Predecesors]
Step 3: DTSStep_DTSActiveScriptTask_DRCImportPackage
Description: DRCImportPackage
Enabled : True
HasWorkflow: False
Task Name : DTSTask_DTSActiveScriptTask_DRCImportPackage
Step Precedence:
Predecessor: DTSStep_DTSActiveScriptTask_GlobalInitialize
Basis : On Success
Step : DTSStep_DTSActiveScriptTask_DRCImportPackage
Step 4: DTSStep_DTSActiveScriptTask_ImportPackage
Description: ImportPackage
Enabled : True
HasWorkflow: False
Task Name : DTSTask_DTSActiveScriptTask_ImportPackage
Step Precedence:
Predecessor: DTSStep_DTSActiveScriptTask_DRCImportPackage
Basis : On Success
Step : DTSStep_DTSActiveScriptTask_ImportPackage
Step 5: DTSStep_DTSActiveScriptTask_MovePendingErrorPackage
Description: MovePendingErrorPackage
Enabled : True
HasWorkflow: False
Task Name : DTSTask_DTSActiveScriptTask_MovePendingErrorPackage
Step Precedence:
Predecessor: DTSStep_DTSActiveScriptTask_ImportPackage
Basis : On Success
Step : DTSStep_DTSActiveScriptTask_MovePendingErrorPackage
=======================================================================
=== Step Details:
=======================================================================
=======================================================================
StepName : DTSStep_DTSActiveScriptTask_CodeLibrary
Description : CodeLibrary
WorkFlow Script:
[No WorkFlow Script]
TaskName : DTSTask_DTSActiveScriptTask_CodeLibrary
Description : CodeLibrary
Task ID : DTSActiveScriptTask
Active-X Task Script: **************************
00001 '**********************************************************************
00002 ' Visual Basic ActiveX Script
00003 '************************************************************************
00004
00005 Function Main()
00006 Main = DTSTaskExecResult_Success
00007 End Function
00008
00009
=======================================================================
StepName : DTSStep_DTSActiveScriptTask_GlobalInitialize
Description : GlobalInitialize
WorkFlow Script:
[No WorkFlow Script]
TaskName : DTSTask_DTSActiveScriptTask_GlobalInitialize
Description : GlobalInitialize
Task ID : DTSActiveScriptTask
Active-X Task Script: **************************
00001 ' GlobalInitialize - Task Script
00002 ' To be included as first step in all DTS Packages
00003 '
00004 ' Description:
00005 ' Load Global Variables from SQL Table [XYZ].[DTSGlobalVariables]
00006 '
00007 ' Author : Mike Gercevich
00008 ' Date : 03/19/2004
00009 ' Version: Initial Release
00010 ' Revisions:
00011 '
00012 '************************************************************************
00013 OPTION EXPLICIT
00014 Const adCmdStoredProc = &H0004
00015
00016 Function Main()
00017
00018 DTSGlobalVariables("gvCodeLibraryObject").Value = ""
00019 DTSGlobalVariables("gvCodeLibraryFile").Value = "not defined"
00020
00021 '*** Search for CodeLibrary Step and Disable Step
00022 '*** Create the DTSGlobalVariable Pointer to CodeLibrary CustomTasks.
00023 '*** Clear the ActiveXScript Contents.
00024 '***
00025 Dim glb_objPackage
00026 Dim glb_objCodeLibStep
00027 Dim glb_objCodeLibTask
00028 Dim glb_objCodeLibScript
00029 Set glb_objPackage = DTSGlobalVariables.Parent
00030 If IsObject( glb_objPackage.Steps( "DTSStep_DTSActiveScriptTask_CodeLibrary" ) ) Then
00031 '*** Disable the CodeLibrary Step
00032 glb_objPackage.Steps( "DTSStep_DTSActiveScriptTask_CodeLibrary" ).DisableStep = -1
00033 If IsObject( glb_objPackage.Tasks( "DTSTask_DTSActiveScriptTask_CodeLibrary" ) ) Then
00034
[>>>>> CUT >>>>>>>]
April 21, 2005 at 10:22 am
Like with any documentation, it's only as good as it is updated. Knowing this, I don't require separate documentation, I have the programmers who write them, document it through comments. Things I make them do are:
1) Notation on who wrote it, when, and why.
2) Brief description what it does.
3) If the package is fairly complicated I have them insert comments throughout each step so that it's fairly easy to figure out the flow.
4) I setup the logging on it and make sure that the connections point to the correct SQL server, any outside files it is importing or exporting are connectec via a UNC path.
Is it a perfect science? No, but usually it's good enough documentation that allows a user -- at a glance -- to see what the purpose of the package. Usually that is the most important part anyway -- at least in our company it is.
As a side note, a developer in our dept. wrote a .NET package that allows us to go in and do searches through the DTS's. This has proven to be a very helpful tool. I'm so surprised that something like this hasn't been written by a 3rd party.
April 22, 2005 at 3:07 am
I am about to document a series of DTS scripts generated from a Cognos consultant. I am principally a .net developer and would appreciate any links to code on the net that would help me create my own DTS documentation application.
Any code snippets you have would also be very much appreciated.
PLease either post the links or send them to sore_ronREMOVE@hotmail.com.
(delete REMOVE )
Cheers,
sore_ron
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply