Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SSIS File Exists Over Range of Time Expand / Collapse
Author
Message
Posted Tuesday, March 31, 2009 12:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 14, 2009 1:53 PM
Points: 4, Visits: 12
Comments posted to this topic are about the item SSIS File Exists Over Range of Time
Post #686750
Posted Tuesday, March 31, 2009 7:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 29, 2011 10:13 AM
Points: 5, Visits: 57
Great, simple example.
There's a "FileWatcher" task at http://www.sqlis.com that is available for free.
I have used it (SQL 2005), and love it (for what it does ).
This example, however, gives me some additional coding practices, which I continue to consume as I browse this excellent site.
Post #687034
Posted Tuesday, March 31, 2009 8:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 27, 2009 1:35 PM
Points: 5, Visits: 20
It always concerns me when I see an endless loop used as a timing device. It'd seem that having a number of packages running on a server, each using an endless loop as a file watcher, might be detrimental to performance.

It's very easy to add a "Sleep" statement in the body of the loop to put the thread to sleep for a few seconds or minutes:

System.Threading.Thread.Sleep(milliseconds)

I've done the file watcher thing by putting the thread to sleep for 60000 milliseconds (one minute), and counting how many minutes have transpired before failing the task:
Dim i As int
While True
If System.IO.File.Exists(Dts.Variables("FlatFileConnection").Value.ToString)Then
Exit While
Else
System.Threading.Thread.Sleep(60000)
i += 1
'Exit after 240 minutes (4 hours):
If i > 240 Then
Exit While
End If
End If
End While

This is much simpler and more efficient code than using a DateTime variable and comparing it's value to Now(). The way the code in this article is written, Now() is called millions and millions of times in the body of the loop, while in the revised loop, the comparison is performed only once a minute, and, at most, 240 times. The revised loop achieves the same objective while requiring a lot fewer CPU cycles.
Post #687087
Posted Tuesday, March 31, 2009 9:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 1,333, Visits: 1,701
There's also a tweak I would make to eliminate a (very small) chance of failure if the file is put out there literally at the last second. Change

...
If Now() >= NewDT Then
Dts.TaskResult = ScriptResults.Failure
Else
Dts.TaskResult = ScriptResults.Success
End If
...

to

...
If System.IO.File.Exists(Dts.Variables("FlatFileConnection").Value.ToString) Then
Dts.TaskResult = ScriptResults.Success
Else
Dts.TaskResult = ScriptResults.Failure
End If
...

This way, the success is dependant on the real thing you want to check (i.e, the existence of the file). Otherwise, if the file arrives at the last second or some other performance issue causes the task to run slowly, you may get a failure even with the file out there.
Post #687132
Posted Tuesday, March 31, 2009 9:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 14, 2009 1:53 PM
Points: 4, Visits: 12
Very good points and I like the ideas. Thanks for the input. I will try that out and that should be a bigger improvement witht he items I handle.
Post #687133
Posted Tuesday, March 31, 2009 9:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 14, 2009 1:53 PM
Points: 4, Visits: 12
sknox (3/31/2009)
There's also a tweak I would make to eliminate a (very small) chance of failure if the file is put out there literally at the last second. Change

...
If Now() >= NewDT Then
Dts.TaskResult = ScriptResults.Failure
Else
Dts.TaskResult = ScriptResults.Success
End If
...

to

...
If System.IO.File.Exists(Dts.Variables("FlatFileConnection").Value.ToString) Then
Dts.TaskResult = ScriptResults.Success
Else
Dts.TaskResult = ScriptResults.Failure
End If
...

This way, the success is dependant on the real thing you want to check (i.e, the existence of the file). Otherwise, if the file arrives at the last second or some other performance issue causes the task to run slowly, you may get a failure even with the file out there.


The only thing is that I dont want it to keep checking past a certain amount of time, so in your case wouldnt the job keep going until the file is found?
Post #687137
Posted Tuesday, March 31, 2009 9:08 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:29 PM
Points: 1,483, Visits: 1,031
Does File.Exists Evaluate to true even if the file is still copying?

If a file is large it can take several minutes to copy, and if it picks it up too soon The file will not be read properly.
The Filewatcher task noted above does a good job of managing this for you.
Post #687139
Posted Tuesday, March 31, 2009 9:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 27, 2009 1:35 PM
Points: 5, Visits: 20
That's a great question. I'm really not sure if the file "exists" while it's being copied to a file server. Our packages work with a lot of really big (>100 megs) files, and FTP'ing them from remote sites can take several minutes.

It's require a bit more complicated code, but it should be possible to write the code in such a way that the file watcher monitors the file for a minute or so to see if the file's size is changing (I'm assuming a large file "grows" as it's downloaded).

The System.IO.FileInfo has a Length property that returns the size (in bytes) of a file. Once the file is 'there', get the size, wait a minute or two, and compare the size with the initial value.

The System.IO.File class that Silver uses in the article has a number of methods that could also be useful: GetCreationTime, GetLastAccessTime, and GetLastWriteTime. I'm not sure which of these change as a file is being written, but I'd bet at least one of them ought to be useful.

Once it (meaning: size, time, etc.) stops changing, it should be safe to assume the file is stable and ready for processing.
Post #687155
Posted Tuesday, March 31, 2009 9:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 27, 2009 1:35 PM
Points: 5, Visits: 20
sknox (3/31/2009)
There's also a tweak I would make .....
...
If System.IO.File.Exists(Dts.Variables("FlatFileConnection").Value.ToString) Then
Dts.TaskResult = ScriptResults.Success
Else
Dts.TaskResult = ScriptResults.Failure
End If
...



That's a real nice upgrade to any package. Seriously, most SSIS package run unattended, and anything we (as developers) can do to enhance the reliability of our packages -- and, reduce support calls at the same time! -- is a real good idea! Sometimes a line or two of addition code can eliminate a lot of trouble later on!

Post #687159
Posted Tuesday, March 31, 2009 12:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:02 AM
Points: 115, Visits: 113
I think the comments above, regarding the sleep command is required, and so is the file check logic after the loop.

Suggestion 3.
The script putting the file onto the server should copy to a separate folder and after the copy completes, should use a 'rename' command run on the target server to 'move' the file into the target folder after file copy.

It is unlikely for a Rename command to be swapped out during execution, when ssis goes to pickup the file it will be completely written.
Post #687341
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse