Thursday, 13 February 2014

Connecting to Excel (XLSX) in SSIS

Case
I cannot access my Excel sheets in SSIS. The Excel version in the Connection Manager is Microsoft Excel 2007 (xlsx).
Could not retrieve the table information for the
connection manager 'Excel Connection Manager'.
Failed to connect to the source using the
connection manager 'Excel Connection Manager'




























Solution
XLSX files don't use the out-of-the-box Microsoft.Jet.OLEDB provider, but they need the Microsoft.ACE.OLEDB provider. You either did not install it or you installed the 64bit version.

Download and install the 32bit version of the Microsoft Access Database Engine 2010 Redistributable. Because Visual Studio (SSDT/BIDS) is 32bit you can't use the 64bit provider for developing SSIS packages. If you already installed the 64bit version then you first need to remove it. You can't install 32bit and 64bit parts of office on the same machine. You will get an error when you run the installer (and you will get the same error if you have a 64bit version of Microsoft Office installed on your development machine):
You cannot install the 32-bit version of Microsoft
Access Database Engine 2010 because you currently
have 64-bit Office products installed. If you want to
install 32-bit Microsoft Access Database Engine 2010,
your first need to remove the 64-bit installation of
office products.

















However this means that you can't run packages with Excel Connection Mangers in 64bit on your development machine. You need to switch to 32bit, otherwise you will get an error like:
Information: 0x4004300A at DFT - xlsx source, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC0209303 at xlsxSource, Connection manager "Excel Connection Manager": The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered.
If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
Error: 0xC001002B at xlsxSource, Connection manager "Excel Connection Manager": The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. For more information, see http://go.microsoft.com/fwlink/?LinkId=219816
Error: 0xC020801C at DFT - xlsx source, EX_SRC - My XLSX Source [8]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at DFT - xlsx source, SSIS.Pipeline: EX_SRC - My XLSX Source failed validation and returned error code 0xC020801C.
Error: 0xC004700C at DFT - xlsx source, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at DFT - xlsx source: There were errors during task validation.

But since there is a 64bit driver... you could install it on your test/acceptance/production server and run the packages in 64bit (as long as you don't use Visual Studio on those servers).

Geplaatst door Joost van Rossum
Labels: ,

14 comments:

  1. Koen Verbeeck15 February 2014 at 14:06

    Or you force the installation of the 64-bit provider, as I explain here:
    http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/mssqlserver/force-ace-installation/

    ReplyDelete
    Replies
    1. Joost van Rossum15 February 2014 at 17:58

      Hi Koen,

      Thanks for reading :-)
      I tried that before, but on my SQL 2012 (WinServer2008R2) machine Visual Studio keeps validating the dataflow when opening it with 64 on. With 32 on it immediately fails validation:
      Validation error. DFT - xlsx source EX_SRC - My XLSX Source: Validation errors occurred. xlsxSource.dtsx
      Also tried VS2012... same error. Any suggestions?

      Regards,

      Joost

      Delete
    2. Jordan Glassman13 March 2014 at 22:14

      Yeah, I don't think it's that simple. I blew half a day on this problem recently. See endless discussion here:

      http://social.msdn.microsoft.com/Forums/en-US/abf34eea-1029-429a-b88e-4671bffcee76/why-cant-32-and-64-bit-access-database-engine-aceoledb-dataproviders-coexist

      and here:

      http://stackoverflow.com/questions/12270453/ms-access-db-engine-32-bit-with-office-64-bit

      I think Microsoft has just punted on these problems. I mean, it's their own software.

      Delete
  2. Danny Teok 6 March 2014 at 23:52

    Any ideas how to "run the package in 32-bit mode" on my 64-bit machine without having to remove stuffs?

    The error message I got:
    [Connection manager "Excel Connection Manager"] Error: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.
    An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

    ReplyDelete
  3. Unknown20 July 2015 at 09:21

    i have installed Microsoft Access Database Engine 2010 Redistributable but still i am facing error


    Could not retrieve the table information for the connection manager 'Excel Connection Manager'.
    Failed to connect to the source using the connection manager 'Excel Connection Manager'

    ReplyDelete
    Replies
    1. Joost van Rossum20 July 2015 at 20:36

      Please give more details (which version of SSIS/Running as job or in visual studio/running in 32 or 64 bit). Or else ask a question in the SSIS forum on msdn

      Delete
  4. Unknown5 August 2015 at 09:55

    i have installed Microsoft Access Database Engine 2010 Redistributor but still i am facing same error

    please help thanks in advance

    ReplyDelete
  5. Unknown9 December 2015 at 15:08

    Thanks for the solution. It works perfectly for me.

    ReplyDelete
  6. kafaru10 June 2016 at 19:07

    after reinstalling the redistributable on windows 2012 -64bit, sql 2014 i still get the same error

    ERROR: ErrNo: 7390, ErrSource: Aspa_BNR_Template, ErrLine: 1, ErrMsg: The requested operation could not be performed because OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not support the required transaction interface.

    ReplyDelete
  7. raj4 August 2016 at 22:54

    I was trying to use with Excel and I had the same issue and it worked after installing the Access Redistributable. I also had to update the package to disable 64bit execution and job to run in 32bit mode. Thank you for the post!

    -R

    ReplyDelete
    Replies
    1. Unknown16 October 2019 at 04:10

      If you are using visual Studio professional 2017, this is common problem. Here use sql command. Example: select * from [sheet1$]. It worked for me this way but wotking with Microsoft to fix the problem prominently.

      Delete
  8. rchacko20 April 2020 at 08:24

    Hope this thread helps:

    https://stackoverflow.com/questions/23896901/ssis-excel-connection-manager-failed-to-connect-to-the-source

    ReplyDelete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Subscribe to: Post Comments (Atom)
Related Posts Plugin for WordPress, Blogger...