[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 966
  • Last Modified:

SSIS not picking up Excel Sheet

Hi,

I have a SSIS package that contains a Data Flow, within that Data Flow is an Excel Source. I Visual Studio/BIDS I configure the Excel Source and point it at the file in question. I know it can read it because the preview returns the data.

However when I run the package from top to bottom it picks up the file but says "Error: opening a rowset for "mike$" failed".  How it can it find the sheet in the designer but when you run it, it doesn't?

Any help greatly appreciated.

Mike
0
hydev
Asked:
hydev
  • 4
  • 3
  • 2
  • +2
1 Solution
 
Reza RadCommented:
maybe the file is in use by another application?
0
 
vdr1620Commented:
Make sure that file is not in use and then close BIDS, reopen it and then try to execute it.. I saw message disappeared in few occasions after reopening BIDS
0
 
BanthorCommented:
Make sure that RUN64BitMote is set to False for XLS files on 64 bit machines.
 
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
BanthorCommented:
I am haveing a similar issue but only when trying to run the packages from a  command line.

Description: 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: "Could not find installable ISAM.".
0
 
hydevAuthor Commented:
Banthor - where is that setting?

Thanks

mike
0
 
vdr1620Commented:
Here's the link to change the Mode
http://sqlblog.com/blogs/john_paul_cook/archive/2010/03/24/running-32-bit-ssis-in-a-64-bit-environment.aspx

Also, the error that comes when trying to use excel in 64 bit mode would be as below,but then you can give it a try

http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/289e29ad-26dc-4f90-bad4-ffb86c76e5f9


0
 
hydevAuthor Commented:
I have checked it's not in use by another application and the 32/64bit is misleading.  I have changed it both ways and the error is the same.  

The problem is that SSIS can find the Excel file and but the issue is finding the sheet even though I have told it the sheet name it says "not there".

Is there a way where you can just tell it to use the first regardless of name?

0
 
Alpesh PatelAssistant ConsultantCommented:
Hi,

TRy with the default name of sheet (Sheet1) etc. and also make sure file is not open to elsewhere.
0
 
vdr1620Commented:
NO, you cant tell it to use the 1st regardless of name.. you can build the SQL using BUILD SQL in the Excel source, where you can select the available sheets and also run it to see the preview.. I would suggest you to take that  approach as you will be able to see what the actual sheetName is (according to ssis)
0
 
Reza RadCommented:
could you upload the excel file which we could look here?
0
 
hydevAuthor Commented:
I couldn't get this to work so I reverted to CSV file instead.
0
 
hydevAuthor Commented:
I did work around the issue.  I still can't get it to work.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now