July 6, 2011 at 3:29 am
I am trying to process multiple excel files. Used "for loop each container" and Data Flow Task inside the loop.
I am getting below error.not able to solve the problem. Checked in Google tried some of the options but not able to solve the problem.
Please let me know if any other way we can process in SSIS.
Please help me regarding this issue.
please find the error below
TITLE: Microsoft Visual Studio
------------------------------
Error at Package7 [Connection manager "Excel Connection Manager"]: 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: "Invalid argument.".
Error at Data Flow Task [Excel Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
July 6, 2011 at 9:17 am
Entered
""Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FileName] + ";Extended Properties=\"Excel 8.0;HDR=YES\";"" in expression window
when you evaluate expression Following value is displayed in expression window
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties="Excel 8.0;HDR=YES";
here =" + @[User::FileName] + " content is missing tried to enter this manually but was not successful. Any body has any clue about this
July 11, 2011 at 1:35 am
Do all the Excel files have the same layout?
Otherwise your set-up won't work.
Is the @[User::FileName] variable populated with a design time default? Otherwise you won't see a thing when you evaluate the expression.
Also try setting the DelayValidation property of the dataflow to FALSE and the ValidateExternalMetadata propery of the Excel source to FALSE.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply