question

rumsmaster123 avatar image
rumsmaster123 asked

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
ssissql-server-2008-r2sql-agent
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
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.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
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.
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.