The 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:
| Name | Data Type | Value | Required |
| AmountSum | Decimal | 0 | FALSE |
| DateFormat | String | | TRUE |
| Debug | Boolean | TRUE | FALSE |
| Delimiter | String | , | TRUE |
| ExtractFilePath | String | | TRUE |
| LastUpdateDateTime | DateTime | 1/1/1900 | TRUE |
| RecordCount | Int32 | 0 | TRUE |
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 & _
sLastUpdateDateTime
' 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...
System.Threading.Thread.Sleep(1000)
' 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
Try
' 8a: open it for append, encoded as built, using a streamwriter...
Dim writer As StreamWriter = New StreamWriter(sExtractFilePath, True, _
Encoding.Default)
' 8b: add the footer row...
writer.WriteLine(sFooterRow)
' 8c: clean up...
writer.Flush()
' 8d: get out...
writer.Close()
' 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
Else
' 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
Try
Dim f As Integer = FreeFile()
FileOpen(f, sFile, OpenMode.Binary, OpenAccess.ReadWrite, _
OpenShare.LockReadWrite)
FileClose(f)
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.
:{>