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

MORE INFORMATION

To add an incremental counter by using a Script component in a Data Flow task, follow these steps:

1. On the Control Flow tab, double-click the Data Flow task that you created. The Data Flow tab appears.
2. In the Toolbox window, double-click Script Component.
3. In the Select Script Component Type dialog box, click Transformation, and then click OK to preconfigure the component as a transformation.
4. To add a connector from the data flow source to the Script component, right-click the data flow source, and then click Add path.
5. To add a connector from the Script component to the data flow destination, right-click the Script component, and then click Add path.
6. Double-click the Script component. The Script Transformation Editor dialog box appears.
7. Click Inputs and Outputs in the left pane, double-click Output 0 in the middle pane, and then click Output Columns.
8. Click Add Column, and then add a new column that is named mycount.
9. 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

10. In the Script Transformation Editor dialog box, click OK.

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.