Export to access extremely slow
-
Hi all, I'd appreciate very much if someone could answer my question bellow.
I created an SSIS package using the Export and Import wizard that exports some tables from SQL server to Access.
The problem is that it's an extremely slow process for a table with just 200.000 rows.
I guess it may take up to 30 minutes while exporting to a flat file takes 10 seconds!
Again, any help is appreciated, even if in other technology.
Thank you!!
-
afilipao20 (6/24/2010)
Hi all, I'd appreciate very much if someone could answer my question bellow.I created an SSIS package using the Export and Import wizard that exports some tables from SQL server to Access.
The problem is that it's an extremely slow process for a table with just 200.000 rows.
I guess it may take up to 30 minutes while exporting to a flat file takes 10 seconds!
Again, any help is appreciated, even if in other technology.
Thank you!!
The regular OLEDB Destination component uses INSERT INTO ... statement for each row of data, making the export incrementally slower. It is not only slow, it is getting slower as the destination Access file grows.
If you can use third-party solutions, I would recommend you check the commercial CozyRoc ODBC Destination component, which supports ODBC bulk-load API. It is part of the SSIS+ library. The ODBC Destination supports Microsoft Access and compared to the OLEDB Destination, it never slows down. It can insert 1,000,000 records in less than 5 minutes.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply