Using SQL Server 2005 Integration Services : Extensibility (part 3) - Script Components

7/7/2012 6:20:24 PM

Script Components

The script component is one of the easiest ways to extend the functionality of the data flow. It allows access to the data flow using script written in Visual Basic .NET. When should you use a script component instead of a custom component? Here are some advantages to using the script component:

  • Less component housekeeping required, allowing you to focus more on scripting the transformation logic.

  • Gives guidance by providing script templates and classes that are generated based on how the component is set up in the data flow. This, in turn, improves the effectiveness of IntelliSense in the script editor.

The disadvantages include:

  • It’s harder to port scripts from one package to another than to install a custom component that can be used in any package.

  • Scripts must be updated to use the latest component column metadata whenever the metadata has changed.

  • Some data flow functionality, such as multiple inputs, is not available to the script component, due to the limitations of the component or its UI editor.

To compare a script component to a custom component further, let’s walk through creating a script component that does the same transformation as the component we created earlier in the section about writing custom components. That is, the script component will take an input column of data type integer and map that integer to the name of a month. For example, 1 will be mapped to “Jan”.

First create a data flow in the Integration Services designer, and then add a source adapter that contains at least one output column with a data type of DT_I4 to be used as the input column to our script component. If you have the data flow from the section on constructing packages programmatically, you can load that package in the designer as your starting point.

Once the data flow with the source adapter is set up, place a script component onto the design surface. A dialog box appears asking if the script component is to be a source adapter, a transformation, or a destination adapter. Select the Transformation option, which initializes the component to be a synchronous transform with one input and one output. Internally in the component, this means the SynchronousInputID property of the output is set to the ID of the input. Next, connect the output of the source adapter to the input of the component. This makes the output columns from the adapter available as upstream columns for the component, which means those columns can be selected as input columns for the component.

Now we are ready to select which input column we want to use. Double-click on the script component to bring up its UI editor. Notice that this is the editor for the script component, which lets you modify the component’s metadata, and is not the editor for modifying the script itself. Select the Input Columns page. Here the upstream columns originating from the source adapter should be displayed as available input columns. Select the column of data type DT_I4 to be used as the input column for the script component. If you are using the data flow from the section on programmatic package construction, select the EmployeeID column from the HumanResources.Employee table, as shown in Figure 3. This adds an input column to the script component. Internally in the component, the LineageID of the input column is set to the LineageID of the upstream output column. Also notice that the usage type of the input column is set to ReadOnly by default, which is correct for our case because the script component will only be reading the value of the column and not writing into the column. Although EmployeeID is not the most likely set of data to be mapped to a month name,  we will use it in this sample. Another option for users who do not want to use EmployeeID is to change the data source so that provides real month data—for example, “select month(startdate)as MonthNumber from DimPromotion” from the AdventureWorksDW database.

Figure 3. Selecting the input column from the list of upstream columns

After selecting the input column, we must add an output column to store the name of the month. In the script component UI editor, select the page for Inputs and Outputs, which displays the input and output of the script component in a tree view. Rename the input to MapMonthInput and the output to MapMonthOutput. Expand the output, and select Output Columns. Click Add Column, and then rename the new output column to Month. Select the new output column, and change its data type to DT_WSTR, with a length of 7, as shown in Figure 4.

Figure 4. Adding an output column

With the input and output columns in place, we are ready to write the script for the component. In the editor, select the Script page and click Design Script to bring up Microsoft Visual Studio for Applications set up as the script editor for the component. Here you can open up the Project Explorer window to see the references that are set and the classes, such as BufferWrapper and ComponentWrapper, that are needed to make the script execution in the data flow possible. The class we need to edit is the ScriptMain class, which contains a template for an overridden method called MapMonthInput_ProcessInputRow. This method will be called once for each row in the data buffer that flows through the component, with the row passed into the method as the Row parameter. Add the code shown in Listing 3 to the class.

Listing 3. Script component example
Public Class ScriptMain
    Inherits UserComponent

    Private Month() As String = New String() {"Invalid", "Jan", "Feb", "Mar", "Apr",
"May", "June", "July", "Aug", "Sept", "Oct", "Nov", "Dec"}

    Public Overrides Sub MapMonthInput_ProcessInputRow(ByVal Row As MapMonthInputBuffer)
        If Row.EmployeeID < 1 Or Row.EmployeeID > 12 Then
            Row.Month = Month(0)
            Row.Month = Month(Row.EmployeeID)
        End If
    End Sub
End Class


The Row object has been generated with properties that correspond to the input and output columns we added to the component, making access to the data in the buffer very easy. You can see the implementations of these properties in the BufferWrapper class. If you worked through the section on writing custom components, you will notice that the custom component’s ProcessInput method takes an entire data buffer and iterates through the rows within the method. In contrast, our script component’s MapMonthInput_ProcessInputRow method is passed a row at a time. What happened to the standard ProcessInput method for the script component? It is still there, but in the ComponentWrapper class. If you bring up the code for that class, you can see the ProcessInput method and how it iterates through the rows in the buffer, calling the MapMonthInput_ProcessInputRow method once per row. Figure 5 shows the script for the component being edited.

Figure 5. Editing the script in the development environment

Once our script has been added, close the script editor and then the script component editor. This completes the configuration of our script component; its output can be attached to the input of another transform or destination adapter that needs the Month output column generated by the component.

This example shows how to use the script component in the most common scenario: as a synchronous transform. However, if you need to implement more advanced functionality in the component, knowledge of data flow concepts becomes invaluable. For example, if you need the script component to be a transform with an asynchronous output instead of a synchronous output that the UI sets up initially, you need to know that you must adjust the configuration made by the UI by manually setting the SynchronousInputID property on the component’s output to 0 using the script component’s UI editor.

  •  .NET Compact Framework 3.5 : Working with Data Sets (part 3) - Reading and Writing a Data Set as XML
  •  .NET Compact Framework 3.5 : Working with Data Sets (part 2) - Data Binding
  •  .NET Compact Framework 3.5 : Working with Data Sets (part 1) - Creating and Accessing DataSet, DataTable, and DataView Objects
  •  .NET Compact Framework 3.5 : Examining ADO.NET
  •  Using SQL Server 2005 Integration Services : Programming Integration Services (part 4) - Connecting the Source and Destination Adapters with a Path
  •  Using SQL Server 2005 Integration Services : Programming Integration Services (part 3) - Setting Up Column Information
  •  Using SQL Server 2005 Integration Services : Programming Integration Services (part 2)
  •  Using SQL Server 2005 Integration Services : Programming Integration Services (part 1) - Creating Packages Programmatically - Data Flow
  •  Using SQL Server 2005 Integration Services : Working with Integration Services Packages (part 2) - Data Flow
  •  Using SQL Server 2005 Integration Services : Working with Integration Services Packages (part 1) - Control Flow
    PS4 game trailer XBox One game trailer
    WiiU game trailer 3ds game trailer
    Top 10 Video Game
    -   Minecraft Mods - MAD PACK #10 'NETHER DOOM!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #9 'KING SLIME!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #2 'LAVA LOBBERS!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #3 'OBSIDIAN LONGSWORD!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Total War: Warhammer [PC] Demigryph Trailer
    -   Minecraft | MINIONS MOVIE MOD! (Despicable Me, Minions Movie)
    -   Minecraft | Crazy Craft 3.0 - Ep 3! "TITANS ATTACK"
    -   Minecraft | Crazy Craft 3.0 - Ep 2! "THIEVING FROM THE CRAZIES"
    -   Minecraft | MORPH HIDE AND SEEK - Minions Despicable Me Mod
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 92 "IS JOE DEAD?!"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 93 "JEDI STRIKE BACK"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 94 "TATOOINE PLANET DESTRUCTION"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 95 "TATOOINE CAPTIVES"
    -   Hitman [PS4/XOne/PC] Alpha Gameplay Trailer
    -   Satellite Reign [PC] Release Date Trailer