Data Transformation Services

DTS is a feature that has come bundled with the latest version of SQL Server from Microsoft. It is a medium that allows data to come from one source, be manipulated using a light programming language (or predefined script), and then be inserted into a destination. This can be done an infinite number of times traveling across multiple databases with branching, and parallel processing. Rules can be setup for how to handle error events and reporting.

The end result? A spectacular idea (though not the first of its kind) with poor execution. The software appears to be in an early beta stage at best, with a horrible user interface. Most of the elements that are in use are accessible through right-clicking only, and only when the mouse is in a particular location. Whether you are on an object, a line, or white space completely changes what options are accessable – and the lines are one pixel thin. The lack of keyboard accelerator keys to almost everything is frustrating, as one has to exclusively use the mouse, while enter in code. The font rendering in the application makes the code almost unreadable. Features such as undo, syntax highlighting, spell checking, code completion, useful error messages, and full VB support are absent.

Working around all of this, I have authored a package that takes the data from a local database that is the backend for an online form, pull out the relevent data, and insert it into another database the school uses for students. The first step is done, and I am now working with the developer on enforcing constraints at application level, and changing a few of the data types from free-text to a selection box in the form. This will allow for a bullet-proof transformation of data, and a clean end result. I had a small part in this project, but the fruits of it should be seen in the next semester.


Leave a Comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.