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


SSIS File Exists Over Range of Time


SSIS File Exists Over Range of Time

Author
Message
Hollywoodrob-1016991
Hollywoodrob-1016991
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 12
Comments posted to this topic are about the item SSIS File Exists Over Range of Time
stahmosh1
stahmosh1
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 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.
michael.groh
michael.groh
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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.
sknox
sknox
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3887 Visits: 2919
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.
Hollywoodrob-1016991
Hollywoodrob-1016991
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 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.
Hollywoodrob-1016991
Hollywoodrob-1016991
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 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?
Ray M
Ray M
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4779 Visits: 1076
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.
michael.groh
michael.groh
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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.
michael.groh
michael.groh
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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!
Truckin
Truckin
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 127
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.
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