Skip to content
+44 (0) 330 99 88 700
Sign In Register
Ready to Talk?

Filter Data: RowsProcessed property does not reflect the number of filtered rows.

Table of Contents

 Symptom #

The Filter Data step’s RowsProcessed property does not reflect the number of filtered rows.

Please follow the below link for a similar symptom encountered with the RowCount property of the Database Query:

Database Query RowCount Property

Cause #

Many ODBC drivers and OLEDB provider do not return a row count to the task environment. Instead, they return a value to indicate the presences or otherwise of rows.

Solution #

The solution is to add a counter logic to the task. The task design to implement the custom counter logic will have the following features:

  1. Task variable to store the counter
  2. Run VB Script step to initialise the counter to 0 (zero)
  3. Run VB Script step to increment the counter value

Continue below for instructions on how to add a counter logic to a task.

Design Implementation #

1.0 Add a task variable to store the counter

A parameter type variable must first be created, this will be utilised by both VB Script steps.

  1. Under the Environment tab of the task browser, right-click the Variables node and select New. This will display the variable properties.
  2. Within the General tab name the variable RowCount.
  3. Within the Details tab tick the Parameter box and set the Scope to Task Instance.
    2

2.0 Run VB Script step to initialise the counter to 0 (zero)

This VB Script step must be placed at the beginning of the task. It’s purpose is to write a value of 0 (zero) to the variable.

  1. Under the Tools tab of the task browser, locate the Run VBScript tool.
  2. Drag the tool onto the ‘Task Planner’. This will automatically open the General tab of the tool.
  3. Within the General tab provide a name for the step, for example Initialise Counter.
  4. Within the Main tab key in the following: [Variable] = 0
    Capture 1

3.0 Run VB Script step to increment the counter value

This VB Script step will consume a recordset and must be placed after the Filter Data step. It will be writing the number of rows to the variable.

  1. Create a second VB Script step and provide an appropriate name, for example Increment Counter.
  2. Within the Main tab key in the following: [Variable] = [Variable] + 1
    Capture 2
  3. Enter a carriage return to begin a new line. Under the Environment tab of the task browser and This Step selected in the Scope field, drag & drop the property LogInfo in to the script.Capture3
  4. Between the double quotes of the LogInfo property enter the text: RowCount:
  5. Complete the script line by entering the following, drag & drop the variable where required: & [Variable]
    The completed script should now look as follows:
    Capture 4(1)
  6. Click Check Syntax to verify that the script is correct.

Summary

A parameter type variable is created. The first VB Script step is placed at the beginning of the task and writes a value of 0 to the variable. This will initialise the counter ensuring it starts at 0 when it begins counting the rows.

The second VB Script step is the counter, it is placed after the Filter Data step and consumes a recordset. It executes once per row and will write the number of filtered rows to the same variable in increments of 1 with each successful execution.

Below is an example of a task which utilises the counter in-line with a Filter Data step.

DF1

Was this article useful?