October 1, 2014 at 12:19 pm
I have created, very carefully, a new SSIS package to import Employee data from an Excel spreadsheet file. File name is "Employee.xlsx" This same SSIS package is also designed to import data from a SECOND Excel spreadsheet file for some operational data. The name of that second spreadsheet file is: "OperationalData.xlsx" The sheet that holds the data for the Employee data is titled "Detail." The sheet that holds the data for the Operational Data is titled "Sheet1."
This SSIS package runs great from my PC using the 32 bit runtime. Just before deployment, I change that to use 64 bit runtime as the runtime on the server is 64bit. The error I get when the package is executed from the SSIS Catalog on the server is: "Opening a rowset for “Sheet1$” failed" (I know the "$" is added when used in executable code.) Of course, "Sheet1$" can be found by this very same code when run from my PC, but when executed from the Catalog of the server, "Sheet1$" can't be found.
If someone has an answer to this problem, I would surely love to get it so I can get this working.
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
October 1, 2014 at 12:29 pm
Is the spreadsheet located on a local drive on the server?
If not, can you try putting the spreadsheet there and seeing whether it works? If it does, this is likely to be a file access/permissions problem.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 1, 2014 at 12:37 pm
The file is on the server. I'm wondering about the permissions you stated because the package seems to at least be able to find the file, it seems it is only the "Sheet1$" that it is complaining about. I do know what the error looks like when the permissions keeps the package from even finding the Excel file in the first place.
Thank you for your response! I will ask the Server guys about the permissions.
/Edit/
I just tested running from the server again with DelayValidation property of the OLE DB Source adapter set to True. It still fails. {~;
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
October 1, 2014 at 1:13 pm
Are you using two separate data flows to do the imports? (Rather than a fancy Foreach container.)
How are you setting the run-time values of things like file name and file path - are you using some sort of external config? Or is it all hard-coded in the package?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 1, 2014 at 1:52 pm
Everything is hard coded, and as simple as possible. I have 5 steps. Empty the SQL table (OperationalData), import from Excel (three step process, The source descriptor, the data conversion, and the Destination descriptor), then empty the Employee SQL table, import from the second Excel spreadsheet using the same (conceptually) three steps (but of course they are their own 3 steps, nothing shared except the concept), and lastly, execute a stored procedure that runs 5 other stored procedures where all the "work" is done on this newly imported data. I'm thinking very straight forward, easy, and it still refuses to work.
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
October 2, 2014 at 12:38 am
Vic Rauch-303403 (10/1/2014)
Everything is hard coded, and as simple as possible. I have 5 steps. Empty the SQL table (OperationalData), import from Excel (three step process, The source descriptor, the data conversion, and the Destination descriptor), then empty the Employee SQL table, import from the second Excel spreadsheet using the same (conceptually) three steps (but of course they are their own 3 steps, nothing shared except the concept), and lastly, execute a stored procedure that runs 5 other stored procedures where all the "work" is done on this newly imported data. I'm thinking very straight forward, easy, and it still refuses to work.
It's not exactly best practice, but are you able to RDP into the server and then run the package there manually in SSDT?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 2, 2014 at 12:48 am
I seriously doubt it. We are on a virtual server that is controlled by IT. We are a small (40 or so people) section of a large (over 20,000) company. So I'm at the mercy of them, and the two I have been able to talk to say they know basically nothing about SSIS. Therefore, I'm using this forum. As I said earlier, I can run this package flawlessly from my desktop using Visual Studio. So I know that I have all the necessary permissions. It was suggested to me to create a new Excel spreadsheet with the same name and just a few rows of records (5?) and use the name "Sheet1" then see if my package will read Sheet1$ out of this fresh, brand new spreadsheet. I plan on trying that first thing in the morning, 8 hours from now. I'll post here what happens from that test.
Thanks for your suggestions!
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
October 2, 2014 at 12:53 am
No problem and good luck. 'Remote' troubleshooting is never fun. You need to get yourself a good contact in IT who has the power to work through this with you!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 2, 2014 at 1:02 am
You need to get yourself a good contact in IT who has the power to work through this with you!
I agree with that 100% Problem is I'm a contractor and working for a "step-child" type of department. No one in this company is to have their "own" IT people, and that is just what our little group of 3 people are. We have Access databases (2 very good sized ones) with SQL as the backend database and are saving the company big bucks is why they are able to get away with having their "own" IT department. But it does look like we are going to get this escalated pretty soon. The bosses are getting tired, as am I, of getting so close, but still no complete success.
Again, thanks for your help, it has been very encouraging!
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
October 2, 2014 at 1:10 am
Vic Rauch-303403 (10/2/2014)
You need to get yourself a good contact in IT who has the power to work through this with you!
I agree with that 100% Problem is I'm a contractor and working for a "step-child" type of department. No one in this company is to have their "own" IT people, and that is just what our little group of 3 people are. We have Access databases (2 very good sized ones) with SQL as the backend database and are saving the company big bucks is why they are able to get away with having their "own" IT department. But it does look like we are going to get this escalated pretty soon. The bosses are getting tired, as am I, of getting so close, but still no complete success.
Again, thanks for your help, it has been very encouraging!
You've probably already considered this, but just in case you haven't ...
All of your (technical) problems would almost certainly evaporate if you were to switch your source file to pipe-delimited text files rather than using Excel. If that's an option, I recommend it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 2, 2014 at 2:46 am
Vic Rauch-303403 (10/1/2014)
I have created, very carefully, a new SSIS package to import Employee data from an Excel spreadsheet file. File name is "Employee.xlsx" This same SSIS package is also designed to import data from a SECOND Excel spreadsheet file for some operational data. The name of that second spreadsheet file is: "OperationalData.xlsx" The sheet that holds the data for the Employee data is titled "Detail." The sheet that holds the data for the Operational Data is titled "Sheet1."This SSIS package runs great from my PC using the 32 bit runtime. Just before deployment, I change that to use 64 bit runtime as the runtime on the server is 64bit. The error I get when the package is executed from the SSIS Catalog on the server is: "Opening a rowset for “Sheet1$” failed" (I know the "$" is added when used in executable code.) Of course, "Sheet1$" can be found by this very same code when run from my PC, but when executed from the Catalog of the server, "Sheet1$" can't be found.
If someone has an answer to this problem, I would surely love to get it so I can get this working.
The Run64BitRuntime setting is only used in Visual Studio. It has no impact whatsoever on the package in production.
Does the account executing the package have access (and permission) to the Excel file?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 2, 2014 at 3:20 am
Does the account executing the package have access (and permission) to the Excel file?
If it's on a local drive on the server, I would not expect it to be a permissions problem. Or perhaps you know otherwise?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 2, 2014 at 3:22 am
Phil Parkin (10/2/2014)
Does the account executing the package have access (and permission) to the Excel file?
If it's on a local drive on the server, I would not expect it to be a permissions problem. Or perhaps you know otherwise?
Does the SQL Server Agent account have permission to all local folders?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 2, 2014 at 3:41 am
Koen Verbeeck (10/2/2014)
Phil Parkin (10/2/2014)
Does the account executing the package have access (and permission) to the Excel file?
If it's on a local drive on the server, I would not expect it to be a permissions problem. Or perhaps you know otherwise?
Does the SQL Server Agent account have permission to all local folders?
Exactly what I was going to ask!
October 2, 2014 at 4:03 am
Lempster (10/2/2014)
Koen Verbeeck (10/2/2014)
Phil Parkin (10/2/2014)
Does the account executing the package have access (and permission) to the Excel file?
If it's on a local drive on the server, I would not expect it to be a permissions problem. Or perhaps you know otherwise?
Does the SQL Server Agent account have permission to all local folders?
Exactly what I was going to ask!
Ha! One step ahead of you 😛
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply