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.