The Database query step’s RowCount property does not reflect the number of rows returned by the query. It may contain a value such as -1, or 0 (zero) in place of the expected number of rows.
Please follow the below link for a similar symptom encountered with the RowsProcessed property of the Filter Data:
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. For example, the ODBC driver may use the value -1 to indicate rows and 0 (zero) to indicate no rows.
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:
- Task variable to store the counter
- Run VBScript step to initialise the counter to 0 (zero)
- Run VBScript step to increment the counter value
Continue below for instructions on how to add a counter logic to a task.
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.
- Under the Environment tab of the task browser, right-click the Variables node and select New. This will display the variable properties.
- Within the General tab name the variable RowCount.
- Within the Details tab tick the Parameter box and set the Scope to Task Instance.
2.0 Add a 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.
- Under the Tools tab of the task browser, locate the Run VBScript icon.
- Drag the tool onto the ‘Task Planner’. This will automatically open the tool for configuration.
- Within the General tab provide a name for the step, for example Initialise Counter.
- Within the Main tab key in the following: [Variable] = 0
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 Database Query step. It will be writing the number of rows to the variable.
- Create a second VB Script step and provide an appropriate name, for example Increment Counter.
- Main tab. Key in the following: [Variable] = [Variable] + 1
- 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.
- Between the double quotes of the LogInfo property enter the text: RowCount:
- Complete the script line by entering the following: & [Variable]The completed script should now look as follows:
- Click Check Syntax to verify that the script is correct.
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 Database Query and consumes a recordset. It executes once per row and will write the number of 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 Database Query step.