SSIS package fails when running as a Job
Hi
I have an SSIS package that is extremely simple...
1 - A Script task that connect to a website (using webclient) to download a csv file
2 - A data flow task that takes that file into a SQL staging table.
When running via BIDs and SSIS (even the Execute Package utility work fine) it works a treat, but when i run it via a job it failed with the error below.
Started: 15:19:35
Error: 2009-11-25 15:19:36.28
Code: 0x00000000
Source: Download File
Description: An exception occurred during a WebClient request.
End Error
Error: 2009-11-25 15:19:36.28
Code: 0x00000004
Source: Download File
Description: The Script returned a failure result.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 15:19:35
Finished: 15:19:36
Elapsed: 0.796 seconds
This Agent Service is setup as a local admin of the server... is there something missing when I setup the job? Could it be a IE proxy thing???
Also... If i create a batch file that calls the package, it works... But if I call the batch file from xp_CMDShell it fails with the same error.
Cheers
JL
Best Answer
Make a credential that uses a domain user account (in SSMS, Database Engine, Security). Then go into SQL Agent and make a proxy that uses this credential, allowing the proxy to be used for SSIS tasks. Then try running the job step as this proxy.
My guess is that being Local Admin, it can't get out of the box. A low-privilege domain account, with just enough permissions to do what the package does should be enough.
