October 27, 2010 at 8:57 pm
Alright, I've been banging my head on the wall on google and in the forums trying to find a solution. Every solution I find looks for files. Well and good, but I want to strip down and clear out folders.
Here's the scenario. I've got a common dropoff folder location. I have a number of load processes that go through the files here during the overnight, creates the new archive directory, and drops off all the files as they're being used in their foreach file loops. Simple enough.
Now, at the tail of this, I want to clean up my trash. I want to remove every folder that's 10+ days old.
In theory, simple enough. The folders are all named yyyymmdd. Simple enough to compare... if I could figure out a way to suck out the directory list.
Of note: I do NOT have xp_cmdshell access, so I can't feed a DIR command to create a temp table for a Loop For list.
I know how to do this in DTS and VBScript. I can't figure it out in SSIS. Links, generic directions, actual code... any of the above would be appreciated. My desk would appreciate my head no longer finding the need to impact on it.
I've thought about doing a 'foreach file' loop, pulling out the fully qualified name, stripping it down to the archive folder's name, determining validity, and then deleting the folder if it is outside the 10 day window. That's... ugly, at best. I'm hoping there's a better way.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 27, 2010 at 9:52 pm
I know how to do this in DTS and VBScript. I can't figure it out in SSIS. Links, generic directions, actual code... any of the above would be appreciated. My desk would appreciate my head no longer finding the need to impact on it.
In the Archival Deletion Process
Create a ST and using the .NET namespace for IO (System.IO) embed the logic for Directory deletion specifying with changing source path which will be retrieved from FEL loop variable.
Though I would also suggest you add necessary exception handling
For more reference, please follow here 🙂
Raunak J
October 27, 2010 at 10:07 pm
Awesome link, thanks, exactly what I was looking for. With a little puzzlement, I've figured out how to get it to do what I want (I think).
However, it's seriously not liking the 20091231 for a cdate, which I thought was an ISO standard. Should I just do some string manipulations here or is there an easy way to get it to behave?
Also, for future reference, my code in the Script task:
Dim di As New DirectoryInfo(CStr(Dts.Variables("ArchiveDirectory").Value))
Dim dis As DirectoryInfo
Dim str As String
For Each dis In di.GetDirectories
str = dis.Name
If CDate(dis.Name) < DateAdd(DateInterval.Day, -10, Today) Then
dis.Delete()
End If
Next
EDIT: Is there a STUFF() equivalent in VB? (I'm browsing the BOL now...)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 27, 2010 at 10:21 pm
There, found what I was looking for. Thank you for that url Raunak, was the missing link for me. 🙂
Final code (functional and tested):
Dim di As New DirectoryInfo(CStr(Dts.Variables("ArchiveDirectory").Value))
Dim dis As DirectoryInfo
Dim str As String
For Each dis In di.GetDirectories
str = dis.Name
str = str.Insert(4, "-")
str = str.Insert(7, "-")
'MsgBox(str)
If CDate(str) < DateAdd(DateInterval.Day, -10, Today) Then
dis.Delete(True) ' The TRUE is for recursive, it empties out the directory along with deleting it.
End If
Next
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 27, 2010 at 10:23 pm
It's always nice to hear back.
Always remember to make your .NET scripts fool proof specially in SSIS as alot depends upon them even if the case is to just add or divide two numbers. You might want to implement a solid exception handling in event of undesirable ripples.
1)What is the use of string variable you have created?
2)Why not use FullName property instead of Name?
Also, if a directory by name 20101027 (yyyymmdd) why not use the CreationTime property?
Please correct me if I am wrong!!! 🙂
Raunak J
October 27, 2010 at 10:25 pm
Ohh!! you posted a new reply..plz ignore the question raised for string variable 😀
Raunak J
October 27, 2010 at 10:41 pm
Raunak Jhawar (10/27/2010)
Also, if a directory by name 20101027 (yyyymmdd) why not use the CreationTime property?
I didn't want to use creation time because runtimes may be a bit wild. It's possible we'll skip 2/3 days and then do a massive update. The Create date would then be off. Not horribly, and it shouldn't short circuit the system, but it makes my life easier on the controls this way before I have rather... intense... sysadmins griefing me. 😉
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply