Browse DevX
Sign up for e-mail newsletters from DevX


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

SQL Server 2000's DTS offers a copious—even bewildering—bundle of pre-packaged tasks. This 10-Minute Solution, the first of a two-part series exploring DTS tasks, illustrates how to chain different types of tasks together to collect the information you'll need to process data contained in a file location table. This technique is particularly useful in environments that require "after the fact" data updates.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date