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

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.

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.

For more information, see the following topics in SQL Server 2005 Books Online:

• SQL Server Integration Services (SSIS)
• Script component
• Configuring the Script component
• Coding and testing the Script component
• Row Count transformation