What is a good automated data import method for SQL Server?
- by Joel Potter
I'm in the process of porting some SQL Server 2005 databases to SQL Server 2008. One of these databases has an associated import application (Windows task) which uses SSIS with a DTS package to import a large dataset from an MS Access database nightly.
In upgrading to SQL Server 2008, I discovered that I can't run the same console application which has been performing the imports due to the missing manageddts DLL in SQL Server 2008. It's several years old and in need of a rewrite for various reason, plus, I've been fairly unhappy with DTS in general. The original reason DTS was chosen was for speed (5 min import time compared to 30+ for ADO.NET).
The format of the data to import is out of my control (the client likes Access). I would also like to be able to run the import from a machine completely separate from the server hosting SQL Server and preferably with minimal SQL features installed.
Options I've considered:
Creating an Access application to connect to both databases (SQL Server and Access) and perform the import (Ugh!)
Revisiting ADO.NET to see if the original implementation was poorly written.
Updated SSIS packages.
What other technologies should I be considering for this job?