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.
- On the Control Flow tab, double-click the Data Flow task that you created. The Data Flowtab 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.
- Click Add Column, and then add a new column that is named mycount.
- 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.
Public Class ScriptMain
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
- In the Script Transformation Editor dialog box, click OK.
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.
- SQL Server Integration Services (SSIS)
- Script component
- Configuring the Script component
- Coding and testing the Script component
- Row Count transformation