x

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

more ▼

asked Nov 25, 2009 at 12:28 PM in Default

sp_lock gravatar image

sp_lock
9.3k 25 28 31

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Nov 26, 2009 at 06:23 AM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

Rob thanks for your response... The Agent Service is setup as a domain user and that users is a local admin on the box.

Tried enabling the proxy for that user... but still getting the same error :-(
Nov 26, 2009 at 08:16 AM sp_lock
Can you make a credential so that it runs using your account? (just temporarily for testing)
Nov 26, 2009 at 09:52 PM Rob Farley
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x941
x27

asked: Nov 25, 2009 at 12:28 PM

Seen: 2897 times

Last Updated: Nov 25, 2009 at 12:42 PM