Solved

SSIS not picking up Excel Sheet

Posted on 2010-11-08
12
944 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
[Unsupported] SQL Update in CRM 2011 Database 19 64
how to solve "recursive" database tables 2 55
mssql 7 32
SQL: Transformation or Pivot 3 35
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

734 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