I am along the way of porting some SQL Server 2005 databases to SQL Server 2008. One of these simple databases comes with an connected import application (Home windows task) which utilizes SSIS having a DTS package to import a sizable dataset from an MS Access database nightly.
In improving to SQL Server 2008, I came across which i can't run exactly the same console application that has been carrying out the imports because of the missing manageddts DLL in SQL Server 2008. It's many years old and looking for a rewrite for a number of reason, plus, I have been fairly unhappy with DTS generally. The initial reason DTS was selected was for speed (5 min import time in comparison to 30+ for ADO.Internet).
The format from the data to import has run out of my control (the customer likes Access). I'd like to have the ability to run the import from the machine completely outside of the server hosting SQL Server and ideally with minimal SQL features installed.
Options I have considered:
- Creating an Access application for connecting to both databases (SQL Server and Access) and carry out the import (Ugh!)
- Returning to ADO.Internet to ascertain if the initial implementation was poorly written.
- Up-to-date SSIS packages.
The other technologies must i be thinking about with this job?
I received an indicator which i use ADO.Internet to transform the .mdb file to a lot of flat files, then use tsql's bulk place statement to import the flat files. Whether it works, I'll mark this because the answer.
This labored very well. Far faster compared to old DTS. One lower side would be that the import user should have bulkadmin privileges, and also the schema from the flat files must precisely match the tables, but just a little coding solved the second, along with a secure, devoted user account solutions the previous.
I am fairly pleased with the end result.
I suppose you should attempt SSIS. If you do not know enough, become familiar with a brand new factor.