DTS Tips and Tricks
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Data Transformation Services (DTS) have been around since SQL Server version 7.0. There have been many fine reference books and articles written about this tool. However, some of the tips and tricks do not come to the surface until you actually roll up your sleeves and make a heavy use of DTS and its Object Model. Indeed, although the theoretical knowledge coming from the reference books is great, you never find out how to optimize your Packages' performance until you actually build and test a few of them.
In this article, I want to share some of the DTS knowledge I acquired while working on postal processing software. I give you some advice for avoiding some slow Tasks as well as which Tasks perform optimally. In addition, I show you the code that takes advantage of the DTS Object Model, and give you some tips on how to make your code more efficient.
Project
The postal software imported data from miscellaneous sources—such as Excel spreadsheets, Foxpro databases, and others—into a dBase file to be processed by some third-party mail verification and sorting software. The dBase file was then checked for invalid characters; if any were found, they were replaced with an acceptable character or a space for unprintable characters (for example, an "é" character was replaced with an "e" character because the postal software would not accept many of these types of characters). The software required that the dBase file adhere to the dBase standard (an invalid file can be created by using the VFP or MS dBase drivers), so the files were checked for invalid field and row lengths. This newly created dBase file was then processed by the third-party software to verify and correct addresses, report invalid addresses, and put the addresses into a specific sort order. The final output of this software was another dBase file with a specific format.
After I had the file in this pristine state, it was imported into SQL Server. As the data was imported into SQL Server, additional DTS Tasks were executed to transform the data into a common format, and to ensure that email addresses and phone/fax numbers were in a valid format. Users could modify this data while it was stored in SQL Server, so to keep the modification process moving quickly, the data was not verified as thoroughly as it was when it first came in. Later, the software exported the data from SQL Server back into a dBase file to go through the same checks as when the data was imported. After these checks were completed, the dBase file was converted into a comma-separated file, zipped up, and FTP'd to a vendor.