April 28, 2015 at 3:50 pm
I am running in to an issue with the Excel Source in SSIS.
I am using the new 2012 Integration Services Catalog with Project deployment. I can run the package in SSDT and everything executes just fine. When I deploy it to the server I get the following error:
Data Flow Task:Error: Opening a rowset for "sheet1$" failed. Check that the object exists in the database.
My package is very simple I have a Data Flow Task with an Excel (2007) Source and OLE DB destination. I have set everything to delay validation as that is what everything I can find on the subject says to do. I have also deleted the tasks and recreated them. Anyone else have this problem? I have an identical package in SSIS 2008 and it works.
April 29, 2015 at 12:34 am
tfendt (4/28/2015)
I am running in to an issue with the Excel Source in SSIS.I am using the new 2012 Integration Services Catalog with Project deployment. I can run the package in SSDT and everything executes just fine. When I deploy it to the server I get the following error:
Data Flow Task:Error: Opening a rowset for "sheet1$" failed. Check that the object exists in the database.
My package is very simple I have a Data Flow Task with an Excel (2007) Source and OLE DB destination. I have set everything to delay validation as that is what everything I can find on the subject says to do. I have also deleted the tasks and recreated them. Anyone else have this problem? I have an identical package in SSIS 2008 and it works.
Can the server actually see where the spreadsheet is actually stored? For example, remember that the C: drive on your desktop isn't the same as the C: drive on the server and that the server login might not have privs to shared drives that YOU do during development.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 29, 2015 at 1:33 am
tfendt (4/28/2015)
I am running in to an issue with the Excel Source in SSIS.I am using the new 2012 Integration Services Catalog with Project deployment. I can run the package in SSDT and everything executes just fine. When I deploy it to the server I get the following error:
Data Flow Task:Error: Opening a rowset for "sheet1$" failed. Check that the object exists in the database.
My package is very simple I have a Data Flow Task with an Excel (2007) Source and OLE DB destination. I have set everything to delay validation as that is what everything I can find on the subject says to do. I have also deleted the tasks and recreated them. Anyone else have this problem? I have an identical package in SSIS 2008 and it works.
Obvious question: is sheet1 present in the excel file?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 29, 2015 at 7:51 am
Jeff Moden (4/29/2015)
tfendt (4/28/2015)
I am running in to an issue with the Excel Source in SSIS.I am using the new 2012 Integration Services Catalog with Project deployment. I can run the package in SSDT and everything executes just fine. When I deploy it to the server I get the following error:
Data Flow Task:Error: Opening a rowset for "sheet1$" failed. Check that the object exists in the database.
My package is very simple I have a Data Flow Task with an Excel (2007) Source and OLE DB destination. I have set everything to delay validation as that is what everything I can find on the subject says to do. I have also deleted the tasks and recreated them. Anyone else have this problem? I have an identical package in SSIS 2008 and it works.
Can the server actually see where the spreadsheet is actually stored? For example, remember that the C: drive on your desktop isn't the same as the C: drive on the server and that the server login might not have privs to shared drives that YOU do during development.
Yes it can see it. I am using a network share. I also logged into the SSIS server as the proxy account and navigated to the file. This package has another data flow that uses the older excel connector (98-2003) and it works fine so I know permissions are correct.
April 29, 2015 at 7:52 am
Koen Verbeeck (4/29/2015)
tfendt (4/28/2015)
I am running in to an issue with the Excel Source in SSIS.I am using the new 2012 Integration Services Catalog with Project deployment. I can run the package in SSDT and everything executes just fine. When I deploy it to the server I get the following error:
Data Flow Task:Error: Opening a rowset for "sheet1$" failed. Check that the object exists in the database.
My package is very simple I have a Data Flow Task with an Excel (2007) Source and OLE DB destination. I have set everything to delay validation as that is what everything I can find on the subject says to do. I have also deleted the tasks and recreated them. Anyone else have this problem? I have an identical package in SSIS 2008 and it works.
Obvious question: is sheet1 present in the excel file?
Yes, sheet1 is present in the file. It works in SSDT.
April 29, 2015 at 12:58 pm
Are you executing right from the catalog or from an agent task or from a query?
April 29, 2015 at 1:00 pm
Nevyn (4/29/2015)
Are you executing right from the catalog or from an agent task or from a query?
Agent Task. Right now I am leaning towards a dll issue on the server.
April 29, 2015 at 1:32 pm
Are you executing as 32 bit or 64 bit?
April 29, 2015 at 1:35 pm
Nevyn (4/29/2015)
Are you executing as 32 bit or 64 bit?
32bit. Thinking it might be something weird with the drivers on my system I installed SSDT right on the test server and created a new package and deployed it to the catalog. Same issue, I can execute it just fine while in SSDT, doesn't execute on the server.
April 29, 2015 at 2:55 pm
Found something interesting. I imported the package into the msdb and created an agent job for it. It runs successfully! I wonder if there is a bug using the SSIS Catalog?
April 30, 2015 at 12:26 pm
Found the issue. It is indeed a bug with SQL Server. I found this KB article (https://support.microsoft.com/en-us/kb/3008000) that described my exact error message even though the KB article only talks about SSMS. I installed CU4 and my error went away!
April 30, 2015 at 2:02 pm
tfendt (4/30/2015)
Found the issue. It is indeed a bug with SQL Server. I found this KB article (https://support.microsoft.com/en-us/kb/3008000) that described my exact error message even though the KB article only talks about SSMS. I installed CU4 and my error went away!
Thanks for posting the link.
Hey Ed! If you're looking at this, notice that they had to fix this twice in 2012. That's one of the "retro-accidents" I was talking about.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 14, 2016 at 12:19 pm
SSIS package works fine in BIDS for loading excel file with more than 5000 rows but failed when same package runs in SQL server Agent Job under proxy account.
Main cause of this is that for large file SSIS process use C:\Users\Default location to buffer data. So Proxy account needs write access on this folder location. Package works fine in JOB too after this…:-)
-Balwant Rajput
June 22, 2018 at 2:19 pm
balwant Singh Rajput - Wednesday, September 14, 2016 12:19 PMSSIS package works fine in BIDS for loading excel file with more than 5000 rows but failed when same package runs in SQL server Agent Job under proxy account.Main cause of this is that for large file SSIS process use C:\Users\Default location to buffer data. So Proxy account needs write access on this folder location. Package works fine in JOB too after this…:-)-Balwant Rajput
Thanks Balwant - had the same issue.
Here are a couple more links that I found helpful:
- Source Excel File Causing failure in SQL Agent
- Strange Error Loading Excel Files (xlsx) using SQL Server Integration Services
It's not so much about the number of records as it is the file size and the environment you are working in.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply