run ssis package as a job

Question

run ssis package as a job

arkiboys

Hall of Fame

Points: 3958
More actions
October 6, 2011 at 9:21 am

#258499

I am running a SSIS package as a job.
It is selected to run as "sql server agent service account" in the job step properties.
But when running the job it gives the following error.
I think it is to do with file or folder permissions but not sure what to do.
Any suggestions please?
Thanks
Description: The file name "\\servername\myfolder\filename.csv" specified in the connection was not valid
And it also says:
Description: Connection "Filename_connection" failed validation.

Viewing 3 posts - 1 through 2 (of 2 total)

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

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 1

IMO you are right- this is some kind of security issue. To understand where error happens (SSIS or sql job) run SSIS package first (I mean outside job) to be sure it's OK.

Then run SSIS as a job if 1-st step completed with no errors.

arkiboys Hall of Fame Points: 3958 More actions · Answer 2

I did a test to confirm the permission issue.

1- In BI the ssis package runs fine and access the network file which points to a Dev server...

2- Set up a job for this ssis package and works fine.

3- In BI changed the path to point to the production server and still the package runs fine and accesses the file on the production server.

3- setup a job for the package which points to the production server, BUT it gives the error as mentioned previously.

The path I have setup for the ssis package to access is \\servername\myfolder\filename.csv

Does this mean that in the production folder ie. \\productionServername\myfolder" I have to give read rights to the localSystem user (Which is the user that runs the sql server agent(MSSQLSERVER)?

Thanks