Process query results

The Process query results activity is used to execute SQL commands over an established database connection and query the results.

This is usually used when selecting records from the database and use them in the masking process.

Usage

The Process query results activity can execute commands on a connected database during package execution to query for database records. This allows you to get information from the database to use during the masking process (e.g. to enable or disable certain features before masking based on the number of records).

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.

Example

In this example we will use the Process query results activity to read the data from 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 Process query results 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 rename the data reader to bdxDemoReader to better match our database and query. The easiest way to use the data reader is to place a While activity inside it, and write bdxDemoReader.Read() as the condition. This will result in a loop where we can access every record the reader has access to. In this example we will simple write them to our log file using the Write log activity.

Figure 2: Processing query results

Properties