SQLServer Query

Use this activity to execute a simple or a complex SQL Query on a Microsoft SQL Server database. The SQLServer Query activity returns information in the form of rows.

Settings

The Settings tab has the following fields:

Field Description
Connection Name of the Microsoft SQL Server database connection from which to retrieve information.

Input Settings

Field Description
Query An SQL statement used to query the database. The query can be a simple query or a complex query. A complex query has nested SQL statements. Prepared SQL queries can be constructed by using substitution variables (or substitution parameters) of the form ?<fieldname> in the query statement. For example select lastname, firstname, title from employees where lastname='?lastname';
Each substitution variable identifies an input parameter whose mapped value is substituted into the substitution variable at runtime. The substitution variable can be reused for the same input parameter elsewhere in the query. Input parameters used in the WHERE clause, and output parameters used in the SELECT clause, and their corresponding type information is automatically fetched from the database using the selected connection for the entered query. Input and output fields on the Input and Output tabs of the activity are also automatically generated.
Note: Be sure to include the semicolon (;) at the end of the query. This activity expects a query to end with a semicolon to indicate the end of the query. A missing semicolon at the end of the query results in the query hanging.
Following are some examples of simple and complex queries:

  • Simple query example: 

    SELECT * FROM employees;

    For the above query, the output fields are generated from the columns of the table employees. 

    SELECT lastname, firstname 
    FROM employees 
    WHERE country = ?country and lastname like '%ll%'
    ORDER BY lastname DESC, firstname ASC;

    For the above query, output fields are generated for lastname and firstname and input fields are generated for country. Also the mapped value for the field country is substituted into the substitution variable ?country at runtime.

  • Nested query example: 

    SELECT categoryID, productName, MAX(unitprice)
    FROM products A
    WHERE unitprice = (
            SELECT
              MAX(unitprice)
            FROM 
              products B
            WHERE 
              B.categoryID = A.categoryID)
    GROUP BY categoryID, productName
    HAVING MAX(unitprice) > 100;
Manually Configure Metadata If you see an error for a valid SQL statement in the Query field, set the Manually Configure Metadata field to True to fetch table metadata manually using simple SELECT statement. For more information, see Manually Configuring Metadata.
Fields The grid is provided for informational purposes only.

Input

This tab contains the input schema. The fields that were selected on the Input Settings tab will be available in the schema. You can either hard code their values or map them to a field from the output of a preceding activity in the flow using the Mapper.

Output

This tab displays the activity output schema in a tree structure format. The output of an activity is displayed for informational purposes only and cannot be modified or altered. The information in this schema varies depending on the fields that you selected on the Input Settings tab.

The properties that are displayed on the Output tab schema correspond to the output of this activity and can be used as input by subsequent activities in the flow.

Related concepts
Related reference