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.
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] 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. :