SQL Server Data Transformation Services (DTS) Best Practices

Microsoft released Data Transformation Services (DTS) along with SQL Server 7.0. A very powerful, easy to use, graphical tool for importing, exporting and transforming data, which is only available as a costly add-on in other RDBMS products like Oracle. Prior to SQL Server 7.0, all we had was BCP, which is powerful indeed, but not as friendly and functionally rich as DTS. DTS was made robust and dramatically improved with the release of SQL Server 2000, as more and more DBAs and developers started using DTS for building various data loading and transformation solutions. It wouldn't be an exaggeration to say that DTS is one of the most popular ETL (Extract, Transform and Load) tools currently in use. Not bad for a tool that's been around for just about four years.

I haven't had a chance to work a great deal with SQL Server 7.0 DTS. That's one of the reasons why I haven't published a lot of articles on DTS. But I've been using SQL Server 2000 DTS extensively for the last year and half, for implementing various data import/export, transformation solutions. It's during this period, I stopped using the import/export wizard and started using DTS Designer to design my packages. Seriously, designing packages in DTS Designer is much better than using the Wizard, as the Designer gives you complete control over the functionality of the package. But the DTS Designer will take some getting used to though. It took me a while to get used to designing packages and during this period I learned a lot of tips and tricks and established some best practices within my organization, which I am going to share in this article.

In this article, I will list some of the DTS best practices that I follow, along with some tips and tricks. All this information applies to SQL Server 2000 and SQL Server 7.0 (with some exceptions). The following points are in no particular order. At the end of this article, I provided links to DTS books, articles and websites

The article continues at http://vyaskn.tripod.com/sql_server_dts_best_practices.htm