Solved

SSIS not picking up Excel Sheet

Posted on 2010-11-08
12
939 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

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!

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

740 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