What if you had a general idea of a window of time that these flat files were most likely to arrive. Say the flat files are supposed to be there every day at 5:00 pm, but never show up later than 5:30 pm. Wouldn't it be nice to have your load job be more flexible and give the file some time to show up before running the rest of the job, and not having to later bug a DBA to kick it off again to get the late arriving files? Well, luckily it's pretty easy to do, and I'll show you in this post how to do it.
The control flow for this example is going to look like this:
|Figure 1. Control Flow|
We're going to take advantage of a Script Task to do this. If we have success we process the file, if not we log it and/or shoot off an email. First thing we need to do is create our flat file connection manager for our test file. For this example I created a test directory in the root of c: called test. In the test directory I created a file called test.txt that the file connection can reference:
|Figure 2. Flat File Connection Manager|
Next, we need to specify some configurations for our script task by using SSIS variables. The first variable we're going to create will be called minutesToCancel. This variable will tell the script task how many minutes to wait for the file to arrive in the directory before timing out. The second variable will be called secondsToTryAgain. This variable will tell the script task how many seconds to wait after not seeing the file in the directory before checking again to see if it arrived. In my example here I set the minutesToCancel to 1 minute and my secondsToTryAgain to 15 seconds. These can be changed to whatever you want based on your particular setup.
|Figure 3. SSIS Variables|
With our flat file connection manager set up and SSIS variables created, we can start coding. Drag a script task onto the control flow design surface. On the Scipt screen, make sure to select our variables minutesToCancel and secondsToTryAgain as ReadOnlyVariables. This way the script task can access them in code. Click the Edit Script button so we can start coding.
|Figure 4. Script Task Script Screen|
Paste the following code into the script task:
If the file is found, and not being written to, we output to the progress tab a file found message, then proceed to the data flow that will process this file:
|Figure 5. Success Message|
If the file has not arrived before the time out threshold, we output an error message and fail the script task(if you don't want the script task to fail on time out call Success() instead of Failure() ):
|Figure 6. Failure Message|
|Figure 7. File Written to Message|
|Figure 8. Success After File Finished Being Written|