Coordinate Your DTS Tasks, Part II: Import an Indeterminate Amount of Excel Data into SQL Server

art I of this two-part series exploring DTS tasks illustrated how to chain different types of tasks together to collect the information you need to process data contained in a file location table. It's a technique I used during a SQL Server project in which I built a solution that automatically refreshes check register data from Excel for a client's Finance department.

The department needed to maintain a current copy of a check register that was subject to subsequent edits. The organization's business rules allowed users to reopen check registers from a prior period and make corrections or additions. I had to figure out how to import an indeterminate number of items from a directory (the check register directory) into a SQL Server staging table, so that the records would be stamped with the directory from which they came and the time of the import.

The solution I chose was using the DTS Execute Process task to execute a batch file that redirects the result of a dir command to a text file. I then used the DTS Connection for a Text File Source to pipe the contents of the text file into a Transform Data task. This filled a table that held the names of the files I wanted to process.

This second and final installment of the DTS task series completes my solution by illustrating how I used multi-step DTS packages to consolidate fifty Excel workbooks worth of check register data in one SQL Server table.

How can I import an indeterminate number of data items from an Excel directory into a SQL Server table?



For your source, use the Excel 2000 driver from the dropdown on the Excel Connection. To transform an Excel workbook or a text file, open the DTS Designer and drag an Excel connection onto the work surface. Fill in the information needed to complete the connection on the design surface.