question

PauloMonaco avatar image
PauloMonaco asked

SSIS inconsistent performance exporting to MS-Access

Hi everyone. I am in MS-Access / SSIS hell, and will apreciate every help from the experienced fellows here. I have to export some data from SQL Server into MS-Access tables every morning (small amount: 40,000 rows max). I setup an SSIS with ODBC connection to the Access "mdb" file. It is basic, simple, and it works. However, it presents inconsistent performance. Usually, the SSIS runs in 1 to 4 minutes (depending on data volume in the day). But occasionally, it takes 2 hours or more to complete. The SOURCE SQL Server is installed and runs on Dallas, Texas. That is where the SSIS runs from. The TARGET MS-Access "mdb" file is located on a server in Kansas City, Missouri. What seems to be somewhat consistent are the conditions when this happens: 1. When the SSIS runs "fast", it is mostly on weekends, holidays, or on business days, before 7:00 am in Texas. 2. When the SSIS runs "slow", it is always on business days, after 7:00 am in Texas. 3. But I've had some "fast" runs on business days, after 7:00 am in Texas too. Nonetheless, other SSIS packages exporting TEXT files to the same destination server in Missouri run consistently "fast" everyday, anytime. I already tried to RENAME the MS-Access "mdb" file during the SSIS run, but it did not solve my problem. My idea was to "break" any connections to the MS-Access "mdb" file, in an attempt to be the only process using it. Does anyone know what could be causing this odd behaviour, and any ideas on how to overcome it?? I want to avoid exporting a TEXT extract, and then writing a MACRO in the MS-Access to Import it. This would add complications to scheduling this task and monitoring its completion. Thanks and best regards, Paulo Monaco.
ssisaccess
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Valentino Vranken avatar image
Valentino Vranken answered
Try using OLE DB instead of ODBC, it should be faster. The provider is called something like "Microsoft Office 12.0 Access Database Engine OLE DB Provider". If you don't have the ACE driver installed, you can always install it through the free redistributable. Current version: [Microsoft Access Database Engine 2010 Redistributable][1] I would also try to avoid connecting to a remote file. If possible, fill the Access file locally (which means on a server at the same location as the SSIS server) and copy it to the remote location as an additional step in the control flow. [1]: http://www.microsoft.com/en-us/download/details.aspx?id=13255
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

PauloMonaco avatar image PauloMonaco commented ·
Valentino. I will try this approach, thanks. As for avoiding connecting to the remote server, it will be difficult. The target MS-Access "mdb" file belongs to our users. I export to it, but there are other updates that prevent me from having a copy on my server. I started conversations to ask them to have SQLExpress installed on their server, so I can export to it, and they can Link their MS-Access table to it. But performance of the link might be an issue, as well as support to SQLExpress databases. Thanks again. I will let you know if the OLE DB approach works. Paulo Monaco.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.