SQL Server 2005 Integration Services – Part 18

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 = _
            Me.Connections.TextFileConnection
        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.WriteLine()
        oWriter.Write("HireDate" & vbTab & Row.HireDate)
        oWriter.WriteLine()
        oWriter.Write("LoginID" & vbTab & Row.LoginID)
        oWriter.WriteLine()
        oWriter.Write("SalariedFlag" & vbTab & Row.SalariedFlag)
        oWriter.WriteLine()
        oWriter.Write("VacationHours" & vbTab & Row.VacationHours)
        oWriter.WriteLine()
        oWriter.WriteLine()
    End Sub
    Public Overrides Sub PostExecute()
        oWriter.Close()
    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

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles