What is a good automated data import method for SQL Server?

Question

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?

Answer 1

I guess you should try SSIS. If you don't know enough of it, you will learn a new thing.

Answer 2

I received a suggestion that I use ADO.NET to convert the .mdb file to a bunch of flat files, then use tsql's bulk insert statement to import the flat files. If it works, I'll mark this as the answer.

EDIT:

This worked really well. Far speedier than the old DTS. One down side is that the import user must have bulkadmin rights, and the schema of the flat files must precisely match the tables, but a little coding solved the latter, and a secure, dedicated user account answers the former.

I'm fairly happy with the result.