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

short.

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

fail.

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

package.

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.