Solved

SSIS not picking up Excel Sheet

Posted on 2010-11-08
12
938 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
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

828 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