SQL Server 2005 Integration Services - Part 18
February 10, 2006
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 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.