SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37

I am using SSIS 2008, trying to import several Excel files into SQL Server using a FOREACH loop.  I can successfully do this for just one file.  But now I specify a folder containing Excel files and it errors although it does write some records.  All Excel files have same columns and format.  And I only need data from the first worksheet in each file.  

Caveat is that the worksheet names between files differs slightly.  My boss wants a solution that is permanent without intervention so that each year customers can upload a new file and SSIS package doesn't need to be changed.  So although I could manually change worksheet name(s), this is not ideal.  Also a loop looping through all Excel files in specified folder is what he wants.

Here is the error I get in SSIS when I run my SSIS package using variables:

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.

Advice?  When I looked at the data added to my SQL database, only records from the first Excel file were added.
ironryan77Asked:
Who is Participating?
 
ValentinoVBI ConsultantCommented:
Try this one:
strTomorrow = DateTime.Today.AddDays(1).ToString("yyyyMMdd");

Open in new window

0
 
EvilPostItCommented:
I think the key error message here is...

"Opening a rowset for "'AM Product Actions 2010$'" failed. Check that the object exists in the database."

Could you check that the correct worksheet is being referenced here.
0
 
ValentinoVBI ConsultantCommented:
If the sheet names differ, you'll need to build them dynamically.  The following should work:

Create a Package Variable and construct the name of the sheet using a Script task.  This script task should be located in your ForEach loop, before the Data Flow task.
In the Excel Source, choose "Table name or view name variable" as Data Acces Mode.  Then select your new variable in the dropdown below.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ironryan77Author Commented:
@EvilPostIt, I checked and correct worksheet is referenced.  But there should be another worksheet (different file) titled "AM Product Actions 2011$" under same folder.  I changed my connection string now to specify this folder rather than the Excel file.  But now when I run it I get a different error with 0 records added to my database:
SSIS package "Package2.dtsx" starting.
Information: 0x4004300A at Data Flow Task 4, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at Package6, Connection manager "SourceConnectionExcel": 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: "The Microsoft Jet database engine cannot open the file 'C:\Documents and Settings\daultrd\My Documents\Excel input files'.  It is already opened exclusively by another user, or you need permission to view its data.".
Error: 0xC020801C at Data Flow Task 4, Source - 'AM Product Actions 2010$' [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task 4, SSIS.Pipeline: component "Source - 'AM Product Actions 2010$'" (1) failed validation and returned error code 0xC020801C.
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 (5) 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.
0
 
EvilPostItCommented:
Key error...

It is already opened exclusively by another user, or you need permission to view its data

Do you have it open yourself?
0
 
ironryan77Author Commented:
@EvilPostIt, no it is not opened outside of SSIS.  And no other users have it open.  Not sure about SSIS though, if it retains connection even after execution has stopped.  How would I check if I need permission to view its data?  I can open this file and view its data in Excel.
0
 
ValentinoVBI ConsultantCommented:
ironryan7: I believe my comment above contains the solution to your problem.  Could you please have a look at it, try it out, and post any follow-up questions if needed?
0
 
ironryan77Author Commented:
@ValentinoV,
Yes, I'm trying to implement what u said but am pretty new with SSIS.  Is there a URL that u could refer me to in how to create this Package Variable in Script task?
0
 
ValentinoVBI ConsultantCommented:
I think you'll find this an interesting read: http://www.simple-talk.com/sql/ssis/passing-variables-to-and-from-an-ssis-task/

One thing to watch out for when using package variables: they are scoped!  The scope of your variable depends on what object is active when you click the "Create Variable" button.  The best way to ensure that the variable is available in the whole package is to first click the yellow background in the Control Flow, then click the Create Variable button.
Just experiment a bit creating/deleting variables with different objects active and you'll see what I mean.
0
 
ironryan77Author Commented:
@Valentino:
Thanks so much for this URL!  It does seem simple, however, this was written in VB and I use C#.  So when I tried to add this function in C#, I got the following error.  Do you know how I can fix this?

Error      1      Non-invocable member 'System.DateTime.Today' cannot be used like a method.      C:\Documents and Settings\daultrd\Local Settings\Temp\SSIS\ST_ceaa126ff88343ccbfdc6dd27d8de1a7\ScriptMain.cs      56      67      ST_ceaa126ff88343ccbfdc6dd27d8de1a7

And the offending line:
            strTomorrow = Convert.ToString(String.Format(DateTime.Today().AddDays(+1), "yyyyMMdd"));   //Strings.Format(DateAndTime.Today().AddDays(+1), "yyyyMMdd"));

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.