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.

Usage

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.

Example

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.

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.

Figure 2: Processing a query scalar value

Properties