delete files using ssis

  • Hi...I want to build an SSIS package that deletes the log files whose names are like Package20081002.txt. The names of the logfiles ends with the current date. I want to maintain only 1 week log files and delete the older files. any help would be appreciated...Thank You

  • You could use a ForEach container to iterate through all of the files with a pattern: Package*.txt

    In the container put a Script task. Test the file name pattern for the date stamp. If it is old enough, set a flag to delete the file. Then use a File Sytem task to delete the file if the delete file flag is set (put the test in the precedence constraint.

    Here is a means to delete a group of files:

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

    Russel Loski, MCSE Business Intelligence, Data Platform

  • Thanks a lot....Let me try this....

  • Uh, you don't need a script task for this. This is an extremely simple operation to do. Use the ForEach Loop Container and place a File System Task inside of it. Configure the File System Task to delete the file from a variable. Use the combination of the loop container, the variable, and an expression (to get date) to delete the files.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • I think I see where you are going with this. Very clever.

    In the Precedence constraint you need an expression that parses the last 8 character into a date string and compare it against the current date. If that date piece is before one week ago, then allow the file task to delete otherwise go to the next file.

    You wouldn't need the Script task.

    Russel Loski, MCSE Business Intelligence, Data Platform

  • yeah....I tried using file system task and for each loop container....but it deletes all the files in it....may be I will try ur way....Thanks for the correction....

  • Hi...I am having problems with the variable expression...I did 'nt understand what expression to put in the variable....the file name is like pakcage20080105.txt......could you please explain me what value i should put in the expression...

  • I'm trying to make this work with just an expression and it is complicated. I would go back to using a script component to strip out the date portion of the file name.

    Russel Loski, MCSE Business Intelligence, Data Platform

  • yeah....It should work...Thank u very much...

  • I need to delete files that are older than 5 business days. Can I do it using a foreach container and a file system task or do I need to use a script component?

  • You are going to need to use a script task, if only to get the date last modified.

    Russel Loski, MCSE Business Intelligence, Data Platform

  • Ok. That's what I was thinking. I'll use a Script Component to get the max date. And then use the for each container and file task to loop thru the files and delete them.

    Thanks!

  • You could also delete the files from withing the script component.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • I was thinking that as a backup. I haven't worked with the foreach loop container so wanted to give that a try. Thanks Alvin!

    I'm following this article: http://consultingblogs.emc.com/jamiethomson/archive/2008/04/17/ssis-deleting-a-group-of-files.aspx. I don't understand why in the Variable Mappings tab I have to create a variable by the name of FileName. Are all of the file names stored in that variable?

Viewing 15 posts - 1 through 15 (of 18 total)

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