Ultimate Guide for Source Transformation in IICS

---

1) What Is a Source Transformation in IICS?

Source transformation in IICS (Informatica Intelligent Cloud Services (IICS is an connected and active transformation and is the starting point of any data integration mapping. It defines where your data comes from and how it enters your data flow. Whether you’re pulling data from relational databases, flat files, cloud applications, or file lists, the Source transformation acts as the entry gate for your pipeline.

Lets walkthrough each steps of Source Transformation in details

1. Connection

This dropdown lets you choose the source connection through which IICS will read data. It may be a database connection (Oracle, SQL Server, MySQL), a flat file connection, or a cloud application connector.
You can also:

• View connection details
• Create a New Connection
• Use New Parameter to parameterize the connection at runtime

2. Source Type

It Defines what type of source structure you will use. These options enable flexibility in how source data is retrieved. Options include as below:

• Single Object → A single table, view, or file
• Multiple Objects → Allows joining multiple related database tables
• Query → Lets you extract data through a SQL query
• Parameter → Fully dynamic source chosen at runtime

3. Object

This field specifies which source object should be read-for example:

• A database table
• A database view
• A flat file
• A file list

When using database sources, IICS imports column metadata automatically. You can modify or restore this metadata from the Fields tab.

4. Filter (Configure…)

The Source transformation allows filtering directly at the source using SQL syntax (e.g., WHERE clause). This pushes the filtering logic into the database engine, improving performance.

Example use cases:

• Filtering active customers
• Fetching records based on date range

This is the recommended alternative to using a Filter transformation

5. Sort (Configure…)

Sorting can also be pushed to the source system. When configured:

• IICS adds an ORDER BY clause in the underlying SQL
• Supports single or multiple sorting columns
  

6. Select Distinct Rows Only

Enabling this option removes duplicates by applying SELECT DISTINCT at the source. This ensures only unique rows enter the mapping pipeline.

7. Advanced Section

• 🔸 Tracing Level
  • Controls the level of logging information written to session logs, This option is helpful for debugging.
    Terse, Normal (default),Verbose Initialization and Verbose Data
    
• 🔸 Pre SQL
  You can provide SQL statements that should run before data extraction begins.
  
• 🔸 Post SQL
  SQL command to run against the source after writing data to the target.
  
• 🔸SQL Query
  SQL query to override the default query that ,Data Integration uses to read data from the source. You can enter an SQL statement supported by the source database.
  

Fields Tab

1️⃣ Add or Remove Fields

• ➕This plus icon allows you to manually add a new field into the Source transformation.
• The trash icon lets you remove fields you don’t want to use in your mapping.
• Sort option changes how the fields appear in the UI:Native order, Ascending, Descending
  
• Options Menu:
  Edit Metadata allows you to modify the properties of ANY field, including: Data type, Precision, Scale, Name, Nullable flag
• Find Bar:
  This allows you to quickly search through field names — very helpful when working with large schemas.

Partitions

Partitioning in IICS allows you to divide source data into multiple segments so that the integration service can process them in parallel. This improves performance and scalability for large datasets. The type of partitioning you can use depends on the source system and its capabilities.

Instead of reading and transforming all rows sequentially, partitioning enables parallel processing, making ETL tasks faster and more efficient-especially for high-volume data.

Partition Types by Source

• Key Range Partitioning:
  • Key Range supports For relational databases like Oracle, SQL Server, MySQL, etc.,
  • Splits data based on ranges of values in a key column. Ideal for numeric or sequential keys.

• Fixed Partitioning:
  Divides the file into chunks based on byte size or row count.

Pass through:
The mapping task processes data without redistributing rows among partitions. All rows in a single partition stay in the partition. Choose pass-through partitioning when you want to create additional partitions to improve performance, but do not want to change the distribution of data across partitions.

You can use this method for sources such as Amazon S3, Netezza, and Teradata.