The Script Transformation part 3 ? as a Destination

[James Beresford]

Fig 1: The Script Transformation


In this post I will be covering using the Script Transformation as a Destination. The sample package can be found here for 2005 and guidelines on use are here.

How can you use the Script Component as a destination?

As before, when dropping the Script Component on the Data Flow surface, you get three choices for what you want to use it as – in this case, you would select Destination. This by default creates a component with a single input and no outputs.You will have to manually define and type the input columns, and the component will only accept a single input.

Each option selected provides a different code template as well, and for a Destination you get code designed to consume incoming rows via the ProcessInputRow method. My example code below simply counts the rows pushed through the component.

Public Class ScriptMain
Inherits UserComponent

Dim RowCount As Integer = 0

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

‘ Increase row count
RowCount = RowCount + 1

‘ Message box giving progress


MsgBox(RowCount.ToString, MsgBoxStyle.OkOnly, “I have processed this many rows:”)

End Sub

End Class

This pretty much concludes my review of the Script Component, though there is more that can be done with it. However to get much further i’d have to get deeper into the coding, an area I have sketchy knowledge of!

Where would you use the Script Component as a destination?

This component can be used in this manner in order to deliver data to a destination that cannot be reached by a standard component, such as a Web Service or non standard flat file.

MSDN Documentation for the Script Transformation can be found here for 2008 and here for 2005.

If you are still struggling, try these additional resources:

• Creating a Destination with the Script Component (MSDN) – has a lot of example scripts

If you need specific help or advice, or have suggestions on the post, please leave a comment and I will do my best to help you.



Get More from the original blog...