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 1:04 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 31, 2014 2:37 PM
Points: 4, Visits: 11
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.
Post #687350
Posted Tuesday, March 31, 2009 2:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 6, 2012 3:06 AM
Points: 63, Visits: 53
Truckin (3/31/2009)

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.


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.
Post #687448
Posted Tuesday, March 31, 2009 2:31 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 31, 2014 2:37 PM
Points: 4, Visits: 11
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.
Post #687454
Posted Tuesday, March 31, 2009 2:41 PM
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
V-Man (3/31/2009)
... guess my overall statement is be sure to consider the transfer latency SOMEWHERE in your architecture.


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.
Post #687464
Posted Wednesday, April 1, 2009 8:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 8, 2009 6:41 AM
Points: 40, Visits: 72
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, MCSD

DBA
GMP Securities L.P.

Post #688020
Posted Wednesday, April 1, 2009 3:05 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 8:50 AM
Points: 1,486, Visits: 1,037
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.
Post #688398
Posted Tuesday, April 7, 2009 9:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 22, 2010 4:36 AM
Points: 4, Visits: 65
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.
Post #692203
Posted Thursday, April 9, 2009 2:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 8, 2009 6:41 AM
Points: 40, Visits: 72
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.
Post #694419
Posted Wednesday, April 15, 2009 2:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 20, 2014 2:37 PM
Points: 1, Visits: 20
Great article! This is very useful including the additional tweaks other users added. These steps are working great for a package I created.
Post #697949
Posted Thursday, June 18, 2009 10:09 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 4:54 AM
Points: 815, Visits: 32
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


If it ain't broke, don't fix it...
Post #737729
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse