Deleting Files using File System Task in SSIS

By | May 17, 2010

I recently had noticed that deleting a file in SSIS using the File System Task is not straightforward specially when your file is not hardcoded in your package but it is declared as a variable. I just noticed it when I was creating a Foreach Loop Container using a File enumerator.


Inside that for loop is a Script Task which manipulates the file and a File System Task which now deletes when the whole process is done.

 

Initially I was using a variable to delete a file like this

But trust me you will encounter an error that will tell you that Variable “something” is used as a source or destination and is empty. I found another workaround by using expressions to come out with the filename but still I gives me some error to the likes of [File System Task] Error: An error occurred with the following error message: “The process cannot access the file ‘C:YourFile.txt’ because it is being used by another process.”.


So after a bit of playing around I found a solution the work around is a bit of pain but it works, First you have to create a Dummy Flat File Connection String by adding it to you connection manager. Give it a Name and point it to any text file with some content (so the OK button will enable and save it).

Now edit the properties of the Flat File Connection Manager, remove the Connection String and add a new expression using the connection string pointing to your File Variable which was outputted from the ForEach Loop Container

Here is how you output the variable in the For Each Loop

Now use that connection string in your File System Task, by making the Source Path Variable to false and Source Connection to the connection string you had just created.

The solution bypassed the Path Variable being True but the Connection String becomes now the dynamic variable.

[end]


6 thoughts on “Deleting Files using File System Task in SSIS

  1. James

    Thread necro!

    I came across this problem just now but thought there must be a simpler solution – and there is!

    From the Todd’s answer here:

    http://social.msdn.microsoft.com/Forums/en-IE/sqlintegrationservices/thread/0ca6d5af-3aa5-499a-8893-ef1aeada6913

    “I think that your problem is that in design time, when you’re not executing the package, your file name variable is empty. It hasn’t been set by the For Each Loop – because it’s not running yet. Since it’s empty, SSIS can’t ensure that the Move operation will succeed. Your response is “of course it can’t!” which is entirely correct. You need to tell SSIS that it shouldn’t worry about checking that Task before it actually tries to run it, because you know it will work properly. To do that, set the DelayValidation property to True on the File System Task.”

    Reply
  2. ghettostitcher

    you only get that variable error because you’re telling the FST to find the file first.
    just set the ‘delayvalidation’ to true on the file system task and then all will work fine.

    Reply
  3. Rachel

    I had the same issue and you can just set a value for the FileName variable, I just use 0, and it worked since the variable was not left empty.

    Reply
  4. ash

    what if my FST is out of for each loop ? i tried every thing and still i am getting the error “Error: Variable “FileName” is used as a source or destination and is empty.”

    Reply

Leave a Reply