SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

DTS Standards

By Jonathan Stokes,

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.

Total article views: 8600 | Views in the last 30 days: 1
Related Articles

Database diagrams

Database diagrams


Relational Diagrams

Relational Diagrams


Step by Step process to follow before and after applying patches on sqlserver

Step by Step process to follow before and after applying patches on sqlserver


Database Diagrams

DB Diagrams in SQL Server 2000/2005


ERD Diagram

ERD Diagram

sql server 7