SQL warehousing — SQL query source operators

The SQL query source operator uses an SQL SELECT statement to retrieve metadata for one or more columns either from a local or a remote database table. The operator functions as a source operator in a data flow.

The SQL query source operator in the data flow palette has one output port, but does not contain an input port. Therefore, you can use the SQL query source operator only as a source operator in your data flow. You can connect the output port of the operator to a table target, a file export, or a bulk load target operator.

The SQL statement that you specify in the SQL query source operator retrieves the metadata for the columns that are queried in the SELECT statement. The database metadata describes information, such as column name, length, and data type, that is inserted into the output port of the SQL query source operator. These columns are then mapped to the input port of a target operator. The SQL statement in the SQL query source operator is run against an SQL execution database or a remote database.

An SQL query source operator provides one distinct advantage over the table source operator. You can directly reuse a complex SQL SELECT statement that you might have already created in some other ETL tool. You can save time because you do not need to re-create the same complex SQL statement by using the various data flow operators in the Design Studio.

Restriction: The SQL query source operator does not use the database metadata that is available in the physical database model (.dbm file) of your data warehouse project. Therefore, the table and column information does not appear when you perform impact analysis on the data flow that uses the SQL query source operator.

Example

You created the following nested SELECT statement either manually or by using a query tool. This statement retrieves the employee data from the employee table of a SALARY database and displays employees whose salary exceeds the average salary of regular employees.

SELECT employee_type, last_name, first_name, pay_rate
FROM employee
WHERE pay_rate > (SELECT AVG(pay_rate) FROM employee WHERE 
employee.job_type = 'REGULAR')
ORDER BY last_name

To reuse this SELECT statement in the Design Studio, you can use the SQL query source operator. Copy this SELECT statement and paste it in the SELECT statement field of the operator. Make sure that you have an active connection to the SALARY database in the Data Source Explorer view and that the SQL execution database is set to the SALARY database.