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.
|Table of contents|
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.
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
|Property group||Property name||Description||Example|
|Input properties||CommandBehavior||Database command behavior.||
|CommandText||Text of the database command.||
|CommandTimeout||Database command timeout in seconds.||
|CommandType||Type of the database command.||
|Connection||Database connection used to execute database command.||
|Parameters||Collection of database parameters.||Use pop-up window to set parameters.|
|Misc||DisplayName||Display name of the activity in the workflow.||
|Result||Contains the masking definition object. It's a part of the masking infrastructure and should be ignored.||-|