Interacting with File System Objects Using SSIS Script Task

As we have demonstrated in recent articles published on this forum, Script Task, included in SQL Server 2008 Integration Services, offers an extensive degree of flexibility, making it possible to satisfy extraction, transformation, and loading requirements not covered by other, more specialized built-in components. This allows you to not only deal with non-traditional data stores (such as Active Directory), but also customize the processing of standard sources of data (such as flat files) by leveraging their unique characteristics.

In order to explore the features that are available using this approach, we will demonstrate a sample procedure that involves the most common operations on file system objects (such as reading, renaming, or identifying their attributes). Our intention is to facilitate a scenario in which text files are uploaded to a designated folder in regular intervals. For each of them, our Script Task will do the following:

• first verify the timestamp (ensuring that it matches the current date),
• read the entire content (saving it into an SSIS variable, which subsequently can be utilized by other package components),
• alter the .txt extension to indicate that the processing has completed

By placing the task in a properly designed For Loop Container, you can apply this sequence of steps to all files in the same folder relatively easily.

To accomplish our objective, start by launching Business Intelligence Development Studio, create an SSIS project based on the Integration Services template, and define two String SSIS variables with the package scope. The first of them (named sFilePath) should be set to a fully qualified path to the source file, while the other (which we will call sFileContent) is intended to provide temporary storage for its content. Next, drag the Script Task icon from the Toolbox and drop it on the Designer interface. Display its Editor interface, and designate (respectively) Visual Basic .NET 2008 as the ScriptLanguage, User::sFilePath as ReadOnlyVariables, and User::sFileContent as ReadWriteVariables. Finally, click on the Edit Script... command button to bring up the Visual Studio Tools for Applications interface, where we will enter our custom code.

File system functionality (including relevant classes with their properties and methods) that we will leverage in our example is incorporated into System.IO and System.Text namespaces (which you can find more about by referring to the msdn Web site documentation). Since both of them should be included in the VSTA project already, simply the add corresponding Imports statements in the (General) (Declarations) section on the ScriptMain.vb tab in the VSTA project window. Next, switch to the Public Main() Sub and enter the following code:

Public Sub Main()
	'
	' Add your code here
        '
        Dim strFilePath As String
        Dim strFileContent As String
        Dim objFileInfo As FileInfo
        Dim objStreamReader As StreamReader

        Try

            strFilePath = Dts.Variables("sFilePath").Value.ToString()
            objFileInfo = New FileInfo(strFilePath)

            If (objFileInfo.LastWriteTime().Date = DateTime.Today) Then

                MessageBox.Show("Today", "Last Modified Date", MessageBoxButtons.OK)

                objStreamReader = New StreamReader(strFilePath)
                strFileContent = objStreamReader.ReadToEnd()
                objStreamReader.Close()

                Dts.Variables("sFileContent").Value = strFileContent
                MessageBox.Show(Dts.Variables("sFileContent").Value, "File Content", MessageBoxButtons.OK)
                objFileInfo.MoveTo(strFilePath.Replace(".txt", ".old"))

            End If

            Dts.TaskResult = ScriptResults.Success

        Catch ex As Exception

            MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK)
            Dts.Events.FireError(1, ex.TargetSite.ToString(), ex.Message.ToString(), "", 0)

            Dts.TaskResult = ScriptResults.Failure

        End Try
    End Sub

End Class

We start by declaring a number of variables. Two of them, corresponding to those defined earlier in SSIS Designer interface (namely User::sFilePath and User::sFileContent) will facilitate interaction between the package and VSTA code. The FileInfo object will provide the ability to identify the LastWriteTime attribute and rename the text file after its processing has been completed. Methods defined as part of the StreamReader class will allow us to read the content of our sample text file.

By enclosing our code into Try Catch block, we guard against potential exceptions that might surface during runtime. With strFilePath set to a value of SSIS User::sFilePath variable, we use the LastWriteTime() method of the FileInfo object to determine whether the sample file was modified today (incidentally, if the file does not exist, the method returns 12:00 midnight, January 1, 1601 A.D. Coordinated Universal Time, adjusted to local time). Assuming that the outcome is positive, we read its entire content using the ReadToEnd() method. By assigning the value of strFileContent to User::sFileContent SSIS variable, we make it available to other package components. Finally, we take advantage of the MoveTo() method of FileInfo class to replace the extension of the file from .txt to .old. For more information regarding the code following the Catch ex As Exception statement, refer to our article on Script Task Exception Handling. On a final note, keep in mind that the message boxes that we leverage to indicate the progress of execution are used strictly for testing purposes and should be avoided in production-ready implementations.

Obviously, this example serves barely as a hint of the abundant possibilities available via Script Task to more skillful programmers. In our upcoming articles, we will be exploring other applications of this functionality.

» See All Articles by Columnist Marcin Policht