Miscellaneous Ramblings on Hacking

October 20, 2009

SSIS: Waiting for a file

Filed under: ETL, SQL Server Integration Services, SSIS — Zack Bethem @ 4:50 pm

With longer running processes, it is a common practice to create a 0-byte file as a signal to a dependent process. An example would be the data transfer via FTP of several files to a staging directory. This post shows how to wait for the 0-byte file.

Control Flow

When complete, the control flow for this package will have the following items defined:

  • For Loop Container – this makes the process continue in a loop until the 0byte file is found
  • Check File script task – this checks for the presence of a file and sets a Boolean variable to True or False
  • Sleep script task – this will create a time delay so that the process isn’t checking every nano-second
  • Move CheckFile script task – this moves the 0byte file to avoid conflicts with subsequent data runs

The control flow will appear as follows:

Control Flow

Variables

The following variables need to be defined:

Variable DataType Purpose
CheckFile String Filename of the 0-byte file to look for
CheckFileExists Boolean Boolean flag that is set to true upon detection of the 0-byte file
FFDataPath String Staging directory where the 0-byte file is expected to be placed
SleepMinutes Int32 Number of minutes to sleep between checks for the 0-byte file
Definition: For Loop Container

Configured to loop while User variable User::CheckFileExists is equal to ‘False’.

InitExpression
EvalExpression @[User::CheckFileExists]==False
AssignExpression
Definition: CheckFile Script Task

Configured to manipulate the value of the User variable User::CheckFileExists to ‘True’ or ‘False’.

ReadOnlyVariables User::CheckFile, User::FFDataPath
ReadWriteVariables User::CheckFileExists

The script task will then execute the following code:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO


Public Class ScriptMain

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    ' 
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.

    Public Sub Main()
        '
        ' Created by: Zack Bethem @ AmberLeaf
        '
        Dim fileLoc, fileName As String
        If Dts.Variables.Contains("User::FFDataPath") = True AndAlso _
        Dts.Variables.Contains("User::CheckFile") = True Then
            fileLoc = CStr(Dts.Variables("User::FFDataPath").Value)
            fileName = CStr(Dts.Variables.Item("User::CheckFile").Value)
            'debug System.Windows.Forms.MessageBox.Show("FileDir:" + fileLoc + "FileName:" + fileName)
            If File.Exists(fileLoc + fileName) Then
                Dts.Variables.Item("User::CheckFileExists").Value = True
                'debug System.Windows.Forms.MessageBox.Show("File exists")
            Else
                Dts.Variables.Item("User::CheckFileExists").Value = False
                'debug System.Windows.Forms.MessageBox.Show("File not exists")
            End If
            Dts.TaskResult = Dts.Results.Success
        Else
            Dts.TaskResult = Dts.Results.Failure
        End If
    End Sub

End Class

The script task is configured to execute the Sleep task based on the precedence constraint. This is defined by double clicking on the connector and setting the following:

Capture2

Definition: Sleep Script Task

Configured to sleep or pause the process.

ReadOnlyVariables User::SleepMinutes
ReadWriteVariables

The script task will then execute the following code:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    ' 
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.

    Public Sub Main()
        '
        ' Zack Bethem @ AmberLeaf
        ' Based on: http://blogs.pragmaticworks.com/mike_davis/2009/06/make-an-ssis-package-delay-or-wait-for-data.html
        '
        If Dts.Variables.Contains("User::SleepMinutes") = True Then
            Dim min As Double = Convert.ToDouble(Dts.Variables("SleepMinutes").Value) * 60
            Dim ms As Int32 = Convert.ToInt32(min * 1000)
            System.Threading.Thread.Sleep(ms)

            Dts.TaskResult = Dts.Results.Success
        Else
            Dts.TaskResult = Dts.Results.Failure
        End If

    End Sub

End Class

The For Loop container is configured to execute the Move CheckFile task based on the precedence constraint. This is defined by double clicking on the connector and setting the following:

Capture3

Definition: Move CheckFile Script Task

Configured to move the 0-byte handoff file to an Archive location with the appended suffix of the date in yyyyMMddHH format.

ReadOnlyVariables User::CheckFile,User::FFDataPath,User::FFArchivePath
ReadWriteVariables

The script task will then execute the following code:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

Public Class ScriptMain

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    ' 
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.

    Public Sub Main()
        '
        'check if vars exist
        If Dts.Variables.Contains("User::CheckFile") = True AndAlso _
        Dts.Variables.Contains("User::FFDataPath") = True AndAlso _
        Dts.Variables.Contains("User::FFArchivePath") = True Then

            Dim srcFile, tgtFile, tgtFileName, searchStr As String

            srcFile = CStr(Dts.Variables("User::FFDataPath").Value) + _
            CStr(Dts.Variables("User::CheckFile").Value)

            tgtFileName = CStr(Dts.Variables("User::CheckFile").Value)

            'add a datastamp on handoff file
            tgtFileName = Left$(tgtFileName, InStrRev(tgtFileName, ".") - 1) + _
            "_" + CStr(Format(Now, "yyyyMMddHH")) + _
            Mid(tgtFileName, InStrRev(tgtFileName, "."))

            'System.Windows.Forms.MessageBox.Show(tgtFileName)

            tgtFile = CStr(Dts.Variables("User::FFArchivePath").Value) + tgtFileName

            'System.Windows.Forms.MessageBox.Show(tgtFile)

            Try
                File.Move(srcFile, tgtFile)
                Dts.Events.FireInformation(0, "", "File moved to: " + tgtFile, "", 0, True)
                Dts.TaskResult = Dts.Results.Success
            Catch ex As Exception
                Dts.Events.FireInformation(1, "", "File move failure. Tried to move to: " + tgtFile, "", 0, False)
                Dts.TaskResult = Dts.Results.Failure
            End Try

        End If

    End Sub


End Class

Advertisements

1 Comment »

  1. Great account of a very common process. FYI – there are a couple of components on CodePlex that can allow you get away with a lot less code (and code maintenance). Take a look at the File Properties Task – which can wait for a file to appear, and the Pause Task – which just waits. Keep posting real-life use case articles!

    Comment by toddmcdermid — October 20, 2009 @ 10:07 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: