SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Hollywoodrob / SSIS File Exists Over Range of Time / Latest Posts

RE: SSIS File Exists Over Range of Time

Bob Weston — Fri, 18 Jun 2010 20:11:30 GMT

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?

RE: SSIS File Exists Over Range of Time

pranavmehta1978 — Fri, 28 May 2010 06:41:27 GMT

File.IsReadWrite() is a better approach, IMO

RE: SSIS File Exists Over Range of Time

David le Quesne — Thu, 18 Jun 2009 10:09:52 GMT

I believe you can use the FileInfo.OpenWrite() method to check if the file is avaialbe for opening to write (assuming that you will not be able to if something else is writing to it) Dim oLoadFile As FileInfo = New FileInfo(cFilePath) Dim lComplete As Boolean Complete = False... 'Loop until the file is openable (e.g. FTP or copy complete) or iTimeOut is reached While lComplete = False lComplete = True Try oLoadFile.OpenWrite() Catch lComplete = False System.Threading.Thread.Sleep(iCycleTime) End If i += iCycleTime If i > iTimeOut Then Exit While End Try End While...etc

RE: SSIS File Exists Over Range of Time

adam.fousek — Wed, 15 Apr 2009 14:57:39 GMT

Great article! This is very useful including the additional tweaks other users added. These steps are working great for a package I created.

RE: SSIS File Exists Over Range of Time

clou-951938 — Thu, 09 Apr 2009 14:05:39 GMT

Yes, you are right. The Retry Attempts and Retry Interval option from agent job relys on the failure of package execution and it will record every failure of retry in log. So if the source file is existed but is still in the process of being written, the uploading package will be failed anyway. That just takes the step of the job waiting for a next retry and that's it. I think the file size is quite unreliable to be taken as judgement. 1K means an empty file (it's ok to me. that means no new record today), but how big the size means good file and will that be accurate? Actually, I developed a custom task component to check the file existence (similar code but in C##). It can be used in any package that I want to check the file existence. All I need to do is to pass the full path file name as the parameter to that component. That works prety well for me.

RE: SSIS File Exists Over Range of Time

Paul Grubb — Tue, 07 Apr 2009 09:41:49 GMT

Interesting view on building a "File watch" process. I have built similar solutions but I tend not to use VB to control the delay as I recall the CPU usage shoots up.Instead, I use the VB purely to check for the file and return true or false back to the package/ populate a variable. I also use this inside a "for loop container" and include a SQL task to process the delay (does not hit the CPU).The properties on the "for loop container" are then configured to increment up to a given point, i.e. n times the delay in the SQL Task and include checks on the EvalExpresion to drop out when the file is found (previously populated variable =true) or when the total delay has been exceeded.The work flows continuing from the container can then also check the previously populated variable and act accordingly. Simple modifications to the VB to use a variable to hold the file to watch, this package can be called from a parent package, and collecting parent variables, allows the same child package to be called multiple times, e.g. waiting for n number of files to be available.I am sure there are another dozen ways to achieve the same goal.

RE: SSIS File Exists Over Range of Time

Ray M — Wed, 01 Apr 2009 15:05:02 GMT

Certainly that works if the file does not show up within the allocated # of intervals, but what if the file is not the expected size?Frequently we get files that are not correct.Our tell tale sign that something has gone wrong at the processor is the file is 1k in size, no where near the 15 mb the file usually contains.So in addition to the file existing, I check the filesize, I set the lowest value to to the smallest file we have received in the last year, (Less a few mb). Sure it May send a false notification once in while, but its good to understand the when files are out of boundry if you will.But for simplicity that surely works.Ultimately I wouldn't want to log "Failure" for each retry simply because the file hasn't gotten there yet. I'd rather handle the exception so it exits gracefully in an expected situation.

RE: SSIS File Exists Over Range of Time

clou-951938 — Wed, 01 Apr 2009 08:49:36 GMT

This is great, but I think there is another solution may be even simpler and with no coding required. I have a set of csv files need to be uploaded to our production database every week day. I create a set of packages to load these files respectively and run these packages in a scheduled SQL Server Agent job step by step. At the first step, the job will run the first package to load the first csv file on schedule, on Job Properties Advanced page, I set the Retry Attempts and Retry Interval options to cover an expected range of time. For example, you can set Retry Attempts to 4 and Retry Interval 30 minutes to cover two hours. So, if the csv file is not at the location on schedule and the first step of the scheduled job will be hold and continue retry 4 times within the designed two hours.Charley lou, MCSDDBA GMP Securities L.P.

RE: SSIS File Exists Over Range of Time

michael.groh — Tue, 31 Mar 2009 14:41:40 GMT

[quote][b]V-Man (3/31/2009)[/b][hr]... guess my overall statement is be sure to consider the transfer latency SOMEWHERE in your architecture. :-)[/quote]For sure! The good news in all of this is that the .Net base classes provide the tools needed to perform these validations. At work we've had numerous cases where the original developer used only small test cases, and their packages failed after weeks or months of running because of those "transfer latencies" that are bound to occur eventually. And, very often these failures look like FTP or network or file server issues, and not something as simple as overly long file transfer times.Good discussion on this short article. I've enjoyed reading input from others who've had to deal with stuff like this.

RE: SSIS File Exists Over Range of Time

V-Man — Tue, 31 Mar 2009 14:31:38 GMT

Agreed. I used this exact approach of moving from a stage directory to an incoming directory in a recent J2EE implementation. (As long as the directories are on the same disk partition.) However, in the case of a file being "pushed" to another system such as via FTP, something must monitor the file and know when it has been completely transferred before performing the move or errors can result. I guess my overall statement is be sure to consider the transfer latency SOMEWHERE in your architecture. :-)

RE: SSIS File Exists Over Range of Time

dalcock — Tue, 31 Mar 2009 14:20:11 GMT

[quote][b]Truckin (3/31/2009)[/b][hr]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.[/quote]I agree with the above statement 100 percent. The safest way to work with large data files is to copy them to a staging directory and then execute a move to the directory being polled by the package. I have never once had a contention or timing issue processing files in this manner and doing so removes the need for most of the other overhead.

RE: SSIS File Exists Over Range of Time

V-Man — Tue, 31 Mar 2009 13:04:54 GMT

It seems like another issue to consider is what if the file exists but is still in the process of being written, such as a file that is FTP'd from another system. In cases like that, you probably want to check not only that the file exists but that it can also be opened, using File.Exists in combination with File.OpenRead.

RE: SSIS File Exists Over Range of Time

Truckin — Tue, 31 Mar 2009 12:58:46 GMT

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.

RE: SSIS File Exists Over Range of Time

michael.groh — Tue, 31 Mar 2009 09:31:01 GMT

[quote][b]sknox (3/31/2009)[/b][hr]There's also a tweak I would make .....[code]... If System.IO.File.Exists(Dts.Variables("FlatFileConnection").Value.ToString) Then Dts.TaskResult = ScriptResults.Success Else Dts.TaskResult = ScriptResults.Failure End If...[/code][/quote]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 [i]real good[/i] idea! Sometimes a line or two of addition code can eliminate a lot of trouble later on!

RE: SSIS File Exists Over Range of Time

michael.groh — Tue, 31 Mar 2009 09:27:09 GMT

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.

RE: SSIS File Exists Over Range of Time

Ray M — Tue, 31 Mar 2009 09:08:26 GMT

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.

RE: SSIS File Exists Over Range of Time

Hollywoodrob-1016991 — Tue, 31 Mar 2009 09:06:34 GMT

[quote][b]sknox (3/31/2009)[/b][hr]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 [code]... If Now() >= NewDT Then Dts.TaskResult = ScriptResults.Failure Else Dts.TaskResult = ScriptResults.Success End If...[/code]to [code]... If System.IO.File.Exists(Dts.Variables("FlatFileConnection").Value.ToString) Then Dts.TaskResult = ScriptResults.Success Else Dts.TaskResult = ScriptResults.Failure End If...[/code]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.[/quote]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?

RE: SSIS File Exists Over Range of Time

Hollywoodrob-1016991 — Tue, 31 Mar 2009 09:03:31 GMT

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.

RE: SSIS File Exists Over Range of Time

sknox — Tue, 31 Mar 2009 09:02:50 GMT

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 [code]... If Now() >= NewDT Then Dts.TaskResult = ScriptResults.Failure Else Dts.TaskResult = ScriptResults.Success End If...[/code]to [code]... If System.IO.File.Exists(Dts.Variables("FlatFileConnection").Value.ToString) Then Dts.TaskResult = ScriptResults.Success Else Dts.TaskResult = ScriptResults.Failure End If...[/code]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.

RE: SSIS File Exists Over Range of Time

michael.groh — Tue, 31 Mar 2009 08:16:40 GMT

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:[code]Dim i As intWhile 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 IfEnd While[/code]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.

RE: SSIS File Exists Over Range of Time

stahmosh1 — Tue, 31 Mar 2009 07:43:12 GMT

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.

SSIS File Exists Over Range of Time

Hollywoodrob-1016991 — Tue, 31 Mar 2009 00:32:23 GMT

Comments posted to this topic are about the item [B]SSIS File Exists Over Range of Time[/B]