SSIS Export from SQL to MsAccess ACCDB is ridiculously slow

Question

I created a SSIS package that exports some data from SQL database (using native OLE DB connection) to an Access (accdb) table using ACE.12 driver. The performance is very poor. It takes about 20 minutes to export 10,000 rows. I have tried running the package from BIDS as well as deploying it and running directly from the server.

On the other hand, exporting (from teh same OLE DB source) to mdb (using Jet 4.0 driver) is much much faster. So in my opinion, the problem dot not lie in data retireval, but in the the way ACE.12 driver interacts with the data target.

Is there any particular sutup needed to improve performance of exporting to accdb?

Answer 1

I saw somewhere that DAO is the fastest of all, I did not experiment, but here you can create an ODBC connection using DAO (as per http://support.microsoft.com/kb/200427) and try this ODBC connection against MS Access. Trust you'll get a decent speed.

---

Arthur My Blog

Answer 2

Hi mrfreddie04,

You can use OPENROWSET function to copy data from SQL Server to Access, please refer to details:
http://www.codeproject.com/Tips/43938/Export-data-from-SQL-Server-to-MS-Access 

Please feel free to ask if you have any question.

Thanks,
Eileen

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.