/ / SQL Transformation in Informatica
Informatica

SQL Transformation in Informatica

Edit
SQL transformation process SQL queries midstream in a pipeline. We can insert, update, delete and retrieve rows from a database.
The following SQL statements can be used in the SQL transformation.
  • Data Definition Statements (CREATE, ALTER, DROP, TRUNCATE, RENAME)
  • Data Manipulation statements (INSERT, UPDATE, DELETE, MERGE)
  • Data Retrieval Statement (SELECT)
  • Data Control Language Statements (GRANT, REVOKE)
  • Transaction Control Statements (COMMIT, ROLLBACK)
Configuring SQL Transformation

Query Mode: We can define and execute query in query editor.
Script Mode: The SQL transformation runs ANSI SQL scripts that are externally located. You pass a script name to the transformation with each input row.
Active/Passive: By default SQL is a active transformation, we can configure it as passive transformation.
Data Base Type: Type of database SQL connects.
Connection Type: Pass database connection information to the SQL transformation or use a connection object.

Implementing SQL Transformation
  • Import source DEPT table.
  • Create target table.
  • Drag both source and target tables in to the mapping designer. 
  • Go to the transformations->Create -> Select SQL transformation->OK. 
  • Edit the SQL transformation and go to the SQL ports tab.
  • Add new fields as like below.
  • Select output ports also.  
  • Write SQL query on SQL query editor.   
     
  • Connect source qualifier ports to SQL transformation input ports.
  • Connect from SQL transformation output ports to target table. 
  • Save the mapping.
  • Create workflow and session.
  • Run the mapping.
Powered by Blogger.