THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is

SSIS Design Pattern: Producing a Footer Row

imageThe following is an excerpt from SSIS Design Patterns (now available in the UK!) Chapter 7, Flat File Source Patterns. The only planned appearance of all five authors presenting on SSIS Design Patterns is the SSIS Design Patterns day-long pre-conference session at the PASS Summit 2012. Register today.

Let’s look at producing a footer row and adding it to the data file. For this pattern, we will leverage project and package parameters. We will also leverage the Parent-Child pattern, which will be discussed in detail in another chapter. We are not going to build the package that creates a flat file containing data. We will start with the assumptions that an extract file exists and we know the number of rows and the extract date. We will use parameters to transmit metadata from the parent package to the child package. Let’s get started!

Create a new SSIS package and name it WriteFileFooter.dtsx. Click on the Parameters tab and add the following parameters:


Data Type































The parameters, when entered, appear as shown in Figure 7-20:

Figure 7-20. Parameters for the WriteFileFooter.dtsx Package

The Sensitive property for each parameter is set to False. The Description is optional and available in the image.
We’re going to do the heavy lifting in a Script Task. Return to the Control Flow and drag a Script Task onto the canvas. Change the name to “scr Append File Footer” and open the editor. On the Script page, click the ellipsis in the ReadOnlyVariables property’s value textbox. When the Select Variables window displays, select the following variables:
•    System::PackageName
•    System::TaskName
•    $Package::AmountSum
•    $Package::DateFormat
•    $Package::Debug
•    $Package::Delimiter
•    $Package::ExtractFilePath
•    $Package::LastUpdateDateTime
•    $Package::RecordCount

The Select Variables window will not appear exactly as shown in Figure 7-21, but these are the variables you need to select for use inside the “scr Append File Footer” Script Task:

Figure 7-21.Selecing Variables for the Footer File

Click the OK button to close the Select Variables window. Set the ScriptLanguage property to Microsoft Visual Basic 2010. Click the Edit Script button to open the VstaProjects window. At the top of the ScriptMain.vb code window, you will find an “Import” region. Add the following lines to that region:

Imports System.IO
Imports System.Text

Just after the Partial Class declaration, add the variable declaration for the bDebug variable (the Dim statement below):

Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Dim bDebug As Boolean

Replace the code in Public Sub Main with the following:

    Public Sub Main()

        ' 1: detect Debug setting...
        bDebug = Convert.ToBoolean(Dts.Variables("Debug").Value)

        ' 2: declare and initialize variables...
        ' 2a: generic variables...
        Dim sPackageName As String = Dts.Variables("PackageName").Value.ToString
        Dim sTaskName As String = Dts.Variables("TaskName").Value.ToString
        Dim sSubComponent As String = sPackageName & "." & sTaskName
        Dim sMsg As String
        ' 2b: task-specific variables...
        Dim sExtractFilePath As String = Dts.Variables("ExtractFilePath").Value.ToString
        Dim iRecordCount As Integer = Convert.ToInt32(Dts.Variables("RecordCount").Value)
        Dim sAmountSum As String = Dts.Variables("AmountSum").Value.ToString
        Dim sDateFormat As String = Dts.Variables("DateFormat").Value.ToString
        Dim sDelimiter As String = Dts.Variables("Delimiter").Value.ToString
        Dim sLastUpdateDateTime As String= _
Strings.Format(Dts.Variables("LastUpdateDateTime").Value, sDateFormat) _
'"yyyy/MM/dd hh:mm:ss.fff")
        Dim sFooterRow As String
        Dim s As Integer = 0

        ' 3: log values...
        sMsg = "Package Name.Task Name: " & sSubComponent & ControlChars.CrLf & _
ControlChars.CrLf & _
            "Extract File Path: " & sExtractFilePath & ControlChars.CrLf & _
ControlChars.CrLf & _
            "Record Count: " & iRecordCount.ToString & ControlChars.CrLf & _
ControlChars.CrLf & _
               "Amount Sum: " & sAmountSum & ControlChars.CrLf & ControlChars.CrLf & _
               "Date Format: " & sDateFormat & ControlChars.CrLf & ControlChars.CrLf & _
               "Delimiter: " & sDelimiter & ControlChars.CrLf & ControlChars.CrLf & _
            "LastUpdateDateTime: " & sLastUpdateDateTime & ControlChars.CrLf & _
ControlChars.CrLf & _
               "Debug: " & bDebug.ToString
        Dts.Events.FireInformation(0, sSubComponent, sMsg, "", 0, True)
        If bDebug Then MsgBox(sMsg)

        ' 4: create footer row...
        sFooterRow = iRecordCount.ToString & sDelimiter & sAmountSum & sDelimiter & _
        ' 5: log...
        sMsg = "Footer Row: " & sFooterRow
        Dts.Events.FireInformation(0, sSubComponent, sMsg, "", 0, True)
        If bDebug Then MsgBox(sMsg)

        ' 6: check if the file is in use...
        While FileInUse(sExtractFilePath)
            ' 6a: if file is in use, sleep for a second...
            ' 6b: incrementor...
            s += 1
            ' 6c: if incrementor reaches 10 (10 seconds),
            If s > 10 Then
                ' exit the loop...
                Exit While
            End If 's > 10
        End While 'FileInUse(sExtractFilePath)
        ' 7: log...
        If s = 1 Then
            sMsg = "File was in use " & s.ToString & " time."
        Else ' s = 1
            sMsg = "File was in use " & s.ToString & " times."
        End If ' s = 1
        Dts.Events.FireInformation(0, sSubComponent, sMsg, "", 0, True)
        If bDebug Then MsgBox(sMsg)

        ' 8: if the file exists...
        If File.Exists(sExtractFilePath) Then
                ' 8a: open it for append, encoded as built, using a streamwriter...
                Dim writer As StreamWriter = New StreamWriter(sExtractFilePath, True, _
                ' 8b: add the footer row...
                ' 8c: clean up...
                ' 8d: get out...
                ' 8e: log...
                sMsg = "File " & sExtractFilePath & " exists and the footer row has " & _
"been appended."
                Dts.Events.FireInformation(0, sSubComponent, sMsg, "", 0, True)
                If bDebug Then MsgBox(sMsg)
            Catch ex As Exception
                ' 8f: log...
                sMsg = "Issue with appending footer row to " & sExtractFilePath & _
" file: " & ControlChars.CrLf & ex.Message
                Dts.Events.FireInformation(0, sSubComponent, sMsg, "", 0, True)
                If bDebug Then MsgBox(sMsg)
            End Try
            ' 8g: log...
            sMsg = "Cannot find file: " & sExtractFilePath
            Dts.Events.FireInformation(0, sSubComponent, sMsg, "", 0, True)
            If bDebug Then MsgBox(sMsg)
        End If ' File.Exists(sExtractFilePath)

        '  9: return success...
        Dts.TaskResult = ScriptResults.Success

    End Sub

Add the following function after Public Sub Main():

    Function FileInUse(ByVal sFile As String) As Boolean

        If File.Exists(sFile) Then
                Dim f As Integer = FreeFile()
                FileOpen(f, sFile, OpenMode.Binary, OpenAccess.ReadWrite, _
            Catch ex As Exception
                Return True
            End Try
        End If
    End Function

This script builds the footer row and appends it to the Extract file. The first thing we do – at the comment labeled 1 – is assign a value to the Debug variable. I use the Debug variable to control message boxes displaying variable values and other pertinent information. I describe why in the chapter on Execution Patterns.

At comment 2, we declare and initialize variables. I break variables into two categories: generic and task-specific variables. At comment 3, we build a message in the variable sMsg. This message contains the values of each variable used in the Script thus far. If we are running in Debug mode (if bDebug is True), the code displays a message box (via the MsgBox function) containing the contents of sMsg. Whether we’re running in Debug Mode or not, I use the Dts.Events.FireInformation method to raise an OnInformation event, passing it the contents of sMsg. This means the information is always logged and is optionally displayed by a message box. I like options (a lot).

Comment 4 has us constructing the actual footer row and placing its text in the String variable sFooterRow. Note the delimiter is also dynamic. The String variable sDelimiter contains the value passed to the WriteFileFooter into the Package Parameter named $Package::Delimiter. At comment 5, we log the contents of the footer row.

At comment 6, we initiate a check to make sure the Extract File is not marked as “in use” by the operating system. There are many ways to detect the state of the file in the file system, so I created a Boolean function named FileInUse to encapsulate this test. If the function I created doesn’t work for you, you can construct your own. If the file is in use, the code initiates a While loop that sleeps the thread for one second. Each iteration through the loop causes the variable s (the incrementor in this example) to increment at comment 6b. If s exceeds ten, the loop exits. We will only wait 10 seconds for the file to be usable. Note that if the file remains in use at this juncture, we still move on. We'll deal with the file in use matter later, but we will not hang ourselves in a potentially endless loop waiting for the file’s availability. We will instead fail. Whether the file is in use or not in use, the script logs its state at comment 7.

At comment 8, we check for the existence of the file and begin a Try-Catch. If the file doesn’t exist, I opt to log a status message (via Dts.Events.FireInformation) and continue (see comment 8g). The Try-Catch enforces the final test of the file’s usability. If the file remains in use here, the Catch fires and logs the status message at comment 8f. At 8f and / or 8g, you may very well decide to raise an error using the Dts.Events.FireError method. Raising an error causes the Script Task to fail, and you may want this to happen. At comments 8a through 8d, we open the file, append the footer row, close the file, and clean up. At comment 8e, the code logs a status message. If anything fails when executing 8a through 8e, code execution jumps to the Catch block.

If all goes well, the code returns Success to the SSIS Control Flow via the Dts.TaskResult function (comment 9).
The Script Task does all the work in this pattern.

I created a test package called TestParent.dtsx to test this package. The package has variables that align with the parameters of the WriteFileFooter.dtsx package, as shown in Figure 7-22:

Figure 7-22. Variables in the TestParent.dtsx Package

If you’re playing along at home, you should adjust the path of the ExtractFooterFilePath variable.
I added a Sequence Container named “seq Test WriteFileFooter” and included an Execute Package Task named “ept Execute WriteFileFooter Package.” On the Package page of the Execute Package Task Editor, set the ReferenceType property to “Project Reference” and select WriteFileFooter.dtsx from the PackageNameFromProjectReference property dropdown. Map the TestParent package variables to the WriteFileFooter package parameters as shown in Figure 7-23:

Figure 7-23. Mapping Package Parameters

Execute TestParent.dtsx to test the functionality. The package executes successfully and the footer row is appended to the file as shown in Figure 7-24:

Figure 7-24. Mission Accomplished

Interesting stuff? I think so but I’m biased; I wrote it! You can get more in the SSIS Design Patterns book and by attending the SSIS Design Patterns day-long pre-conference session at the PASS Summit 2012.


Published Tuesday, October 9, 2012 8:00 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


This Blog



My Latest Book:

Community Awards

Friend of Red Gate

Contact Me


Privacy Statement