locked
Error Loading multiple excel files from a folder RRS feed

  • Question

  • I am facing issues while loading data from all excel files in a folder in SQL Server 2008. I had changed the connection manager in the Expression of the Excel connection manager to load data from all excel files in a folder dynamically. I changed the Run64BitRunTime to False after reading some blogs for this issue.
    I got the following error.
    Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTON FROM CONNECTION MANAGER. The AcquireConnection method call to the connection manager "ExcelInputFile" failed with error code 0xC0209302. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
    Error: component "DFT_ExcelSrc" failed validation and returned error code 0x020801C.
    Error: One or more components failed validation.
    Error: There were errors during task validation.
    Error:  SSIS Error Code DTS_E_OLEDBNOPROVIDER_ERROR. The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. Error code: 0x00000000. An OLE DB record is availabe. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154
    Description: "Class not registered"
    My Connection String :: 
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +  @[User::vG_SourceFileFolder]   + @[User::vG_SourceFileName] +";Extended Properties=\"EXCEL 12.0;HDR=YES\";"
    I also tried changing the connection string from ACE to Jet. I got the following error after changing.
    Changed Connection String ::
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::SourceFileName] + ";Extended Properties=\"Excel 8.0;HDR=YES\";"
    Error :: 
    [Excel_SRC_BudgetFile [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "cEXC_BudgetFile" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
    [SSIS.Pipeline] Error: component "Excel_SRC_BudgetFile" (1) failed validation and returned error code 0xC020801C.
    [SSIS.Pipeline] Error: One or more component failed validation.
    Error: There were errors during task validation.
    [Connection manager "ExcelInputFile"] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available.  Source: "Microsoft JET Database Engine"  Hresult: 0x80004005  Description: "External table is not in the expected format.".
    Friday, July 29, 2011 10:25 AM

Answers

  • ACE OLE DB provider is loading Excel 2007 and later, JET provider for Excel 2003.

    Which version of Excel are you trying to upload?

    If it is .xlsx, you need to install the ACE OLE DB drivers:

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

    They are available in 64-bit, but there are still quite some issues with the 64-bit version.


    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...
    • Marked as answer by Eileen Zhao Monday, August 8, 2011 6:08 AM
    Friday, July 29, 2011 10:26 AM
  • The data provider or Data connectiivty components to be installed depends upon the version of the Execl input source. The error is clearly due to the unavailability of the office providers. 


    Happy to help! Thanks. Regards and good Wishes, Deepak. In a revamp of my Blog!
    • Marked as answer by Eileen Zhao Monday, August 8, 2011 6:08 AM
    Friday, July 29, 2011 12:11 PM

All replies

  • ACE OLE DB provider is loading Excel 2007 and later, JET provider for Excel 2003.

    Which version of Excel are you trying to upload?

    If it is .xlsx, you need to install the ACE OLE DB drivers:

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

    They are available in 64-bit, but there are still quite some issues with the 64-bit version.


    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...
    • Marked as answer by Eileen Zhao Monday, August 8, 2011 6:08 AM
    Friday, July 29, 2011 10:26 AM
  • Which version of Ms Office you have

    if its 2007 then you will be having 32 bit Ace drivers installed and if you have 2010 then you have to check which edition it is an x64 or an x86.

    if you dont have office installed on the server then you have to download and install the ACE Drivers (2010 or 2007)

     


    --------------------------------------------------------

    Surender Singh Bhadauria

     

    Friday, July 29, 2011 10:52 AM
  • The data provider or Data connectiivty components to be installed depends upon the version of the Execl input source. The error is clearly due to the unavailability of the office providers. 


    Happy to help! Thanks. Regards and good Wishes, Deepak. In a revamp of my Blog!
    • Marked as answer by Eileen Zhao Monday, August 8, 2011 6:08 AM
    Friday, July 29, 2011 12:11 PM
  • Hi,

    The mentioned error occurs when Microsoft Office 2007 is not installed on the machine where SQL Server 2008 Integration Services is installed. In order to resolve this issue you need to install 2007 Office System Driver: Data Connectivity Components on the server where SSIS is running. For more information refer the following article titled “Troubleshooting OLE DB Provider Microsoft.ACE.OLEDB.12.0 is not registered Error when importing data from an Excel 2007 file to SQL Server 2008

    Hope that Helps!

    Cheers,
    Ashish Kumar Mehta

    Please click the Mark as Answer button if a post solves your problem and Vote as Helpful if a Post is Helpful!

    Monday, January 9, 2012 5:32 AM