July 29, 2011 at 4:30 am
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.".
July 29, 2011 at 7:52 am
Have you set the Delay Validation property of your connection manager to True?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply