SSIS Error code: 0x80040E37

6
votes

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!?

4
excelssisssis-2008
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) – Tab Alleman

4 Answers

7
votes

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

2
votes

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. :)

Reading files in FOr Each Loop Container

remove read-only property: File System Task Editor

2
votes

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

1
votes

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.)