How to read Folder Names in SSIS

Question

How to read Folder Names in SSIS

only4mithunc

SSCrazy Eights

Points: 8434
More actions
December 28, 2015 at 4:32 am

#310448

Hi All,
I have some folder names as dates, say 20151217 , 20151218, 20151219 etc (YYYYMMDD) and many files inside these folders.. Every day I need to delete those folders which are 3 days old ( so that all the files in it also get deleted).
I can think two options:
Option 1: Use For Each Loop Container
Then make it to traverse sub folders.. use the date attached to the file and derive YYYYMMDD and then try to delete the folder of that date...I don't think it is a good option because the folder may contain some old files as well, and time consuming if the folder has got too many files in it.
Option 2: Use For Loop.
Say @StartLoop = 20151217
@EndLoop = 20151228 ( this I can get from GETDATE() )
then
Condition I used is @StartLoop <= @EndLoop-3
Then I used @StartLoop to dynamically get the Folder path.. and then used FileSystem task inside For Loop to delete those folders.. but here the catch is if there is no folder on a particular day then it will fail ; " Invalid path ".
Is there any better option to get the folder names and I can delete the folder if it is 3 days older ?
Thanks & Regards,
MC

Viewing 6 posts - 1 through 5 (of 5 total)

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

Phil Parkin SSC Guru Points: 246697 More actions · Answer 1

Have you also considered using a script task? It might be the most elegant solution in this case.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

Phil Parkin (12/28/2015)
Have you also considered using a script task? It might be the most elegant solution in this case.

Agree.

Use a script task and save the script where you can easily find it again. My scripts for file and folder properties get re-used over and over again.

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]

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 3

Thanks for the response. Can any of you please elaborate the solution using script task?

Thanks & Regards,
MC

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 4

Essentially, this can be done using the file system IO libraries using script task either using VB.NET or C#.NET. Using the library (System.File.IO) this library exposes set of API's for file and directory manipulation. A quick search online will yield you lot of contents or if you are lucky enough a complete working code.

Raunak J

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 5

Thanks for all your response.

I have done it using For Each Loop Container , Execute SQL Task, File System task combination... I have retrieved the folder names from file names while looping through.. and using that result self (self of folder list ) I have deleted them using File System Task.

Thanks & Regards,
MC