18. srpna 2012 3:01
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?
20. srpna 2012 13:36Moderátor
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
21. srpna 2012 10:14Moderátor
You can use OPENROWSET function to copy data from SQL Server to Access, please refer to details:
Please feel free to ask if you have any question.
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.