SSIS Error code: 0x80040E37

Question

There is a dataflow task,which consists of excel source and sql destination.When executing dataflow task alone it is runing good.If i execute this dataflow taks inside a a for each loop ,it gives below error

[Excel Source [17]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.

[Excel Source [17]] Error: Opening a rowset for "'Sheet Data$'" failed. Check that the object exists in the database.

[SSIS.Pipeline] Error: "Excel Source" failed validation and returned validation status "VS_ISBROKEN".

Have anybody got similar problem!?

Edee

Show us how you are implementing the foreach loop. Include everything that changes from when it works (without the loop) to when it doesn't (with the loop) – Phonetic

Answer 1

The sheet which SSIS is trying to open in your Excel file does not exist.

Senility

It does exist.That is why i was telling when try to execute the oledb task independently then alll good – Edee

My answer is still correct- the worksheet does not exist - why it doesn't exist is difficult to deduce with the information provided. Is your ForEach set up correctly? How many spreadsheets does it process before the error? Have you tried just looping through some flat text connections first? – Senility

the for each loop excelfilepath is refering to a wrong location( .Found it in debugg mode.) eventhough i have given a different file path!! – Edee

I faced the same issue, setting up ValidateExternalMetadata in the Excel source properties to false fixed it. This is because the Excel file name is dynamically defined from a parameter, at validation time it didn't exist. – Chretien

Answer 2

I had the same problem. And as I found out the reason was the read-only property.

When I added before Data Flow Task task: File System Task where I set Excel property ReadOnly=False it started working.

Here are some screens. I hope it can help someone. :)

Hanan

Thank you for taking the time to post this answer. Yes, it helped someone. My wife doesn't have an account, so I'm thanking you on her behalf. You have my up-vote. – Gujarat

Answer 3

In my case this error was due to a referenced Named Range in the Excel book's Name Manager being deleted.

Feverish

Answer 4

I don't know if this will help with an Excel source component, but in case it does:

To resolve this, I just had to go into the Data Flow task, into the Excel component (source component for the original poster, destination component for me), and in the 3rd drop-down, re-select the "Name of the Excel sheet:" Then the package ran without error.

I guess the tab names initially generated by the SSIS Import and Export Wizard -- tab names that matched the table names -- were TOO LONG: my "create table" step (Prep SQL task) continues to show those long tablenames, but in the spreadsheet the tab names are truncated, and in the Excel component of the Data Flow task, I had to select the spreadsheet tab name ending with "$".

(Note re' my initial answer:
In my case, I initially thought it was a problem with the mappings (re-mapping was one of the things I did, when the problem went away), but... When it happened again, I discovered that the only necessary step was the other thing I did -- the re-selection of the Excel spreadsheet name.)

Spherulite

Answer 5

I solved this issue by creating a new sheet to the file, selecting that new one, clicking "preview" and selecting back the official one.

Seems like when you change files or something the package is still referencing the old one. Doing what I mention below will solve the issue.

Tattle