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.".

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

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

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!