SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to delete the oldest file in a given path. Assume some .txt file of the format File_MMDDYYYY.txt...


How to delete the oldest file in a given path. Assume some .txt file of the format File_MMDDYYYY.txt

Author
Message
SS53217
SS53217
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 23
Can someone please help me....i have tried using for each loop, script task and file system task

this is the script used to get the oldest file from a given folder


Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum


Public Sub Main()
Dim FILETOTEST As String = Dts.Variables("CurrentFileName").Value.ToString()
Dim FileDate As Date
If File.Exists(FILETOTEST) Then
FileDate = File.GetCreationTime(FILETOTEST)
End If
If FileDate < Dts.Variables("CurrentFileDateTime").Value.ToString Then
Dts.Variables("CurrentFileDateTime").Value = FileDate
Dts.Variables("OldestFileName").Value = Dts.Variables("CurrentFileName").Value.ToString()
End If

Dts.TaskResult = ScriptResults.Success

when i assign OldestFileName to File system task as source variable the error it shows

"Error 1 Validation error. File System Task File System Task: Variable "OldestFileName" is used as a source or destination and is empty. OLDEST_FILE_DELETE.dtsx 0 0"
Evil Kraig F
Evil Kraig F
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34925 Visits: 7660
SS53217 (9/4/2012)

Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum


Public Sub Main()
Dim FILETOTEST As String = Dts.Variables("CurrentFileName").Value.ToString()
Dim FileDate As Date
If File.Exists(FILETOTEST) Then
FileDate = File.GetCreationTime(FILETOTEST)
End If
If FileDate < Dts.Variables("CurrentFileDateTime").Value.ToString Then
Dts.Variables("CurrentFileDateTime").Value = FileDate
Dts.Variables("OldestFileName").Value = Dts.Variables("CurrentFileName").Value.ToString()
End If

Dts.TaskResult = ScriptResults.Success





"Error 1 Validation error. File System Task File System Task: Variable "OldestFileName" is used as a source or destination and is empty. OLDEST_FILE_DELETE.dtsx 0 0"


This has to be a code snippet, it's not looping the directory, and you're comparing a datetime from the filesystem to a string... which is hazardous at best.

Immediate answer: to deal with that error send the value in the DTSVariable into a local VB Variable and test it before usage. It'll help you track down the break if nothing else, and DTSVariable calls don't behave as direct requests internal to all functions, I've found it better to transfer to local variables in almost all cases.

Longer answer: There's a LOT of wrapper to this that we're not seeing and we won't be able to troubleshoot incomplete code for you. There's cut and paste code using filesystem in vb that will archive everything past a certain date, etc etc, that can be modified easier if you take the time to learn all of what they're doing. Usually they're error trapped too to make sure accidents can't happen with live files. I'd recommend a bit of googling and research on this.


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
SS53217
SS53217
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 23
hi Evil Kraig F,

Thanks for the reply, as I'm new to ssis could you please explain in detail or if possible provide an example pkg for the requirement
Thanks in advance
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search