opening a rowset failed

Question

opening a rowset failed

coool_sweet

SSCommitted

Points: 1892
More actions
January 21, 2018 at 10:53 am

#341837

hi
i have ssis package , loading data from excel file into sql server table.
package is working fine if I execute in SSDT
when I deploy to server and create job , i am getting error , opening a row set for page1$ failed.
I have delay validation : true, validate external data : false.
what i am doing wrong here, even I created new package for same excel sheet, getting same error.
any help?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246961 More actions · Answer 1

Have you checked the following
1) That the version of the ACE driver on the server matches what is in your connection string?
2) That the job is running in the correct bitness mode (32 or 64)?
3) That the SQL Agent user has permissions to access the file?

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

MMartin1 One Orange Chip Points: 27795 More actions · Answer 2

I would also throw in there to check that you or someone else does not have the Excel file open.

----------------------------------------------------

coool_sweet SSCommitted Points: 1892 More actions · Answer 3

hi

my file is in excel 2007 coming, in my local machine I have 2016 installed, so will file save as 2016?

my connection string is :

file is not opened anywhere, job is running on 64 bit and has permission to access this file?

Phil Parkin SSC Guru Points: 246961 More actions · Answer 4

coool_sweet - Tuesday, January 23, 2018 5:11 AM
hi
my file is in excel 2007 coming, in my local machine I have 2016 installed, so will file save as 2016?
my connection string is :
file is not opened anywhere, job is running on 64 bit and has permission to access this file?

Is the file on a network drive? If so, are you using a UNC path?

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

coool_sweet SSCommitted Points: 1892 More actions · Answer 5

coool_sweet

SSCommitted

Points: 1892

January 23, 2018 at 8:06 pm

#1977027

yes I am using shared drive path , in my parameter

MMartin1 One Orange Chip Points: 27795 More actions · Answer 6

And you did not rename of Excel sheet ,it is still Page1 ( I dont know if trailing spaces in the name would make a difference but might as well check that off too) ?

----------------------------------------------------

Phil Parkin SSC Guru Points: 246961 More actions · Answer 7

coool_sweet - Tuesday, January 23, 2018 8:06 PM
yes I am using shared drive path , in my parameter

Try a UNC path instead.

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