Debugging DTS Packages
You haven’t really experienced sheer hell until you have to debug a DTS package. SQL Server development is something completely different from the traditional 4GL languages like VB and C#, you have to think in rows and columns and joins. A lot of 4GL programmers are forced or simply want to get into writing T/SQL Stored Procedures and realize that it is a whole new ballgame. But one that is easy to master if you put the time in. So a lot of beginning SQL Server programmers who came over from the 4GL side ask me often at conferences, “How do I debug a DTS package?” The answer is “You don’t.” (Oh we are all so spoiled by setting a breakpoint in Visual Studio.)
Microsoft has publicly announced some of the new DTS features of Yukon at TechEd in Barcelona and will announce more at the PDC in LA later this month, so I won’t go there and besides Yukon will ship sometime over the rainbow. So let’s start thinking about SQL Server 2000.
First off, proper design of your package will only make debugging much easier. Use only Stored Procedures (with or without parameters) and if you have to use SQL dynamically utilize Views. This is because the more dependencies on “real” database objects, the easier it will be to track down your problem. Avoid ActiveX Scripts as much as you can-consider an Extended Stored Procedure that calls a DLL wrote yourself (or call the DLL with a CreateObject in your script if you must).
For the actual debugging itself my advice to you is to take everything in steps. You can run each DTS package’s step individually just by right clicking on it in the designed and selecting “Execute Step” from the pop-up menu. That is the first part. Then you can deconstruct the step manually and run those pieces in Query Analyzer. (In theory you can debug your stored procedure in Visual Studio too.) From there it gets easier, small bits and pieces of your step may or may not be working, so start looking at your select statements in QA. Before you know it, you will be in DTS debugging heck instead of hell.
