SQLServerCentral Article

SSIS File Exists Over Range of Time



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
                    If Now() >= NewDT Then
                        Exit While
                    End If
                End If
            End While
            If Now() >= NewDT Then
                Dts.TaskResult = ScriptResults.Failure
                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.


3.12 (25)

You rated this post out of 5. Change rating




3.12 (25)

You rated this post out of 5. Change rating