Click here to monitor SSC
SQLServerCentral is supported by Redgate
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
Posted Friday, May 28, 2010 6:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 9:14 AM
Points: 1, Visits: 6
File.IsReadWrite() is a better approach, IMO
Post #929624
Posted Friday, June 18, 2010 8:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 4, 2014 4:38 PM
Points: 1, Visits: 34
I have a similar situation, except the files we receive (approx 30 files) are sent more frequently. The source system is scheduled to send the files every :15 throughout the day at :00, :15, :30 and :45. The problem is, depending on the file size and load on the source server, the files can arrive anywhere in that 15 minute window.

The expectation is that we try to stay as current as possible on this data, so we're checking for and processing updates every 5 minutes. Unfortunately, about 30% of the time, we end up grabbing a file as it's being written and end up with a partial file. We copy it from our FTP/File server to our SQL server for processing. I wish the senders could do a rename on the file after it's been written, but that's not currently an option. Since FTP doesn't lock the file, the Konesan's File Watcher task doesn't do us much good as it's able to open the file at any point as the file is being written.

I'm contemplating a solution that checks the file sizes after we've robocopied the files to the SQL server to see if the file sizes match. If they do, great, if not, re-run the robocopy script and keep checking until it does.

Curious what others might do in this situation?
Post #939912
Posted Wednesday, July 22, 2015 1:20 PM



Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 5,067, Visits: 4,845
michael.groh (3/31/2009)
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:


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
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.

I created a DTS and a SSIS Package to check for the existence of a backup file, rename and move the file to an archive folder and a task to delete the files after a given date. It needs to have a timer and when it finds the file it loads it into a Table. That was years ago.

Does anyone have sample code to do this?

For better, quicker answers on T-SQL questions, click on the following...

For better answers on performance questions, click on the following...

Post #1704893
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse