Get records from a database table

Crete a database query to get records from a database table.

  1. On the properties panel of the Data access activity, click Configure.
    The Data access configuration dialog box is displayed.
  2. On the Database connection list, select the process or server variable to specify the database connection string that holds the details of the relational database.

    By default, Tables is selected and all tables within the selected database are available on the Tables list.

  3. On the Tables list, select the table to get records. All columns within the selected table appear.
    Note By default, the Get option is selected.
  4. On the Available columns list, select the columns to retrieve the records. For example, for a Customer table, you can get the records for the ContactName, Address, and Phone columns.
  5. To get the records for the selected columns, select one of the following options on the Return list:

    Option Description

    Multiple rows

    Retrieves multiple rows.

    1. Map the Return resultset to output parameter to the dynamic complex variable.

    2. Map the Return row count to output parameter to the long variable.

      See Create process variables.

    Single row

    Returns a single row. (Default: Single row)

    Use aggregate

    Retrieves the minimum and maximum values and the row count.

    1. To retrieve the minimum value of a column, select Min value for the column and on the Output min value to list, select the variable that holds the minimum value.

    2. To retrieve the maximum value of a column, select the Max value for that column and Output max value to list, select the variable that holds the maximum value.

    3. To get the count of rows within a table, select Count rows and on the Return row count to list select the variable that holds the count of rows.

    Note You must map all the output parameters with the relevant variables. Note that ODBC and OLEDB do not support xml data type.

  6. To specify the fields and sort order in your result:
    1. Select the columns.
    2. Select the Sort order as Ascending or Descending against each column.
  7. To reorder the columns, select the columns, and then use the  icons.
  8. Configure your query based on specific conditions. For example, you can search for records with similar values. Conditions are helpful and enhance your search result when you do not know the exact value of an item.

    Example 1

    To retrieve the records of ContactName, Address and Phone for the cities of London and Berlin:

    1. Click under Search criteria.

    2. Specify the search criteria as Column: City(String), Operator: Equal To, Criteria: Berlin..

    3. Click Add.

    Example 2

    To retrieve records of customers whose surname starts with 'M', specify the condition as: Column: LastName, Operator: Begins with, Criteria: "M".

    Example 3

    To retrieve the records of FirstName, LastName, Date of Birth and Address for first names starting with 'A' such as Andrew, Avril, and Anthony; first names containing the letters 'or' such as Trevor, Florence, and Victoria; or first names ending in 'a' such as Martina, Katrina, and Sabrina, specify the following condition:

    1. Click .

    2. Specify the search criteria as Column: ContactName(String), Operator: begins with, Criteria: A, Logic: OR.

    3. Click Add.

    4. Specify another search criteria as Operator: ends with, Criteria: A.

    5. Click Add.

  9. Click OK.