The SQL transformation processes SQL queries midstream in a pipeline. The transformation processes external SQL scripts or SQL queries that you create in an SQL editor. You can pass the database connection information to the SQL transformation as input data at run time.
This chapter provides two examples that illustrate SQL transformation functionality. You use the examples in this chapter to create and execute dynamic SQL queries and to connect dynamically to databases. The chapter provides sample data and descriptions of the transformations that you can include in mappings.
¨ Creating a dynamic SQL query to update a database. The dynamic query update example shows how update product prices in a table based on a price code received from a source file.
¨ Configuring a dynamic database connection. The dynamic connection example shows how to connect to different databases based on the value of a customer location in a source row.
Dynamic Update: SQL transformation to generate SQL queries based on the value of a column in a source file.
You have a database table that contains product prices. You need to update the prices from a transaction file. Each transaction row updates the wholesale, retail, or manufacturing prices in the database based on a price code column.
The source file is a flat file. You can configure an Expression transformation to return the column names to update based on the value of a price code column in each source row. The Expression transformation passes the column names to the SQL transformation. The SQL transformation runs a dynamic SQL query that updates columns in the Prod_Cost table based on the column names it receives.
The SQL transformation returns database errors to the Error_File target.
How the Expression transformation passes column names to the SQL transformation:
The mapping contains the following components:
¨ PPrices source definition. The PPrices flat file contains a product ID, package price, unit price, and price code. The price code defines whether the package price and unit price are wholesale, retail, or manufactured prices.
¨ Error_File flat file target definition. The target contains the Datastring field that receives database errors from the SQL transformation.
¨ Exp_Dynamic_Expression transformation. The Expression transformation defines which Prod_Cost column names to update based on the value of the PriceCode column. It returns the column names in the UnitPrice_Query and PkgPrice_Query ports.
¨ SQL_Dynamic_Query transformation. The SQL transformation has a dynamic SQL query to update a UnitPrice column and a PkgPrice column in the Prod_Cost table. It updates the columns named in the UnitPrice_Query and PkgPrice_Query columns.
Note: The mapping does not contain a relational table definition for the Prod_Cost table. The SQL transformation has a static connection to the database that contains the Prod_Cost table. The transformation generates the SQL statements to update the unit prices and package prices in the table.
* Defining the Source File
* Creating a Target Definition
* Creating the Database Table
Configuring the Expression Transformation:
The Expression transformation has an input/output port for each source column. It passes column names to the SQL transformation based on the value of the PriceCode column.
Defining the SQL Transformation:
The SQL transformation executes a dynamic SQL query that inserts the unit price and package price data into the Prod_Cost table. The SQL transformation receives the column names to update in the UnitPrice_Query and PkgPrice_Query ports.
When you create an SQL transformation, you define the transformation mode, the database type, and the type of connection. You cannot change the mode or connection type after you create the transformation.
Create an SQL transformation with the following properties:
¨ Query Mode. The SQL transformation executes dynamic SQL queries.
¨ Static Connection. The SQL transformation connects once to the database with the connection object you define in the Workflow Manager.
SQL transformation Ports
The dynamic connection SQL transformation example shows how to dynamically connect to a database based on source file data. You have a customer database for the Sweden, India, United States, United Kingdom, and Canada. You need to insert customer data from a transaction file into a database based on where the customer is located.
The Expression transformation returns a database connection object name based on the value of the location column. The Expression transformation passes a connection object name to the SQL transformation LogicalConnectionObject port. The SQL transformation connects to the database based on value of the LogicalConnectionObject column.
The Expression transformation and the SQL transformation.
The mapping contains the following components:
¨ Customer source definition. A flat file source definition that includes customer information. The customer location determines which database the SQL transformation connects to when it inserts the customer data.
¨ Error_File target definition. The target contains a Datastring field that receives database errors from the SQL transformation.
¨ Exp_Dynamic_Connection transformation. The Expression transformation defines which database to connect to based on the value of the Location column. The Expression transformation returns the connection object name in the Connection port. The connection object is a database connection defined in the Workflow Manager.
¨ SQL_Dynamic_Connection transformation. The SQL transformation receives a connection object name in the LogicalConnectionPort. It connects to the database and inserts the customer data in the database.Your comments are always welcome here …. Skip to top
MCTS – BI