Coordinate Your DTS Tasks, Part I: "After the Fact" Data Updates

he version of DTS in SQL Server 2000 provides a bundle of pre-packaged tasks. Most are dedicated to moving data, but some are specific to SQL Server maintenance. The data tasks facilitate moving information between database servers (not limited to SQL Server), or moving other file types from FTP or text files into databases. Some tasks are specific to SQL Server maintenance (e.g., transferring databases or transferring user logins), but the bulk of the functionality is dedicated to moving data.

From a higher-level view, DTS tasks perform rudimentary jobs that you can use to get your work done. However, they got me only almost home because I was using them "right out of the box." I didn't understand how to connect them to fulfill my requirements. Once I learned how to coordinate several different types of tasks, I found that DTS allows access to other object models (ADO), which enabled me to reach my processing goals.

How can I import an indeterminate number of items from a directory into a staging table and stamp the records with the directory from which they came and the time of the import? How do I then parse each file name into fields in the staging table by identifying the elements contained in the imported file?



Use the DTS Execute Process task to execute a batch file that redirects the result of a dir command to a text file. Then use the DTS Connection for a Text File Source to pipe the contents of the text file into a Transform Data task. This fills a table that will hold the names of the files you want to process.