SSIS: how to use Access 2010 accdb x64 as a source on win7x64
I'm trying to create an SSIS package that uses Access 2010 accdb as a source (Access 2010 x64 is installed on Win7 x64). But from BIDS (Sql server business intelligence development studio etc., a vis studio frontend), I see a way only to connect to MDB files, but no way to connect to an Access 2010 ACCDB file. How do I do it? Thank you. The destination is SQL 2008 R2 x64, but the source has to be unpivoted/normalized along the way. looking for the best performance.
October 31st, 2010 1:49am

Hello TechVsLife2, use the OLE DB provider similar to what is described here: http://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/importing/Import_Access_DB.asp From there to unpivot you would need to use the Unpivot Transformation (correct me if I misunderstood what you need). Good luck!Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
October 31st, 2010 12:24pm

I already have those providers installed, but it doesn't work (not available from within SSIS). Import and export wizard works (64-bit), but not SSIS. Unfortunately import and export wizard does not do the unpivot (at least not well--there's a severe sql statement size limit on the unpivot statement and it's slow as a dog when doing an unpivot that way). Note that I have access 2010 x64 installed (not 32-bit access 2007). The 32-bit driver CANNOT be installed when the 64-bit driver is installed, and the 64-bit driver must be installed on Access 2010. For example, see: http://www.sqlservercentral.com/Forums/Topic931754-391-1.aspx The problem is this: there is no way to get SSIS (from Sql Server 2008 R2 x64) to work with Access 2010 accdb files (as a source) when the version of access installed is Access 2010 x64. As an added clarification: BIDS and SSMS are 32-bit, but 32-bit ace oledb drivers (access accdb 2010) drivers can NOT be installed if 64-bit office 2010 is installed, as is the case here. The 32-bit jet drivers cannot read the accdb format.
October 31st, 2010 6:01pm

I already have those providers installed, but it doesn't work (not available from within SSIS). Import and export wizard works (64-bit), but not SSIS. Unfortunately import and export wizard does not do the unpivot (at least not well--there's a severe sql statement size limit on the unpivot statement and it's slow as a dog when doing an unpivot that way). Note that I have access 2010 x64 installed (not 32-bit access 2007). The 32-bit driver CANNOT be installed when the 64-bit driver is installed, and the 64-bit driver must be installed on Access 2010. For example, see: http://www.sqlservercentral.com/Forums/Topic931754-391-1.aspx The problem is this: there is no way to get SSIS (from Sql Server 2008 R2 x64) to work with Access 2010 accdb files (as a source) when the version of access installed is Access 2010 x64. As an added clarification: BIDS and SSMS are 32-bit, but 32-bit ace oledb drivers (access accdb 2010) drivers can NOT be installed if 64-bit office 2010 is installed, as is the case here. The 32-bit jet drivers cannot read the accdb format. Further described here: http://dougbert.com/blogs/dougbert/archive/2009/08/28/64-bit-excel-driver-in-microsoft-office-14.aspx
Free Windows Admin Tool Kit Click here and download it now
October 31st, 2010 6:02pm

It seems you are using 64bit Office so you can only install 64bit ACE provider. However, BIDS is 32 bit and it can only call 32bit provider at DESING time (not only for SSIS but also for other BI projects). ACE provider (64bit) is only for 64bit application, and then you cannot use it in your BI project. Could you try to install AccessDatabaseEngine_2007_x86 (note: you cannot install 32bit 2010 driver as you mentioned), closes everything and then reboot to give another try. Hope this helps, RaymondRaymond Li - MSFT
November 1st, 2010 4:23am

Thank you. Do you know if this is an officially supported option (Access 2007 32-bit drivers and Access 2010 64-bit drivers installed on the same machine)? Data drivers can be hard to uninstall cleanly and I'm thinking there must be some good reason Access 2010 32-bit drivers are not approved for installation. Also, unlike older versions of access (e.g. the Jet 4 drivers), the Access 2007 drivers use the same connection string as Access 2010 (oledb ACE 12.0). (The databases make use of a couple of Access 2010 only features--I think the only issue for data export may be if the 2007 driver needs to read the new access 2010 collations. I'll assume not.)
Free Windows Admin Tool Kit Click here and download it now
November 1st, 2010 3:36pm

You simply can't install 32bit and 64bit 2010 drivers on the same machine. The Office team must have completely disregarded the developer use-case from that decision. The ONLY way you can develop a package to use the 2010 64 bit drivers is to develop it on a machine with the 32-bit drivers installed, then deploy that package to a fully 64-bit machine. One way to overcome this is to install/uninstall the correct bitness of the drivers constantly (a bad solution). Another solution is to build a VM with Virtual PC that you can use to develop in 32-bit on your fully 64-bit environment. I have some blog posts about 32/64bit development and Office - but someone else had a better post on solving this problem especially. I can't locate it at the moment, but it was a good step-by-step on how to dev in your exact situation. I'm inquiring for a URL... Talk to me now on
November 1st, 2010 9:26pm

Thanks. Provisionally, I'm doing that, developing with Office 2010 32-bit drivers only (with the SSIS package property to use 64-bit off, since it's actually running on an x64 os) and transferring that final SSIS package to pcs with 64-bit office 2010, but it's less than ideal. The suggestion by Raymond-Lee was that one could side-by-side install office 2007 32-bit drivers with 64-bit office 2010 drivers, but my gut tells me that may not be kosher. however, if others have done it without problem, or if it's officially recommended or supported, I might try it. I found one blog post on a workaround, but it really was useful only for excel files, not Access files. (There are some additional bugs with Access 2010 x64 and sql server-- for example, it turns out that sql server has problems with linked servers to Access 2010 x64 drivers, though it works fine against Excel files with those same x64 drivers.) See: https://connect.microsoft.com/SQLServer/feedback/details/587897/connecting-via-a-linked-server-to-an-access-2010-database-file
Free Windows Admin Tool Kit Click here and download it now
November 2nd, 2010 1:26am

I checked it with Access Group, side-by-side installation is unsupported. I tried to find a workaround but unfortunately, this scenario against the fundamental of Office I have to go with either all 32-bit or all 64-bit. The only really supported approach is to match bitness across all products, sorry to say that:( Thanks, RaymondRaymond Li - MSFT
November 2nd, 2010 7:54pm

Raymond-Lee, Thanks for checking that. The only solution then is what Todd McDermid suggests, getting a 32-bit only Office on one computer (or VM) to design the SSIS packages, and then copying over the package to the computers with 64-bit Office installed. --At least, that's true until such time as BIDS supports 64-bit Office/Access/accdb drivers in design mode, or until Microsoft supports side by side install of 32 and 64 bit office oledb/accdb drivers.
Free Windows Admin Tool Kit Click here and download it now
November 2nd, 2010 8:07pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics