If a person familiar with DTS were to go
out into the world and audit a sample of DTS packages present in various
organisations, I am guessing that half of them would not make any sense other
than to the person who created them. Moreover, I doubt many of these will have
any consistency and fluctuate in design from package to package, even within
the same organisation. The fact that DTS packages are fundamental building
blocks to many processes imply that they should be controlled as tightly as any
other tool and be subject to the same level of QA.
The purpose of this article is to take a
look at what I class as good practice and the attempts I employ to achieve a
homogeneous structure. This is, however, not intended to be a definitive guide.
The quintessential element here is the need to address these issues and achieve
some level of uniformity.
Specifically broken down into the following areas in no particular order of precedence:
a) Layout and presentation
b) Exposing hidden objects
c) Error handling
d) Naming Conventions
Anyone familiar with DTS will have seen the classic package created with the minimum amount of effort, in the minimum amount of time. I’m talking about this sample below:
Although this example will work if the
transform data task is set up correctly, the design is no help to anyone. By
allowing DTS to apply the default naming conventions, all the packages will
look the same and those with multiple connections will become confusing and
prone to error. In this example, all we can tell is that an excel file is being
imported into a table in a database. To find out more information, we would
have to open both connections and then look at the transformation task.
A better representation of the diagram
This diagram is much easier to interpret.
Note that the prefix of ‘src’ is added for source and ‘dst’ for destination. I
apply these naming conventions to establish some uniformity. I do not prefix
with the object type nor explain the transform data task as the pictures
themselves are a giveaway – After all, I’m writing a package here, not an
essay. The only downside here is that there is a limit to the amount of text
you can add before it adds those annoying dots at the end. Hence, keep names
The next area to deal with is the error
handling. It is easy enough to apply logging to a server or a file, but to
receive an immediate success/failure message and know exactly how far the
package has got, it is a good idea to put some separate failure steps in.
We want to import the source spreadsheet
before we DTS it in, just in case a user has it open and causes the package to
Success should flow to the right and
failure to the left, making it easier to follow the paths. This is not always
possible for a package with high volumes of connections, but sticking as close
to this method as humanly possible is recommended.
Adding another connection to output to a
spreadsheet and then applying an activex script to format that spreadsheet has
the effect of making the package harder to follow.
I recommend at this point to start adding
some text to the diagram. However, placing help text in and around the objects
can also decrease readability. The best place for the text is at the top of the
I break the steps into blocks that make the
logic easy to follow. If someone who knew nothing about DTS viewed this
package, they would have a good idea as to what the package is setting out to
accomplish. It is far easier to give a diagram to a user than talk them through
a process sans visual aids.
It is worth remembering that not all
components of the package are displayed in the diagram. I’m talking
specifically about global variables. It’s quite easy to miss these when trying
to debug packages authored by others.
Adding a global variable to supply a
filename is noticeable through the Dynamic Properties Task, but again it is
needs to be opened and viewed to get an idea of what’s going on behind the
scenes. Much easier if it is part of the diagram.
This is the finished product. I am sure you
will agree that this package is clear, concise and can be familiarised faster
if revisited in months or years time.
As stated in the opening to this article, I
would not promote this as being the ‘de facto’ way of creating packages. The paramount
objective in creating packages is to keep them easy to follow, which in turn
will make them easier to maintain. Like any pieces of code, you need standards
and naming conventions and DTS should be no different.