8

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

AHiggins
  • 7,029
  • 6
  • 36
  • 54
user1254579
  • 3,901
  • 21
  • 65
  • 104
  • 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 Jun 20 '16 at 13:54

5 Answers5

7

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

BIDeveloper
  • 2,628
  • 4
  • 36
  • 51
  • 1
    It does exist.That is why i was telling when try to execute the oledb task independently then alll good – user1254579 Jun 20 '16 at 13:37
  • 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? – BIDeveloper Jun 20 '16 at 13:53
  • 1
    the for each loop excelfilepath is refering to a wrong location( .Found it in debugg mode.) eventhough i have given a different file path!! – user1254579 Jun 20 '16 at 14:05
  • 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. – MadMarc Jan 18 '21 at 09:16
3

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

Monic
  • 726
  • 10
  • 31
  • 1
    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. – John McCann Sep 25 '18 at 23:02
2

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

lachs
  • 361
  • 1
  • 4
  • 12
1

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

Doug_Ivison
  • 778
  • 7
  • 17
0

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.

ZygD
  • 22,092
  • 39
  • 79
  • 102
Willy Taveras
  • 1
  • 1