x

SQL agent job not deleting a file via ssis

Dear All,

I have a SSIS package that deletes older log files from a location. The logic is based on modified date. It works fine when it is executed manually but when it is scheduled via a SQL Server Agent job, it doesn't deletes the files at specified location.

The job terminates successfully and there are no errors displayed.

Any help in this regard would be very helpful.

Regards, Rafayz

more ▼

asked Jun 12, 2012 at 11:15 AM in Default

rumsmaster123 gravatar image

rumsmaster123
10 2 4 4

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

2 answers: sort voted first
You need to make sure that the account that SQL Server Agent is using has rights to be able to delete the files. When you execute manually it is using your credentials. When executed by SQL Server Agent it uses the service account of SQL Server Agent.
more ▼

answered Jun 12, 2012 at 11:31 AM

Tim gravatar image

Tim
36.4k 38 41 139

Tim can you kindly tell me how to change sql server agent accounts setting?? I am new to SQL server agent.

Help in this regard will be appreciated.

Regards,

Rafayz
Jun 14, 2012 at 11:50 AM rumsmaster123

You can find information about account under which the agent is running in the SQL Server Configuration Manager. Then you can grant that account appropriate rights. Also you have possibility to change account to other one if necessary.

If the files are located on remote machine, then agent has to be running under account which has access to that remote machine. In most cases a domain account with appropriate rights.
Jun 14, 2012 at 07:13 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

One of the options As @Tim mentioned is grant the service account under which the agent is running appropriate rights to be able to delete those files.

The other option is to create a proxy account with appropriate rights and specify the proxy account in the job. So the Agent executes the SSIS under this specific proxy account with all the necessary rights for the delete operation.
more ▼

answered Jun 12, 2012 at 11:54 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(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
x593
x91

asked: Jun 12, 2012 at 11:15 AM

Seen: 1379 times

Last Updated: Jun 14, 2012 at 07:13 PM