June 13, 2008 at 8:39 am
Hi,
i am trying to load multiple excel sheets, but i get this error, i followed these instructions, can you help please
1. i created a foreach loop.
2. changed collection expression to 'Directory @user::excel file'
2. changed teh folder location for each loop container.
4. changed files to *.xls.
5. added variable mapping Filename(which is a variable that contains my filename) and filepath.
6. dropped a data flow task.
7. in data flow got my excel source going to ole db destination.
8. for excel connection manager created expression connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelFile] + @[User::Filename] + ";Extended Properties=\"" + @[User::ExtProperties] + "\""
9. ran the ssis and i am getting this error message::
[Excel Source [1]] Error: 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.
[DTS.Pipeline] Error: component "Excel Source" (1) failed validation and returned error code 0xC020801C.
[Connection manager "Excel Connection Manager"] 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: "Failure creating file.".
June 13, 2008 at 9:42 am
If you want to load all the excel files in a directory, you just need folder location and files(*.xls). You don't have to use variables for these. Inside you can use data flow task to load these files to a oledb destination.
-V
June 16, 2008 at 3:47 am
All you would need is the variable Filename (which contains your filename and filepath)
- In the for each enumerator, you would have to map the Filename variable (on variable mappings, add User::Filename)
- In the excel connection manager, under the expressions section, add an excel filepath @[User::Filename]
TADA
Good luck
~PD
August 26, 2008 at 12:06 pm
did you solve the problem?
I am getting the same error. I am trying to open the file using OLEDB
August 26, 2008 at 12:31 pm
i found the fix. i use \\servernameetwork_path and give appropriate permissions.
November 11, 2008 at 1:35 pm
Can you please detial the fix. I have the same problem
if i use connection string I get the Could not find installable ISAM."
December 15, 2008 at 1:53 pm
I am facing the similar issue.Can some one have any idea on the error?
July 16, 2009 at 2:27 am
HI,
I redid the Excel source part, and instead of step (8) in the original email, I just put in the variable.
That worked.
HTH,
Jen
April 7, 2011 at 3:03 pm
Can you please expound on how u did this? I tried the above steps but I get the following error:
SSIS package "Package2.dtsx" starting.
Information: 0x4004300A at Data Flow Task 4, SSIS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task 4, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task 4, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Data Flow Task 4, SSIS.Pipeline: Execute phase is beginning.
Information: 0x402090DF at Data Flow Task 4, OLE DB Destination [212]: The final commit for the data insertion in "component "OLE DB Destination" (212)" has started.
Information: 0x402090E0 at Data Flow Task 4, OLE DB Destination [212]: The final commit for the data insertion in "component "OLE DB Destination" (212)" has ended.
Information: 0x40043008 at Data Flow Task 4, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x4004300B at Data Flow Task 4, SSIS.Pipeline: "component "OLE DB Destination" (212)" wrote 40 rows.
Information: 0x40043009 at Data Flow Task 4, SSIS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300A at Data Flow Task 4, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at Data Flow Task 4, Source - 'AM Product Actions 2010$' [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error: 0xC02020E8 at Data Flow Task 4, Source - 'AM Product Actions 2010$' [1]: Opening a rowset for "'AM Product Actions 2010$'" failed. Check that the object exists in the database.
Error: 0xC004706B at Data Flow Task 4, SSIS.Pipeline: "component "Source - 'AM Product Actions 2010$'" (1)" failed validation and returned validation status "VS_ISBROKEN".
Error: 0xC004700C at Data Flow Task 4, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task 4: There were errors during task validation.
Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package2.dtsx" finished: Failure.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply