SQL Server 2005 Integration Services - Part 18

Friday Feb 10th 2006 by Marcin Policht

Marcin Policht reviews the characteristics of the Destination Script Component and provides a simple example illustrating its implementation.

Destination Script Component

In the previous two articles of our series dedicated to SQL Server 2005 Integration Services, we demonstrated versatility of the Script Component, which is capable of operating as either a Data Flow source or a transformation. In addition, it is also possible to have this component configured as a destination, extending the range of accessible data stores beyond those covered by built-in data flow features (which, for example, do not support files with XML or non-standard formatting). In this article, we will review characteristics of the Destination Script Component and provide a simple example illustrating its implementation.

As with the other two types of configurations types (source and transformation), you are prompted to decide whether you intend to create a destination as soon as you drag the Script Component icon from the Toolbox and drop it onto the Data Flow area of the SSIS Designer interface (in the Select Script Component Type dialog box). As before, your decision has impact on the number of available inputs and outputs. By default, there exists only a single input (with generic name Input 0, which you can easily change), but you do have the ability to create one or more outputs, using the Add Output command button in the Inputs and Outputs section of the Script Transformation Editor (although the need for outputs in a destination component is rather uncommon). The editor also contains three other sections - Input Columns, Script, and Connection Managers - described in more details in the introductory article on this topic.

In our example, the input will be provided by the Transformation Script Component, which implementation we presented in the previous installment of our series. Its synchronous output consists of five columns (EmployeeID, LoginID, HireDate, SalariedFlag, and VacationHours), which were slightly modified from their original source in the [HumanResources].[Employee] table of the AdventureWorks database. You can simply delete the existing Flat File Destination to which output is pointing and replace it with a newly created Script Destination Component.

Typically, a destination Script Component relies on a connection to a data store identified within the Connections Managers section (by clicking on the Add... command button, defining a new or using an existing connection manager, and assigning to it an alias to be referenced within the component). While this simplifies designating a data flow target, it is still necessary to create Visual Basic .NET code (within the Microsoft Visual Studio for Applications interface accessible via Script section of the Editor) that will populate the store and perform connection maintenance tasks, such as its opening and closing. Typically, these additional activities are performed during PreExecute and PostExecute stages of the Script Component lifetime (which, as you can expect, take place immediately before and after its input rows are processed). Existing connections are referenced in the code via a collection (called Connections) of instances of IDTSConnectionManager90 class.

To demonstrate this approach, let's assume that we intend to dump the results of our package (in the form of five data columns processed by the Script Transformation Component) to a text file, with each data field appearing on a separate line, preceded by the column name and a single tab. To accomplish this, we need to create the appropriate collection manager, by clicking on the Add... command button in the Connection Managers section of the Script Transformation Editor, assigning a descriptive alias to it (we will call it TextFileConnection), activating the option in the Connection Manager column, selecting the "FILE Connection manager for files" entry in the Add SSIS Connection Manager dialog box, and filling out the appropriate details (usage type as well as file name and path) in the File Connection Manager Editor dialog box.

Once this is done, we are ready to modify the pre-created templates of our ScriptMain class. Switch to the Script section within the Script Transformation Editor and click on Design Script... Command button, to activate the Microsoft Visual Studio for Applications interface. Start by adding the Imports System.IO statement, since, as you might recall, we will need to directly manipulate our destination file. For the same reason, we will also declare two variables. The first one (which we call sDestinationFile) of the String type will temporarily store the name of the target file (which we retrieve from the parameters of the previously defined connection). The second one (oWriter) will be an instance of StreamWriter class, representing this file, which methods (such as Write or Close) we will use to interact with it. Our code will take the following form:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO
Public Class ScriptMain
    Inherits UserComponent
    Dim sDestinationFile As String
    Dim oWriter As StreamWriter
    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
        Dim oConMgr As IDTSConnectionManager90 = _
        sDestinationFile = CType(oConMgr.AcquireConnection(Nothing), String)
    End Sub
    Public Overrides Sub PreExecute()
        oWriter = New StreamWriter(sDestinationFile, False)
    End Sub
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        oWriter.Write("EmployeeID" & vbTab & Row.EmployeeID)
        oWriter.Write("HireDate" & vbTab & Row.HireDate)
        oWriter.Write("LoginID" & vbTab & Row.LoginID)
        oWriter.Write("SalariedFlag" & vbTab & Row.SalariedFlag)
        oWriter.Write("VacationHours" & vbTab & Row.VacationHours)
    End Sub
    Public Overrides Sub PostExecute()
    End Sub
End Class

As you can see, we start by retrieving the name of the destination file, which subsequently is used in the PreExecute stage as a reference to its representation (in the form of an instance of the StreamWriter class). Once the instance is available, we enter consecutive fields of each data row into it, using the Write method (within the ProcessInputRow sub). Finally, once all the rows are processed (and the PostExecute stage is reached), we call the Close method to gracefully complete the execution. Our target file will contain at this point all of the transformed data in the desired format.

» See All Articles by Columnist Marcin Policht

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved