Recently, I 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 it 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 workaround is a bit of pain but it works. First, you have to create a Dummy Flat File Connection String by adding it to your 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.
