none
SSIS The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered RRS feed

  • Question

  • Hi All,

    In my SSIS package am reading data from .xlsx file and loading it into a database table. Am using "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="  +  @[User::FullPath]  + ";Extended Properties=\"Excel 12.0;HDR=YES\";"

    as the connection string. It works fine when I run from BIDS but when I put it inside job it fails saying OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered . I checked the checkbox Use32 it runtime inside job and in SSIS project property I have made Run64BitRunTime as False.  Nothing seems to be working out.

    Any help is appreciated.

    Thanks

    Thursday, August 9, 2012 11:57 AM

Answers

All replies

  • You must be using the DTEXEC from the 64 bit evironment which would be default path once you are on a 64 bit machine

    Execute it from the job using the dtexc inside the programmfilesx86 folder which is the 32 bit equivalent

    also the RUN64BIT property is only valid while execution from BIDS and has no relation with the JOB


    Abhinav
    http://bishtabhinav.wordpress.com/

    Thursday, August 9, 2012 12:09 PM
    Moderator
  • here is a link to another thread with the same issue and the resolution:

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/20aafd3d-0219-4706-abb4-e4b35aebb18e/

    it says:

    You can download and install only the ACE provider from the following link, this supports office 2007 files both xls and xlsx file.

    http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en



    Please mark the post as answered if it answers your question

    Thursday, August 9, 2012 12:20 PM
  • You must be using the DTEXEC from the 64 bit evironment which would be default path once you are on a 64 bit machine

    Execute it from the job using the dtexc inside the programmfilesx86 folder which is the 32 bit equivalent

    also the RUN64BIT property is only valid while execution from BIDS and has no relation with the JOB


    Abhinav
    http://bishtabhinav.wordpress.com/

    Setting Run64Bit property on the SSIS Project and setting the job step to run under 32bit mode check box eventually is the the same thing. IN BIDS this property simulates a 32bit environment if this property is set to false and on the server the checkbox as i mentioned forces the package to run under the 32bit mode if checked.

    Please mark the post as answered if it answers your question


    • Edited by DotNetMonster Thursday, August 9, 2012 12:24 PM correction
    Thursday, August 9, 2012 12:23 PM
  • Setting Run64Bit property on the SSIS Project and setting the job step to run under 32bit mode check box eventually is the the same thing. IN BIDS this property simulates a 32bit environment if this property is set to false and on the server the checkbox as i mentioned forces the package to run under the 32bit mode if checked.


    Please mark the post as answered if it answers your question



     SETTING the Run64Bit  property has no implications outside BIDS, this property will just impact when the package is run from BIDS and not via DTEXEC . if its required the package to load the 32BIT runtime with DTS the dtexec inside the programmfilesx86 must be called

    Abhinav
    http://bishtabhinav.wordpress.com/

    Thursday, August 9, 2012 12:38 PM
    Moderator
  • Setting Run64Bit property on the SSIS Project and setting the job step to run under 32bit mode check box eventually is the the same thing. IN BIDS this property simulates a 32bit environment if this property is set to false and on the server the checkbox as i mentioned forces the package to run under the 32bit mode if checked.


    Please mark the post as answered if it answers your question



     SETTING the Run64Bit  property has no implications outside BIDS, this property will just impact when the package is run from BIDS and not via DTEXEC . if its required the package to load the 32BIT runtime with DTS the dtexec inside the programmfilesx86 must be called

    Abhinav
    http://bishtabhinav.wordpress.com/

    yup, and to call that 32bit dtexe you have to check the checkbox on the job step just like CoolDbGuy already did.

    Please mark the post as answered if it answers your question

    Thursday, August 9, 2012 12:45 PM
  • Hi Abhinav,

    I have tried both using SSIS and operating System from job. Below is the way i tried using operating sys in job.

    "D:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /SQL "\test_pkg" /SERVER \instance_name  /CHECKPOINTING OFF /REPORTING E

    Still its not working.

    Thanks

    Thursday, August 9, 2012 1:30 PM
  • What is the error when you run it via the 32 bit dtexec, also please confirm your machine bitness 32/64 where you are faced with this problem


    Abhinav
    http://bishtabhinav.wordpress.com/


    Friday, August 10, 2012 6:31 AM
    Moderator
  • Hi Abhinav

    Sorry for the confusion. Today I checked the development server is 64 bit so I tried running without checking 32bitruntime option. I got the below error

    The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

    Could you please suggest.

    Thanks

    Friday, August 10, 2012 7:07 AM
  • Hi,

    maybe this will help:

    I had a similiar issue with a job, getting Data from a oracle Data Source.

    The Problem was, that the SQL Agent User has (per default) no Access Rights (ntfs) to the Oracle Folder (eq Ole DB Driver). As far as I know, there is no 64 bit OLE DB Driver for Excel, so you have to check Use32bit.

    Check if your SQL User (the User you defined in SQL Configuration Manager for the Database instance)  has access rights or try to run the Package as local Admin (for testing!) and then give us a status

    Friday, August 10, 2012 8:14 AM
  • Hi

    When am using  the below excel connection string and checking 32bitruntime checkbox. Its working fine.

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source="  + @[User::FolderPath]  + "\\"+ @[User::FileName]   + ";Extended Properties=\"Excel 8.0;HDR=YES\;

    So I dont think there's any permission issue involved here. No Clues how to fix this.

    Thanks

    Friday, August 10, 2012 9:30 AM
  • Hi Abhinav

    Sorry for the confusion. Today I checked the development server is 64 bit so I tried running without checking 32bitruntime option. I got the below error

    The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

    Could you please suggest.

    Thanks

    So your development environment is 64 Bit thats what you mean, and you tried running you package via BIDS setting the RUN64BITruntime property to FALSE.

    The error you have pasted is through which environment is it via the JOB execution or via the BIDS execution, i could see that your package is trying to still load the 64 bit runtime so that implies you if you ran the package through BIDS then your run64BITRuntime properte = TRUE, else of from JOB it was still using the 64 bit dtexec


    Abhinav
    http://bishtabhinav.wordpress.com/

    Friday, August 10, 2012 9:43 AM
    Moderator
  • Hi

    When am using  the below excel connection string and checking 32bitruntime checkbox. Its working fine.

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source="  + @[User::FolderPath]  + "\\"+ @[User::FileName]   + ";Extended Properties=\"Excel 8.0;HDR=YES\;

    So I dont think there's any permission issue involved here. No Clues how to fix this.

    Thanks

    http://social.msdn.microsoft.com/Forums/br/sqlintegrationservices/thread/289e29ad-26dc-4f90-bad4-ffb86c76e5f9

    regards

    joon

    Friday, August 10, 2012 9:48 AM
  • Are you trying to execute the package from the SQL AGENT?

    if yes: there is a checkbox under the stepconfiguration called: use 32-bit runtime -> (it is the same like the use64runtime (it has only effect in BIDS!!!) in BIDS, but for the execution in SQL AGENT)

    Friday, August 10, 2012 9:52 AM
  • Hi

    The Connection string I mentioned in above two cases works fine in BIDS. While am getting error when make the excel connection string as

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="  +  @[User::FullPath]  + ";Extended Properties=\"Excel 12.0;HDR=YES\";"

    inside job. Yes the development server is 64bit. Its SQL Server 2008 R2. But when I make the connection string as

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source="  + @[User::FullPath]   + ";Extended Properties=\"Excel 8.0;HDR=YES\;

    and check 32bitruntime checkbox inside job its working fine.

    Hope am clear on the problem.

    Thanks

    Friday, August 10, 2012 10:01 AM
  • Ok sou your problem is that you dont have the 64bit drivers installed on the system, i hope as you have excel12.0 you have OFFICE 2010 installed on the system you can find the 64bit drivers here

    http://www.microsoft.com/en-us/download/details.aspx?id=13255

    Download/ INstall  them and then it should fix yor problem, you might have to uninstall the 32 bt OFFICE version and install the 64 bit versin , this would lead to your BIDS excel not working fine, however best i would say for your job call the 32 bit DTEXEC version to resolve the problem,

    Follow this link:
    http://hrvoje.piasevoli.com/2010/09/01/importing-data-from-64-bit-excel-in-ssis/

    If you are a reader then This would help your understanding of issue as to why does it arise :)

    http://msdn.microsoft.com/en-us/library/ms141766(v=sql.105).aspx

    http://usa.autodesk.com/adsk/servlet/ps/dl/item?siteID=123112&id=18307898&linkID=9240697

    Todd has explained in great details here

    http://toddmcdermid.blogspot.com/2009/10/quick-reference-ssis-in-32-and-64-bits.html


    Abhinav
    http://bishtabhinav.wordpress.com/


    • Edited by AB82Moderator Friday, August 10, 2012 11:13 AM
    • Proposed as answer by Eileen Zhao Tuesday, August 14, 2012 9:55 AM
    • Marked as answer by Eileen Zhao Thursday, August 16, 2012 8:56 AM
    Friday, August 10, 2012 11:11 AM
    Moderator
  • Hi All,

    After a long search and trying various options. Finally i got rid of this weird Excel driver error. I was using SQL Server 2008 r2 and the server is 64 bit. I continued  to get this error whn i run the package from job.

    Then I made the below changes to fix the issue.

    1)64Bit Runtime--> False IN BIDS

    2)Job execution option Use 32 bit Runtime Checkbox checked

    3)Download & install the components on the server(64 bit) from the below link

    http://www.microsoft.com/en-us/download/details.aspx?id=23734

    Thanks All

    Friday, October 19, 2012 1:10 AM
  • Todd's exlaination took me directly to my problems.
    Awesome!!!!

    Wednesday, September 11, 2013 2:28 PM
  • Hi,

    i guess you may have Microsoft access Database engine 64 bit, in your machine.please, follow the simple method below and you will be okey.

    1. uninstalled the Microsoft access Database engine 64 bit from your computer(this will be pack only not office).

    2. install the  Microsoft access Database engine 32 bit from link below

    https://www.microsoft.com/en-us/download/confirmation.aspx?id=13255

    OR you can type   Microsoft Access Database Engine 2010 Redistributable  in search bar.

    3. Goto SSDT project and rightclick on project property click on Configuration property and click on Dubugging and

    make Run64BitRunTime False.

    SORRY I CAN NOT ATTACHED IMAGE.

    THANK YOU.


    Wednesday, June 7, 2017 7:38 AM
  • I had same issue and I installed 32 bit version and it works.

    https://www.microsoft.com/en-us/download/confirmation.aspx?id=13255

    Thanks a ton everyone. 


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Monday, October 7, 2019 10:52 PM