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-4 : Page 4




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

Good Solution—Could've Been Better
So that's how I managed to import an indeterminate number of Excel worksheets into SQL Server. Having the check register data available in the database allowed me to give the Finance guys an automated report that isolated mismatches or missing checks, which saved them hours of manual searching through paper registers or spreadsheets.

As usual, after I finished the work, I got a couple more ideas for tightening it up. One big improvement to the process would be to use the lookup tab on the Data Driven Query task to check the imported line against the contents of the table. This would change the total refresh into a differential one, where the lookup would import the row only if it did not exist in the check register table.

Lookups are not without their own limitations, however. The biggest of which is the extra time they take to run, since they don't work by sets but by a row at a time. Testing would establish its applicability. If the number of changes were small, it may improve execution speed, but if many changes occurred, it would be best to manage them in a set-based solution.

Finally, I would like to thank my colleague, Sesha Chivakula, for her help in creating this solution.

Drew Georgopulos is the database architect for GoAmerica Communications. He moonlights as an adjunct professor of computer science at Marymount College of Fordham University, teaching systems analysis and relational database design. .
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