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.
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 following steps will help you set up this check in your SSIS package.
- Using the Microsoft Business Intelligence Studio, open a new Integration Services project.
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.
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
- Go back to the properties of the Flat File Connection Manager, and click on the expressions options.
- Since this is a flat the expression property must be set ConnectionString, then click open the expression.
- 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.
Drag a script task from the toolbox.
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..
- 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
- Be sure to edit the while true section to match your variable name - System.IO.File.Exists(Dts.Variables("FlatFileConnection").Value.ToString)
- 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
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.