SSIS Package runs from SSIS but not from Job

0
votes

I have built an SSIS Packaage that imports data on a daily basis.

i have tested it in BIDS and it runs fine, i have deployed it to the the SSIS server and it runs fine, but when i schedule it to run as a job it Errors out. i get the errors below. i've added the srvSQLagent account to the the correct group to have access to the folder that causing the problem.. am i missing something stupid here?

===================================================================

Date 03/07/2013 14:55:06 Log Job History (Import SN2 Data)

Step ID 1 Server STX-RPT Job Name Import SN2 Data Step Name Run Import Package Duration 00:00:09 Sql Severity 0 Sql Message ID 0 Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message Executed as user: INT\srvSQLagent. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 14:55:06
Error: 2013-07-03 14:55:15.69
Code: 0xC001401E
Source: Servicenet Data Import Connection manager "Downloaded"
Description: The file name "\\FILE\Groups\ISG\England\Servicenet Data\Downloaded" specified in the connection was not valid. End Error
Error: 2013-07-03 14:55:15.69
Code: 0xC0202070
Source: Servicenet Data Import Connection manager "Downloaded"
Description: The file name property is not valid. The file name is a device or contains invalid characters. End Error
Error: 2013-07-03 14:55:15.69
Code: 0xC0029163
Source: File System Task File System Task
Description: File or directory "\\FILE\Groups\ISG\England\Servicenet Data\Downloaded" represented by connection "Downloaded" does not exist. End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 14:55:06
Finished: 14:55:15
Elapsed: 9 seconds.
The package execution failed.
The step failed.

3
sql-serverssis
Check permissions. When you run it from BIDS or from SSIS, it runs under your credentials. When you run as a job, it uses the SQLAgent user account's credentials (INT\srvSQLagent). Looks like that user can't see the file location. – Bill
And why is that file location not an absolute path? There is no guarantee AFAIK for the working directory of an SQL Job. – TToni
I have logged in as srvSQLagent and have access to the files and folders required. and its a UNC because its a network share. Mapped drives are user specific. – Adam McC

3 Answers

0
votes

The issue had arisen from using a File system task to clear a folder of all Files.

I never got to the bottom of the problem, i simply found a different method of deleting the required files.

0
votes

It's definitely folder permission issue.

Please give proper rights for everyone.

Alpesh Dhori Software Professional

0
votes

I suppose that next solution will help you:

Assume that we need to write \\serv\share\dir1..\dirN\targetDir\somefile.txt using SSIS throught SQL Agent Job and nonadmin proxy account MyDomain\TestAccount

1) MyDomain\TestAccount need read/write access to share \\serv\share

2) MyDomain\TestAccount needed at least FILE_READ_DATA permission for all folders (share,dir1,..dirN)

3) MyDomain\TestAccount needed the CHANGE rights + FILE_DELETE_CHILD permission for folder targetDir

(cacls should show this permissions as following:

DELETE
READ_CONTROL
SYNCHRONIZE
FILE_GENERIC_READ
FILE_GENERIC_WRITE
FILE_GENERIC_EXECUTE
FILE_READ_DATA
FILE_WRITE_DATA
FILE_APPEND_DATA
FILE_READ_EA
FILE_WRITE_EA
FILE_EXECUTE
FILE_DELETE_CHILD
FILE_READ_ATTRIBUTES
FILE_WRITE_ATTRIBUTES

)

Of course the full rights for targetDir are enough.

If targetDir doesn't has FILE_DELETE_CHILD permission then we see such strange behavior of flat file connection in SSIS. User MyDomain\TestAccount may doing anything with file somefile.txt (read,write,delete) using standard Windows tools. But SSIS Job can't overwrite this file and fail.