|
|
|
Grasshopper
      
Group: General Forum Members
Last Login: Friday, September 07, 2012 4:54 PM
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"
|
|
|
|
|
SSCertifiable
       
Group: General Forum Members
Last Login: Today @ 5:35 PM
Points: 5,722,
Visits: 6,194
|
|
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
|
|
|
|
|
Grasshopper
      
Group: General Forum Members
Last Login: Friday, September 07, 2012 4:54 PM
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
|
|
|
|