I have a pretty basic SSIS package that is attempting to read a file hosted on a share, and import its contents to a database table. The package runs fine when I run it manually within SSIS. However, when I set up a SQL Agent job and attempt to execute it, I get the following error:
Executed as user:
DOMAIN\UserName. Microsoft
(R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 64-bit
Copyright (C) Microsoft Corp
1984-2005. All rights reserved.
Started: 10:14:17 AM Error:
2010-05-03 10:14:17.75 Code:
0xC001401E Source:
DataImport Connection
manager "Data File Local"
Description: The file name
"\10.1.1.159\llpf\datafile.dat"
specified in the connection was not
valid. End Error Error: 2010-05-03
10:14:17.75 Code: 0xC001401D
Source: DataAnimalImport
Description: Connection "Data File
Local" failed validation. End Error
DTExec: The package execution returned
DTSER_FAILURE (1). Started: 10:14:17
AM Finished: 10:14:17 AM Elapsed:
0.594 seconds. The package execution failed. The step failed.
This leads me to believe it's a permissions issue, but every attempt I've made to fix it has failed.
What I've tried so far:
Run as the SQL Agent account (DOMAIN\SqlAgent) - yields same error. DOMAIN\SqlAgent has "Full Control" permissions on both the share and the uploaded file.
Set up a proxy account with a different account's credentials (DOMAIN\Account) - yields same error. Like above, "Full Control" permissions were given over the share to that account.
Gave "Everyone" full control permissions over the share (temporarily!). Yielded same error.
Manually copied the file to a local path and tested with the SQL Agent account. Worked properly.
Added an ActiveX script task that would first copy the remotely hosted file to a local path, and then have the DTS package reference the local file. Gave a completely nondescriptive (even by SSIS standards) error when trying to run the script.
Set up a proxy account, using my own personal account's credentials - worked correctly. However, this is not an acceptable solution as there are password policies in place on my account, as well as being a bad practice to set things up this way in general.
Any ideas? I'm still convinced it's a permissions issue. However, what I've read from various searches more or less says giving the executing account permissions on the share should work. However, this is not the case here (unless I'm missing something obscure when I'm setting up permissions on the share).
