?
Solved

SSIS not picking up Excel Sheet

Posted on 2010-11-08
12
Medium Priority
?
954 Views
Last Modified: 2013-11-10
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
Comment
Question by:hydev
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34085148
maybe the file is in use by another application?
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 34085841
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
 
LVL 10

Expert Comment

by:Banthor
ID: 34089309
Make sure that RUN64BitMote is set to False for XLS files on 64 bit machines.
 
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 10

Expert Comment

by:Banthor
ID: 34089311
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
 

Author Comment

by:hydev
ID: 34091898
Banthor - where is that setting?

Thanks

mike
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 34093433
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
 

Author Comment

by:hydev
ID: 34101256
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
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 34102440
Hi,

TRy with the default name of sheet (Sheet1) etc. and also make sure file is not open to elsewhere.
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 34102777
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
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34104096
could you upload the excel file which we could look here?
0
 

Accepted Solution

by:
hydev earned 0 total points
ID: 34211236
I couldn't get this to work so I reverted to CSV file instead.
0
 

Author Closing Comment

by:hydev
ID: 34272642
I did work around the issue.  I still can't get it to work.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question