Browse DevX
Sign up for e-mail newsletters from DevX


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

Part II of this DTS task series illustrates how the author used multi-step DTS packages to build a solution that consolidates an indeterminate amount of Excel data in one SQL Server table. The solution repeats this process so it catches updated data, and it executes once for each Excel file.




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

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.

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