518 Hands-On Microsoft SQL Server 2008 Integration Services
26. Now that you’ve completed the configurations of the synchronous transformation
component in the Metadata design mode, it is time to write some code in the
Code design mode. Click Edit Script to open the scripting environment. Let’s
take some time to understand the auto-generated code and the differences in this
code compared to when we create a source.
Open the BufferWrapper.vb project item. Note that there is only one class with the
name of input buffer—i.e., XfrInputBuffer—and all the columns are exposed as
typed properties, including the ones that were created in the output. There is only
one buffer class instead of the two that one may expect—one for input and one
for output. This indicates and confirms that in a synchronous transformation the
transformations are applied on the same buffer in place and the buffer is passed
on to the next component as is without moving data to a new buffer. This enables
synchronous transformations to be able to flow the data as it appears in the input
and perform the transformations at a very fast speed. The XfrInputBuffer exposes
two typed accessor properties for each column, one with the column name to refer
to the column and the other one with the <Column Name>_IsNull name used to
set the value of a column to null. In the end, it contains the AddRow method and
the SetEndOfRowset method along with a function EndOfRowset to mark the
end of a rowset in the pipeline.
Now open the ComponentWrapper.vb project item. Note that a ProcessInput
method has been provided here. The ProcessInput method is repeatedly called
for each row as it receives the buffers from the upstream component until the
end of the rowset is reached. Another subroutine, XfrInput_ProcessInput, is
created here. This method is also used in the ScriptMain class in the Main.vb
project item. If you change the name of the input, the ComponentWrapper will
regenerate the code and will use the updated name in these methods; however,
the Main.vb item doesn’t regenerate the code and hence, will need to be modified
manually. Scroll down to where the Variables class declares a typed property
varBonusMultiplier, the same name as our package variable, and returns the
package variable value. We will use this property in our custom code to access the

package variable in the Main.vb project item. Also in the ScriptMain class, note
that you don’t have CreateNewOutputRows as you did in the source component;
rather, you have the XfrInput_ProcessInputRow method that is derived from the
ComponentWrapper project item and will be run for each data row.
27. We will need two variables in the script to calculate the values for the output
columns. Though the calculated values could also be passed on to the output
variables directly without involving intermediate script variables, yet it is a better
practice to use script variables. Use of script variables helps in capturing calculated
values in your script that you can use for further processing or for auditing purposes.
Chapter 11: Programming Integration Services 519
If you pass the calculated value directly to an output column, you can’t read back
that value, as the output columns are write-only columns. We will see this clearly
while we work with asynchronous output. Add the following lines just after the
Inherits UserComponent line in the ScriptMain class (refer to Figure 11-14):
Dim BonusPaid As String
Dim Bonus As Decimal = 0
Figure 11-14 Code listing for Script Synchronous transformation
520 Hands-On Microsoft SQL Server 2008 Integration Services
28. Add the following code in the XfrInput_ProcessInputRow subroutine in the
ScriptMain class as shown in Figure 11-14.
Dim Bonusfactor As Integer = Me.Variables.varBonusMultiplier
If (Row.Title = "Sales Representative" And Row.SalesAmount > 10000) Then
BonusPaid = "Y"
Bonus = Row.SalesAmount * Bonusfactor / 100
ElseIf (Row.Title = "Sales Manager" And Row.SalesAmount > 50000) Then
BonusPaid = "Y"
Bonus = Row.SalesAmount * Bonusfactor / 100
ElseIf (Row.Title = "Vice President" And Row.SalesAmount > 100000) Then
BonusPaid = "Y"
Bonus = Row.SalesAmount * Bonusfactor / 100

Else : BonusPaid = "N"
Bonus = 0
End If
29. As you can see, scripting a synchronous component is quite easy. Close the
scripting environment and the Script Transformation Editor. You’ve completed
scripting a synchronous component.
30. As you tested the source component, you can test the synchronous component also.
Add a grid-type data viewer to the path connected to Row Count transformation.
Execute the package and you will see the two derived columns in the
SynchXfrOutput Data Viewer as shown in Figure 11-15.
Scripting an Asynchronous Transformation
In this part, we will calculate total sales and total bonus paid. As you can see, this
aggregation cannot be done unless all the records have been processed. This is a clear
case for asynchronous output. You can add another Script component and can configure
it as an asynchronous transformation. However, we will use the already-added script
transformation and will add asynchronous output to it to demonstrate that a Script
component can have both a synchronous output and an asynchronous output.
Figure 11-15 SynchXfrOutput Data Viewer showing the derived columns
Chapter 11: Programming Integration Services 521
31. Double-click the Script transformation component to open the editor and go to the
Inputs and Outputs page. Add a new output and rename it as AsyncXfrOutput.
Note that the SynchronousInputID is set to None by default (Figure 11-16).
You’ve learned about this property in the previous part, but just to remind you
here, this property decides whether the output will be of a synchronous type or
will be an asynchronous output. The None value here is okay with us, as we want
to add an asynchronous output.
Figure 11-16 Adding an Asynchronous Output to a Script transformation
522 Hands-On Microsoft SQL Server 2008 Integration Services
32. Click the Output Columns node and then add two columns for the asynchronous
output as per the following details:

Column Name Data Type
TotalSalesAmount numeric [DT_NUMERIC]
TotalBonusAmount numeric [DT_NUMERIC]
33. There is nothing more to configure in the metadata design mode. Let’s go
now to the code-design mode and click Edit Script to open the VSTA IDE.
Let’s see how the auto-generated code has behaved with the addition of a new
asynchronous output.
Open the BufferWrapper.vb project item. One thing you can immediately
notice is that there are two buffer classes: the XfrInputBuffer class, as you had
in the previous part, represents the synchronous output, while the other one, the
AsyncXfrOutputBuffer class, represents the asynchronous output. There is still
no buffer class for the SynchXfrOutput, as the synchronous output columns are
contained in the XfrInputBuffer class. The new AsyncXfrOutputBuffer class
contains two typed accessor properties for each column, one with the column
name and the other one with <Column Name>_IsNull name to set the value
of a column to null. Like the synchronous output class, it also has an AddRow
method and a SetEndOfRowset method to mark the end of the rowset.
Moving on to the ComponentWrapper.vb project item, it also differs from that of
synchronous transformation. Here you can see the PrimeOutput method that was
also used in the Script Source component. The PrimeOutput method prepares
the outputs to accept new rows and is used for the outputs where you add new
rows to the output buffers such as a source or a transformation with asynchronous
output. This method calls CreateNewOutputRows to create a new row in our
case. The ProcessInput method, which is repeatedly called for each row as it
receives the buffers from the upstream component until the end of the rowset
is reached, does a bit more work this time. It looks for the end of the rowset and
lets the Data Flow task know that the outputs have finished. It uses Buffer
.EndOfRowset to know that the current buffer is the final buffer in the rowset.
When you look at the ScriptMain class in the Main.vb project item, you will realize
that the code listed here has not changed. This further confirms that the code you

add in this item is persisted and does not get affected with auto-generated code.
The Main.vb item is auto-generated only the first time you click Edit Script,
and the contents in the ScriptMain class depend upon the configurations you’ve
done in the metadata design mode. In our case, obviously the code required for
asynchronous output has not been generated in the ScriptMain class, as it already
existed, so we will need to write the required code completely ourselves. But hey,
that’s not very difficult. We need to write the code for the following three steps:
Chapter 11: Programming Integration Services 523
First, we will add a new row to the asynchronous output in the CreateNewOutputRows
method. In this method we can use the AddRow method to add a blank row as we
did while writing the code for the source component.
Second, we will calculate the required values. As the XfrInput_ProcessInputRow
method processes all the rows, we can add a bit more code here to aggregate the
required values in this process.
Finally, we need to pass the calculated values to the output columns in the output
buffer. However, we want to pass the aggregated values only when the end of
rowset has been reached, so we will override the ProcessInput method. And that’s
it. Let’s get started.
34. Write the following code as a separate subroutine in the ScriptMain class to
create the CreateNewOutputRows method. We are using the AddRow method
to add a blank row in the AsyncXfrOutputBuffer.
Public Overrides Sub CreateNewOutputRows()
AsyncXfrOutputBuffer.AddRow()
End Sub
35. Next, to calculate total sales and total bonuses, we will need two script variables.
So, write the following two lines at the top of the code after the Inherits
UserComponent line.
Dim TotalSales As Decimal = 0
Dim TotalBonus As Decimal = 0
And then add the following two lines to calculate summary totals for these

variables in the end of the XfrInput_ProcessInputRow method, but before the
End Sub statement. Refer to Figure 11-17 if in doubt.
TotalSales = TotalSales + Row.SalesAmount
TotalBonus = TotalBonus + Bonus
Note that in the TotalBonus calculation, the Bonus script variable has been used
instead of Row.BonusAmount. This is because Row.BonusAmount is an output
column and the output columns are created as write-only columns in the Script
component object model; hence, you cannot read values from output columns.
If you need to reuse the calculated values in your script, it is better to use an
intermediate script variable than to pass the value directly to the output column.
36. Last, write the following lines as a separate subroutine in the ScriptMain class:
Public Overrides Sub XfrInput_ProcessInput(ByVal Buffer As XfrInputBuffer)
While Buffer.NextRow
XfrInput_ProcessInputRow(Buffer)
End While

If Buffer.EndOfRowset Then
AsyncXfrOutputBuffer.TotalSalesAmount = TotalSales
AsyncXfrOutputBuffer.TotalBonusAmount = TotalBonus
AsyncXfrOutputBuffer.SetEndOfRowset()
End If
End Sub
524 Hands-On Microsoft SQL Server 2008 Integration Services
As you can see, the overridden ProcessInput method calls the ProcessInputRow
method to process all the rows for synchronous output and calculate totals for
sales and bonus to be used in the asynchronous output. Once the end of rowset is
reached, the aggregated total sales and total bonuses are written to the appropriate
output columns.
Figure 11-17 Code for adding an Asynchronous Output to the Script transformation
Chapter 11: Programming Integration Services 525

37. You can test the asynchronous output by adding a new Row Count
transformation into the data flow. Connect the AsyncXfrOutput to the Row
Count 1 transformation using the data flow path. Add a data viewer on this
new path as well as shown in Figure 11-18. You can use the varRecords variable
again in this transformation to complete the configuration of Row Count 1
transformation.
38. Execute the package and the TotalSalesAmount and TotalBonusAmount values
will be shown in the AsyncXfrOutput Data Viewer.
Script Component as a Destination
You are at the last part of the exercise, where you will configure the Script component
as a destination. Here we will write the two outputs—synchronous output and
asynchronous output—into the separate text files. We will write the synchronous
Figure 11-18 Data flow layout for Script component package
526 Hands-On Microsoft SQL Server 2008 Integration Services
output to a comma-delimited text file and the asynchronous output to a nonstandard
text file. In both these cases, configuring the Script component as a destination is not
much different than configuring it as a source, though there is one very obvious but
minor difference; we will use StreamWriter method to write into the text file instead of
StreamReader to read from the text file. You will find the rest of the code quite similar
to what you used in the script source.
Comma-Delimited Text File Destination
Here, you will write the synchronous output to a comma-delimited text file.
39. Delete both the row count transformations. Drop a Script component below the
Script transformation component and choose Destination in the Select Script
Component Type dialog box. Rename it as Script Destination Component.
40. Join the Script transformation to the Script Destination component. As you join
them, an Input Output Selection dialog box will open. Choose SynchXfrOutput
in the Output field and click OK.
41. In this step, we will add a connection to the destination file that we will need
to use in our script. Right-click in the Connection Managers area and choose

the New File Connection option. In the File Connection Manager Editor,
select the Create File option in the Usage Type field, type C:\SSIS\RawFiles\
SalesWithBonus.txt in the File field, and click OK to create the File Connection
Manager.
42. Double-click the Script Destination Component to open the editor and change
the ScriptLanguage to Microsoft Visual Basic 2008.
43. Go to Input Columns page and select all the columns. Now go to Inputs and
Outputs page and rename the Input 0 to DstSynchInput. Expand it to see all the
columns listed that you selected in the Input columns.
44. Go to Connection Managers page and click Add. Select SalesWithBonus.txt in
the Connection Manager field and type SalesWithBonus in the Name field. The
name specified here will be used in our script to refer to this connection manager.
45. Click the Edit Script button in the Script page to open the VSTA IDE. I would
suggest you to spend few minutes to see the auto-generated code. The auto-
generated code in both the BufferWrapper.vb and the ComponentWrapper.vb
project items is familiar and very much expected, and there are no surprises.
46. First of all, as we are going to use StreamWriter to write into a text file, we will
need to add an IO namespace. Add the following line in the Imports statements.
Imports System.IO
47. Then type the following lines of code to declare variables required for the connection
string, the StreamWriter instance, and the column delimiter, for which we are
using a comma.
Chapter 11: Programming Integration Services 527
Dim cmSalesWithBonus As String
Private textWriter As StreamWriter
Private columnDelimiter As String = ","
48. Next add an acquire connection method to establish a connection to the
SalesWithBonus Connection Manager. The code is similar to what you’ve already
used in the Script Source component.
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

Dim connMgr As IDTSConnectionManager100 = Me.Connections.SalesWithBonus
cmSalesWithBonus = CType(connMgr.AcquireConnection(Nothing), String)
End Sub
49. In this step, we will perform one-time operations to write data to the text file in
the PreExecute phase. We have two items that need to be implemented one time,
one to initialize the StreamWriter and the other to write column headings in the
file. Type the following lines in the PreExecute subroutine:
textWriter = New StreamWriter(cmSalesWithBonus, False)
With textWriter
.Write("FirstName")
.Write(columnDelimiter)
.Write("LastName")
.Write(columnDelimiter)
.Write("Title")
.Write(columnDelimiter)
.Write("SalesAmount")
.Write(columnDelimiter)
.Write("IsBonusPaid")
.Write(columnDelimiter)
.Write("BonusAmount")
.WriteLine()
End With
The first line initializes the textWriter to open a connection to the SalesWithBonus
.txt file. The false argument lets the textWriter overwrite the contents in the file each
time it is invoked. The remaining lines write the column headings in the text file
using the Write and WriteLine methods.
50. As we have instantiated the StreamWriter, we must close it. We will close the
StreamWriter in the PostExecute method.
textWriter.Close()
51. Now type the following code into the main DstSynchInput_ProcessInputRow

method that will be repeatedly called for each row.
With textWriter
If Not Row.FirstName_IsNull Then
.Write(Row.FirstName)
End If
.Write(columnDelimiter)