Query scalar value

The Query scalar value activity is used to execute SQL commands over an established database connection and query a scalar value as a result.

This can be used to select the number of records in a table, query for a specific value, etc.

Table of contents


The Query scalar value activity can execute commands on a connected database during package execution to query for a scalar value. This allows you to get information from the database to use during the masking process (e.g. to get the record count of a table).

To use it, drag it to an already created Connect to database activity. Enter the name of the created database connection, and define the command text by clicking the field next to it. This will open a Code Editor dialog where you can write and validate your SQL commands before they are executed during package execution. Since we have to query the results, this activity generates an IDataReader reader that we can use to access our data, and the name of the reader can also be changed to simplify the usage of this activity.

Additional options, like a CommandType and CommandTimeout can also be set if necessary. CommandBehaviour can be set to control what data will be queried and how the command will behave and execute. Also, you might want to set the Result to a variable where you want to store the queried value.


In this example we will use the Query scalar value activity to get the record count of the table created in the Execute command example. We will use a Connect to database activity to create a connection to the database. Once the connection is created, place the Query scalar value activity inside the Connect to database activity. After setting the name of the connection as BdxDemo to match the created connection name, we can enter the command text by clicking the field next to it.

Code Editor dialog Figure 1: Code Editor dialog

This opens a separate Code Editor dialog where we can enter our SQL commands. After clicking on the Validate button to make sure the command is valid, the command is finished and ready to be executed when the package starts.

Next, we will go to the Code.cs class to create a variable where we will store the record count. The Code class looks like this:

public static class Code
    public static int DemoCount;

We will then return to the workflow, select the Query scalar value activity and change its Result property to Code.DemoCount. We can then place a Write log activity to write out the value of the variable after the Connect to database activity.

Processing a query scalar value Figure 2: Processing a query scalar value


Property group Property name Description Example
Input properties CommandText Text of the database command. SELECT COUNT(*) FROM DemoTable;
CommandTimeout Database command timeout in seconds. 3000
CommandType Type of the database command. StoredProcedure or TableDirect or Text
Connection Database connection used to execute database command. BdxDemo
Parameters Collection of database parameters. Use pop-up window to set parameters.
Misc DisplayName Display name of the activity in the workflow. Query scalar value
Result Contains the masking definition object. Used to get the result and save it to a variable. -