File System Task is Giving Access denied Error

Question

Post reply

File System Task is Giving Access denied Error

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
August 31, 2010 at 9:03 am

#239706

Hello Everyone
I am creating a package that will delete all files from a directory, and then copy new files into that same directory.
I am having an issue with the delete task. I have tried both a Script Task and a File System Task. Both are throwing an Access Denied error. I have given the Service Account that SSIS uses, Full Permissions to that directory. I can copy file into the directory fine, I just cannot Delete anything from there.
Does anyone have an idea of what I may check or look into? Of does anyone have a better way of doing this?
For the time being, I have set up a SQL job using xp_cmdshell (I know, I really hated to turn that feature on to perform a delete. That is usually turned off always!!)that does go out and and delete all the file before the copy. That works fine. What am I missing when using the SSIS package?
Thank you in advance
Andrew SQLDBA

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 1

2 questions..

1. Are you Admin on the said machine

2. Is that drive where the folder is placed a local drive or a Network share drive?

Raunak J

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 2

If you are executing the SSIS package from the SQL Agent, then it is the SQL Agent service account that will need the proper permissions to the directory, not the Integration Services account.

Alternatively, you could set up a proxy account with the correct permissions and run as that account in a job.

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 3

Yes to both

I have given the service account full access permissions to the directory. I can copy into that directory, but I cannot delete from. The directory is on the same box, on the same spindle.

I am trying to run this from within VS BIDS to verify that everything works. It is using an XML config file to fill the variables that the C# code used to perform the delete task. Everything is working perfectly, all but the delete method.

I honestly do not believe this is a permissions issue.

This is really odd.

Andrew SQLDBA

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 4

since you are able to add new content...it shd not be the issue with the credentials...have u implemented any scripts that involves IO operation

Raunak J

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 5

Yes, that is all that is in the Script Task.

Delete files from a directory.

Andrew SQLDBA

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 6

Is it possible that something is locking the files that you are trying to delete?

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 7

No, I am the only one using this box. I just copied the files there. The FileInfo class does not lock the files in any way.

I am using a File System Task after I disabled the Script Task. It does nothing other than delete the files from the directory that is listed in the xml config.

Andrew SQLDBA

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 8

have you closed and disposed the streams, if any

Raunak J

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 9

Yes, currently the delete task is now the only task in the package.

I have scheduled the package, and it is still give the same error.

I am going to move the package to another box, to see if there is a policy set on the workstations here in this office.

Thanks

Andrew SQLDBA

slash.young SSC Enthusiast Points: 159 More actions · Answer 10

I had the same issue and was getting stressed out by this. I mistakenly set my File System Task operation to 'Delete File', which to a normal syntax of a programmer would make sense, theres a loop delete one file per loop.

What actually needs to happen is to remove the ForEach loop all together, and set the File System Task operation to 'Delete Directory Content'. This is all assuming you are using the ForEach as I was. There is a way to do it, if you wanted to keep your foreach, simple refer to :

http://consultingblogs.emc.com/jamiethomson/archive/2008/04/17/ssis-deleting-a-group-of-files.aspx

lamtu007 Newbie Points: 2 More actions · Answer 11

Hi,

Suggest you to check the attributes of the file (which you are trying to delete). If the file attributes is ReadOnly, you should use the File system task to change the attribute (ReadOnly=False) before try to delete the file.