August 22, 2016 at 2:35 am
Hi all
I've got a script task that should loop while searching for a specific until a specific time and then fail.
I've set the stop time to 11PM but the task fails at 11AM (i.e. 12 hours early).
I've tried various ways of casting the time (including the AM/PM marker, making it 24hour, etc) but it still fails early.
The script is written in Visual Basic 2012 if that helps.
The script is here:-
Public Sub Main()
' Gets the path and filename of the final file that is sent by TPP/SystmOne and checks that it exists.
' If the file does not exist then this routine will continue to loop
Dim strFileName As String, strFilePath As String, strFilePathAndName As String
Dim delay As Integer, dtStopTime As Date
Dts.TaskResult = ScriptResults.Failure 'Set this to fail and only have a success if the file is found
'set variables
strFilePath = Dts.Variables("User::DownloadPath").Value.ToString()
strFileName = Dts.Variables("User::FinalFiletoLookFor").Value.ToString()
delay = Dts.Variables("User::LoopDelay").Value
strFilePathAndName = strFilePath & "\" & strFileName & ".csv"
dtStopTime = Dts.Variables("User::StopTime").Value
If System.IO.File.Exists(strFilePathAndName) Then 'File exists, carry on with load
Dts.TaskResult = ScriptResults.Success
Exit Sub
End If
While Now.ToString("hh:mm:ss tt") < dtStopTime.ToString("hh':'mm':'ss tt")
'dtStopTime.ToString("HH':'mm':'ss")
If System.IO.File.Exists(strFilePathAndName) Then 'File exists, carry on with load
Dts.TaskResult = ScriptResults.Success
Exit Sub
Else
System.Threading.Thread.Sleep(delay * 1000)
End If
End While
End Sub
Most of the above code is driven by variables which are as follows:-
strFileName - the file to look for (including extension)
strFilePath - the path to find the file (full path including server name)
strFilePathAndName - this is a concatenation of the two above
dtStopTime - this is a datetime in the variable (currently set to a 23/02/2016 23:00 but we're only trying to use the time part)
At the moment, the script is using the AM/PM marker but I've also tried this:-
ToString("HH':'mm':'ss")
on both to try 24hour format.
If I step through the script line-by-line it all seems to work (i.e. it picks up the right time format) but when I deploy it the time format is ignored.
Any help on this would be greatly appreciated.
August 22, 2016 at 7:37 pm
I know squat about VB anymore but it seems to me that the following line would be a problem ...
While Now.ToString("hh:mm:ss tt") < dtStopTime.ToString("hh':'mm':'ss tt")
... because the single quotes are going to produce a different format for "dtStopTime" and the way "now" is being formatted.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 22, 2016 at 7:51 pm
p.s. It's also not likely that you need to check for the file every second if you're willing to wait until 11PM for it. Why not just write a job that looks for the file every 10 to 60 minutes (or so) without making a trip to VB land? For that matter, if you're willing to wait for 11PM for the file to show up, why not schedule a single job at 11PM?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 23, 2016 at 1:44 am
Hi Jeff
I've stepped through the code as it is and, in the watch window, the times are the correct format in both cases.
It appears to be only when the package is deployed that it loses the ability to differentiate between 11AM and 11PM.
Back to the drawing board methinks.......
Also, we need to load the files as early as possible which is why it checks every minute. The final file normally appears around 02:00 but can be quite a bit later than that (especially at weekends for some bizarre reason that I can't get to the bottom of).
August 23, 2016 at 4:11 am
Jeff Moden (8/22/2016)
I know squat about VB anymore but it seems to me that the following line would be a problem ...
While Now.ToString("hh:mm:ss tt") < dtStopTime.ToString("hh':'mm':'ss tt")
... because the single quotes are going to produce a different format for "dtStopTime" and the way "now" is being formatted.
Well Its is a issue but a different one, if you want to compare Date/Time, it should be in its proper datatype. How to compare a DateTime to a string.
There are many ways you can calculate the time. Here are few which can be used.
August 23, 2016 at 4:22 am
richardmgreen1 (8/23/2016)
Hi JeffI've stepped through the code as it is and, in the watch window, the times are the correct format in both cases.
It appears to be only when the package is deployed that it loses the ability to differentiate between 11AM and 11PM.
Back to the drawing board methinks.......
Also, we need to load the files as early as possible which is why it checks every minute. The final file normally appears around 02:00 but can be quite a bit later than that (especially at weekends for some bizarre reason that I can't get to the bottom of).
If your logic do come true, There is another issue, Let say a file is a big one like in 1 GB, can its being copied to the FilePath you have configured and at the same time your code is also accessing it. SystemIO will return an exception on this for more details check the following Is there a way to check if a file is in use?
August 23, 2016 at 4:37 am
There's no issue with the file still being in use while trying to copy it as the filename ends with ****tmp.csv while it's still being written. When the "tmp" section is removed, the file is complete.
I'll have a look at the COMPARE method and see if that gets around my issue and let you know.
::edit::
Just noticed it needs a full date/time, whereas I only want to compare the times.
Do you know if this will still work?
August 23, 2016 at 5:03 am
richardmgreen1 (8/23/2016)
There's no issue with the file still being in use while trying to copy it as the filename ends with ****tmp.csv while it's still being written. When the "tmp" section is removed, the file is complete.I'll have a look at the COMPARE method and see if that gets around my issue and let you know.
::edit::
Just noticed it needs a full date/time, whereas I only want to compare the times.
Do you know if this will still work?
Yup it will work, you have to build your known Date portion for this.
Something like this :
DateTime dt11AM = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 11, 0, 0);
if (DateTime.Now < dt11AM)
Console.WriteLine("It's not 11 AM yet");
else
Console.WriteLine("It's past 11 AM");
August 24, 2016 at 5:42 am
Solved it!!
I cheated slightly and just used the hour portion of both Now and dtStopTime as my comparitors.
It seems to be working the morning (the final file hasn't been downloaded yet and the import job is still running).
August 24, 2016 at 6:08 am
Congrats. Thanks for your feedback.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply