Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

Deleting Files using File System Task in SSIS

, 16 May 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
Deleting Files using File System Task in SSIS

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.


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Raymund Macaalay
Technical Lead
New Zealand New Zealand
http://nz.linkedin.com/in/macaalay
http://macaalay.com/
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141223.1 | Last Updated 17 May 2010
Article Copyright 2010 by Raymund Macaalay
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid