A Source transformation in IICS-IDMC, extracts data from a source table or a file.
Usage:
▹ Source Transformation is used to read data from a source table or a file.
▹ You can use one or more Source transformations in a mapping.
▹ If you use two Source transformations in a mapping, you can use a Joiner transformation to join the data.
▹ If you use multiple Source transformations with the same structure, you can use a Union transformation to merge the data into a single pipeline.
▹ In source Transformation, we have options like single Object, Multiple objects, Query and Parameter as shown below.
When source is a Database source (single Object):
▹ Database sources include relational sources such as Oracle, MySQL, and Microsoft SQL Server. When you configure a Source transformation for a database source, you can use a single source table or multiple source tables
▹ When source is a table, instead of using filter and Sorter transformations, use the below options in source transformation as shown in below
- use filter (oracle syntax) – To filter the source Records
- use sorter – To order by the source Records
- use select distinct rows only – To remove the source duplicates
▹ When you use the above two properties in Source transformation, source will write a default SQL select query in the log and executes internally in source schema.
Source SQL Query Example:
Select distinct
ot_src_dev1.contacts.first_name,
ot_src_dev1.contacts.contact_id,
ot_src_dev1.contacts.last_name,
ot_src_dev1.contacts.email,
ot_src_dev1.contacts.phone,
ot_src_dev1.contacts.customer_id
from ot_src_dev1.contacts
where contacts.contact_id>50 and contacts.contact_id<100
order by ot_src_dev1.contacts. first_name
▹ You can Override the source by using SQL Override in Advanced properties in source.
▹ In SQL Override option, you can write any query whichever the source database supports as shown in the below.
Multiple Objects in Source (Join Homogeneous source tables):
Use Multiple Objects option in source transformation to join 2 or more homogeneous source tables as mentioned in the below steps. You can join related tables or specify a custom relationship.
▹ Select source Type as multiple objects.
▹ When these sources having PK and FK relationship in DB level already, click on the three dots, and select the first source which is having pk.
▹ And then, click on Add related objects.
Informatica will write the Inner join select statement in the log as below.
SQ instance [Source] SQL Query
[
Select
ot_src_dev1.orders. salesman_id,
ot_src_dev1.customers.customer_id,
ot_src_dev1.customers.name,
ot_src_dev1.customers.address,
ot_src_dev1.customers.website,
ot_src_dev1.customers.credit_limit,
ot_src_dev1.orders.order_id,
ot_src_dev1.orders.status,
ot_src_dev1.orders.order_date
from ot_src_dev1.orders, ot_src_dev1.customers
where “ot_src_dev1″.”orders”.”customer_id”=”ot_src_dev1″.”customers”.”customer_id”
and orders.salesman_id is not null
order by ot_src_dev1.orders.salesman_id
]
Custom relationships: To join 2 or more source tables (which are not having PK and FK relationship is database level)
You can create custom relationships to join objects in the same source system. To create a custom relationship, select a primary object, select another object from the source system, and then select a field from each source to use in the join condition. You must also specify the join type and join operator.
You can select one of the following join types:
▹ Inner join
▹ Left join
▹ Right join
To join 2 or more source tables (which are not having PK and FK relationship is database level) in source transformation, but having at least one common column with common data in between the tables, do the below steps.
a) After selecting Multiple objects in Source Transformation, Add the first source object by going the action menu
b) Go to action menu and select Add Related object and select Custom Relationship option.
c) Now select the second table, under Configure relationships, Select Primary Object Key and Related object key And Specify the Join Type (Inner join, Left join or Right Join) as per your requirement.Click on Add and Click on OK.
d) It will look like below. You can still Select the Filter and sort properties under the query option.
e)You can Still choose Advanced Relationship under the actions menu and can enter multiple joining conditions
For example, the following image shows a custom relationship that uses an inner join to join the EMPLOYEE and MANAGER database tables when the EMPLOYEE.E_MANAGERID and MANAGER.M_ID fields match:
Query option in Source: (Custom Query)
▹ Create a custom query when you want to use a database source that you cannot configure using the single- or multiple-object source options. You might create a custom query to perform a complicated join of multiple tables or to reduce the number of fields that enter the data flow in a very large source.
▹ When you use Query Option in source transformation, the source properties will be disabled
▹ We can use Query option under source type, and write your own simple or complex query (source connection database syntax). You can write Simple or complex join queries also.
Note: Change the datatypes in source fields as per your source tables. Otherwise mapping task will fail.
Note: Test the SQL statement you want to use on the source database before you create a custom query. Data Integration does not display specific error messages for invalid SQL statements.
When source is a Flat File:
▹ When source is a flat file, you will not see the properties under source properties. (FILTER, SORTER, DISTINCT option will not show)
▹ We must use FILTER, SORTER Transformations when source is a fiat file to Filter or sort the source data.
Leave a comment