A DBMS SQL flow allows you to enter dialect-specific SQL SELECT statements for extracting data from a relational database. Dialect-specific SQL is the SQL specific to one particular relational database, such as ORACLE or MS SQL Server.
You can type in the SELECT statement, or import it from a pre-existing .sql file.
A DBMS SQL flow only contains a specialized SQL object where you select the DBMS adapter and connection, and one or more target objects. Synonyms for sources are neither required nor used. Tables must be identified using the same names as in the database.
While source transformations cannot be used, target transformations, functions, variables, validates, target properties, and record logging can be used. The procedures for using these capabilities are the same for DBMS SQL flows as for more typical data flows.
For information on these procedures, see the appropriate topics earlier in this chapter.
The following procedure uses the Microsoft SQL Server pubs.dbo.authors sample table, and the Microsoft SQL Server difference scalar function, which is not available in ANSI SQL.
An empty flow appears in the data flow workspace. The DBMS SQL Flow tab opens by default.
A DBMS SQL Object appears in the center of the workspace.
The Select Statement dialog box opens.
SELECT au_fname, au_lname FROM pubs.dbo.authors WHERE difference (au_lname, 'delay') > 3
Note: The SQL statement must be a SELECT statement that conforms to the dialect-specific requirements of the adapter.