Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SSIS File Exists Over Range of Time

By Silver Pheonix,

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.

Total article views: 5535 | Views in the last 30 days: 2
 
Related Articles
FORUM

One Click Installation Scripts

One Click Script for installing Sql Server Script

FORUM

SSIS variable added to import file

Pass variable to import

FORUM

Transformation VB Script

Import Transformation VB Script

FORUM

Variable not in script task

Package level variable not in dts.variables in script

FORUM
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones