Microsoft KB Archive/908460

= How to add an incremental counter in a SQL Server 2005 Integration Services package by using a Script component in a Data Flow task =

Article ID: 908460

Article Last Modified on 3/11/2006

-

APPLIES TO


 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Standard Edition

-





INTRODUCTION
When you create a Microsoft SQL Server 2005 Integration Services (SSIS) package by using Business Intelligence Development Studio, you can add an incremental counter at any point of a data flow by using a Script component in a Data Flow task.

For example, you may want to have an incremental counter inserted per row after a data source. Then, you can record and identify the count of the rows that are successfully loaded and redirect the rows that are not loaded. If you are only interested in a final count, you can use a Row Count transformation to add a counter. The difference between using a Script component to add a counter and using a Row Count transformation to add a counter is the following:
 * A Script component adds an incremental value to your data flow as a new column value in each row.
 * A Row Count transformation adds and updates the current counter value to a single user-defined variable.



MORE INFORMATION
To add an incremental counter by using a Script component in a Data Flow task, follow these steps:  On the Control Flow tab, double-click the Data Flow task that you created. The Data Flow tab appears. In the Toolbox window, double-click Script Component. In the Select Script Component Type dialog box, click Transformation, and then click OK to preconfigure the component as a transformation. To add a connector from the data flow source to the Script component, right-click the data flow source, and then click Add path. To add a connector from the Script component to the data flow destination, right-click the Script component, and then click Add path. Double-click the Script component. The Script Transformation Editor dialog box appears. Click Inputs and Outputs in the left pane, double-click Output 0 in the middle pane, and then click Output Columns.</li> Click Add Column, and then add a new column that is named mycount.</li>  Click Script in the left pane, and then click Design Script. When a new Microsoft Visual Studio for Applications window appears, paste the following code in the window. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain Inherits UserComponent Dim counter As Integer = 0 ' User code

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) '       ' Add your code here Row.mycount = counter  ' User code counter = counter + 1  ' User code

'   End Sub

End Class </li> In the Script Transformation Editor dialog box, click OK.</li></ol>

When you complete these steps, you can use the mycount column as a column in the data flow destination. You can extend the functionality of the sample code to add more information. For example, you can add the ExecutionInstanceGuid system variable or a time and date stamp.

Note The mycount column that you added reflects the order in which the Script component processed the rows. The mycount column does not necessarily reflect the actual row number in the original source. This is especially true when you insert the Script component after other transformations in the data flow, because all the data flow objects before the Script component may filter data or redirect rows.

<div class="references_section">