SSIS File Exists Over Range of Time

Introduction

I don't know know many times I have wanted SSIS to be able to wait on a file before starting to process it. I have had many packages fail because some file was not put out in a location on time. Example A mail-order sample catalog is to be electronically delivered at 8pm every night onto the server. Lately, The SSIS import process has been running and stopping because the catalog has not sent been on time, Only if the SSIS was to be able to wait 30 more minutes it would see the file out there.

My Solution

I have discovered how to use SSIS to check if a file exists over a time period, and it is a fairly simple task. To accomplish this, the use of a variable and script task are the only things needed for a package. This article will go over how to configure SSIS to look in the location and check for the existence of the file and then keep checking the same folder for up to the given time span. That can be 1 hour, 2 hours, or 30 minutes even. This way the developer does not have to have the re-process/re-execute the SSIS again if the file transfer is delayed.

The Process

The following steps will help you set up this check in your SSIS package.

1. Using the Microsoft Business Intelligence Studio, open a new Integration Services project.

2. Create your connection for the file and copy the location from the filename.

   

   Be sure to set the "Delay Validation" of the file connection to true. This will help with execution of the package in case the file is not there when this is actually run.

   

3. Create a new variable using the Menu Options, View, Other Windows, Variables. You can name this variable whatever you wish, but it must be a string data type.

   The value will be the location pasted into the "Value" Field

   

4. Go back to the properties of the Flat File Connection Manager, and click on the expressions options.
5. Since this is a flat the expression property must be set ConnectionString, then click open the expression.
6. Drag the Variable from the drop down variables into the expression area. By clicking Evaluate expression, the value of the variable will also show up, now click ok.

7. Drag a script task from the toolbox.

   

8. Double click on the script task and set the read only variables to the flat file variable that was just created and set this script to VB Script..

   

9. Click on Edit Script and paste the following code into the edit script:

   Imports System
   Imports System.Data
   Imports System.Math
   Imports Microsoft.SqlServer.Dts.Runtime
   Enum ScriptResults
       Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
       Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
   End Enum
   
   Partial Public Class ScriptMain
       Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
       Public Sub Main()
           Dim Dt As DateTime = Now()
           Dim NewDT As DateTime = Dt.AddHours(4)
           While True
               If System.IO.File.Exists(Dts.Variables("FlatFileConnection").Value.ToString)Then
                   Exit While
               Else
                   If Now() >= NewDT Then
                       Exit While
                   End If
               End If
           End While
           If Now() >= NewDT Then
               Dts.TaskResult = ScriptResults.Failure
           Else
               Dts.TaskResult = ScriptResults.Success
           End If
       End Sub
   End Class
   

10. Be sure to edit the while true section to match your variable name - System.IO.File.Exists(Dts.Variables("FlatFileConnection").Value.ToString)
11. To alter the time range goto the line marked Dim NewDT As DateTime = Dt.AddHours(1), by altering the number in the Dt.AddHours() this will alow you to set your range.

Click on exit and press OK

The Conclusion

This process created a flat file connection through the connection manager. Then a new variable was created and used as the file connection path and name. Finally with the creation of a VB script task, the developer can tell the SSIS to keep looking for a file for however much time they need, be it hour, 2 hours, or even 23 hours.