DTS- Debugging Tips

Question

In a legacy project that I'm on, we have several processing that are preformed via DTS. DTS is not something I worked with a lot back in its hey day.... I was in college.

More specificity, these process are in ActiveX code blocks -- which is basically VBScript for database. It is really hard to debug.

Anyway, I'm wondering if any past or present experienced DTS professionals can offer tips on how to deal with debugging, troubleshooting or otherwise dealing with DTS package development.

This this question is marked as community wiki, I'm hoping to have general and targeted ideas and methods for all types of DTS package implementations.

Answer 1

In the scripting portion, I have used the MsgBox to display "I got here" or "xfer worked" or whatever you want to indicate something happened which is not so obvious at run time.

You can also use conditional statements to branch off to an 'End' if you are testing a particular portion of the flow.

If you are stuck working with DTS, but are also running a SQL Server 2005 instance, you might see if you cant upgrade the DTS packages to DTSX (SQL Server Integration Services) and re-do them there. I know this isn't a 'trick' but you work in the VS2005 IDE, can write in .NET and also you can set break-points and will make life in 'DTS' world much easier.

There are also some articles here:

http://www.databasejournal.com/article.php/1503191

Scroll down and you will see the "SQL Server 2000 DTS" articles.

Answer 2

I had a complex DTS package that imported some data, ran some batch scripts, made a CSV file and uploaded the resulting output via FTP. Sometimes the FTP process would fail.

I created a "DTS LOG" table and after each step I simply added a SQL insert task and wrote a time stamp and function name into the table. I made a view to show me any process that did not complete.

While this may not be as granular as you need, but at least you'll know where the problem is in the execution.