ETL CONCEPTS

FLAT FILE LOAD

Import Flat File Source Definition:

1. Connect and Open the folder where you want to import flat file definition. If it not exist in the folder.

2. Select Tools --> Source Analyser

3. Click on Source Menu --> Import from File

4. It will ask you File Path

Note: Change Files of types: All Files (*.*) to see all types of files.

4. Browse the File path and select the File.

5. Click 'OK'.

6. Flat File Import Wizard - Step 1 of 3 will appears.

• Choose the Flat File Type is eitherDelimited or Fixed Width.
• Enter a name for this source: EMPLOYEE
• Start Import at Row: 2. If you specify to start at row 2, the Flat File Wizard skips 1 row before reading.
• Select 'Import field names from first line', If you want to import the port name from the 1st row of the flat file.
• Click 'Next'

7. Flat File Import Wizard - Step 2 of 3 will appears.

• Select the Delimited: Comma (in my case). You can choose Tab, Semicolon, Comma, Space, Other (Character used to separate columns of data).
• Treat Consecutive Delimiters as One: If selected, the Flat File Wizard reads one or more consecutive column delimiters as one. Otherwise, the Flat File Wizard reads two consecutive delimiters as a null value.
• Treat Multiple Delimiters as AND: If selected, the Flat File Wizard reads a specified set of delimiters as one.
• Escape Character: Character immediately preceding a column delimiter character embedded in an unquoted string, or immediately preceding the quote character in a quoted string. When you specify an escape character, the Integration Service reads the delimiter character as a regular character. This is called escapingthe delimiter or quote character.
• Remove Escape Character From Data: This option is selected by default. Clear this option to include the escape character in the output string.
• Use Default Text Length: If selected, the Flat File Wizard uses the entered default text length for all string datatypes.
• Text Qualifier: Quote character that defines the boundaries of text strings. Choose No Quote, Single Quote, or Double Quotes. If you select a quote character, the Flat File Wizard ignores delimiters within pairs of quotes.
• Click 'Next'.

Note: Treat Consecutive Delimiters as One, Treat Multiple Delimiters as AND, Escape Character are optional.

8. Flat File Import Wizard - Step 3 of 3 will appears.

• Name: Port name that you want to appear for each column. If you select Import Field Names from First Line, the wizard reads the column names provided in the file instead.
• Datatype:Column datatype. Select Text, Numeric, or Datetime, and then enter the appropriate Length/Precision, Scale, and Width.

1. For numeric columns, Precision is the number of significant digits. The Flat File Wizard ignores the width for numeric columns in delimited files.
2. For Text columns, Precision is the maximum number of characters contained in the source field or the target field. The Flat File Wizard ignores the precision when reading text columns in or writing text columns to delimited files.
3. By default, the Flat File Wizard enters the same value for both Precision and Width. You can change the value of the precision or width, but the Flat File Wizard only lets you define the precision to be greater than or equal to the width.

Note: Only characters 0 to 9 are considered numeric. Columns that contain multibyte character set numbers, such as Japanese, are considered text.

• Click 'Finish'

9. Flat File is Imported.

Import Flat File Target Definition:
1. Select Tools --> Target Designer
2. Select Target from Menu --> 'Import from File' and follow step from 4 to 8.

INDIRECT LOAD

A file list is nothing but loading multiple source files of same structure using one instance in the mapping. If an organization collects data at several locations which you then want to move through the same session. We need to create a mapping to use multiple source files for one source instance.

To use multiple source files, you create a file containing the names and directories of each source file you want the Integration Service to use. This file is referred to as a file list.

When we configure the session properties, enter the file name of the file list in the Source Filename field, enter the location of the file list in the Source File Directory field and Source filetype as Indirect. When the session starts, the Integration Service reads the file list, then locates and reads the first file source in the list. After the Integration Service reads the first file, it locates and reads the next file in the list.

The Integration Service writes the path and name of the file list to the session log. If the Integration Service encounters an error while accessing a source file, it logs the error in the session log and stops the session.

Note: When you use a file list and the session performs incremental aggregation, the Integration Service performs incremental aggregation across all listed source files.

Creating the File List

The file list contains the names of all the source files you want the Integration Service to use for the source instance in the session. Create the file list in an editor appropriate to the Integration Service platform and save it as a text file.

Use the following rules and guidelines when you create the file list:

• Each file in the list must use the user-defined code page configured in the source definition.
• Each file in the file list must share the same file properties as configured in the source definition or as entered for the source instance in the session property sheet.
• Enter one file name or one path and file name on a line. If you do not specify a path for a file, the Integration Service assumes the file is in the same directory as the file list.
• Each path must be local to the Integration Service node.

Example shows a valid file list created for an Integration Service on Windows

C:\Informatica\SourceFileDir\department_list.dat

d:\data\department_10.dat

e:\data\department_20.dat

f:\data\department_30.dat

After you create the file list, place it in a directory local to the Integration Service

Configuring a Session to Use a File List

Once you create a file list for multiple source files, you can configure the session to access those files.

1. Open the session properties.

2. Click the Mapping tab and open the Transformations view.

3. Click the Properties settings in the Sources node.

4. In the Source Filetype field, choose 'Indirect'.

5. In the Source Filename field, replace the file name with the name of the file list.

6. Click Apply and OK.

MAPPING PARAMETERS & VARIABLES




Use mapping parameters and variables to make mappings more flexible. Mapping parameters and variables represent values in mappings and mapplets. If you declare mapping parameters and variables in a mapping, you can reuse a mapping by altering the parameter and variable values of the mapping in the session. This can reduce the overhead of creating multiple mappings when only certain attributes of a mapping need to be changed.

To use a mapping parameter or variable in a mapping or mapplet, first we need to declare them in each mapping or mapplet. Then you can define a value for those mapping parameter or mapping variable before run the session.

Uses of Mapping Parameter:

• A mapping parameter represents a constant value that you can define before running a session. A mapping parameter retains the same value throughout the entire session.
• A mapping parameter cannot be change will session is using. It will retain the same values throughout the session.
• If mapping or mapplet is reusable then you change defines different values at parameter file.
• When you use a mapping parameter, you declare and use the parameter in a mapping or mapplet. Then define the value of the parameter in a parameter file. The Integration Service evaluates all references to the parameter to that value.

Mapping parameters and variables can be used in below transformations:

• Source qualifier
• Filter
• Expression
• User-Defined Join
• Router
• Update strategy
• Lookup override

Uses of Mapping Variable:

Unlike a mapping parameter, a mapping variable represents a value that can change through the session.

The Integration Service saves the value of a mapping variable to the repository at the end of each successful session run and uses that value the next time you run the session.

A mapping variable can change dynamically 'N' no of the throughout the session.

Use a variable function in the mapping to change the value of the variable.

At the beginning of a session, the Integration Service evaluates references to a variable to determine the start value. At the end of a successful session, the Integration Service saves the final value of the variable to the repository. The next time you run the session, the Integration Service evaluates references to the variable to the saved value. To override the saved value, define the start value of the variable in a parameter file or assign a value in the pre-session variable assignment in the session properties.

Mapping parameters and variables can be used in below transformations:

• Filter
• Expression
• Router
• Update strategy

Initial and Default Values

When we declare a mapping parameter or variable in a mapping or a mapplet, we can enter an initial value. The Integration Service uses the configured initial value for a mapping parameter when the parameter is not defined in the parameter file. Similarly, the Integration Service uses the configured initial value for a mapping variable when the variable value is not defined in the parameter file, and there is no saved variable value in the repository.

When the Integration Service needs an initial value, and we did not declare an initial value for the parameter or variable, the Integration Service uses a default value based on the datatype of the parameter or variable.

The following table lists the default values the Integration Service uses for different types of data:

Data	Default Value
String	Empty string.
Numeric	0
Datetime	1/1/1753 A.D. or 1/1/1 when the Integration Service is configured for compatibility with 4.0.

Using String Parameters and Variables

For example, we might use a parameter named $$State in the filter for a Source Qualifier transformation to extract rows for a particular state:

            STATE = ‘$$State’

During the session, the Integration Service replaces the parameter with a string. If $$State is defined as MD in the parameter file, the Integration Service replaces the parameter as follows:

            STATE = ‘MD’

You can perform a similar filter in the Filter transformation using the PowerCenter transformation language as follows:

            STATE = $$State

If you enclose the parameter in single quotes in the Filter transformation, the Integration Service reads it as the string literal “$$State” instead of replacing the parameter with “MD.”

Variable Values

The Integration Service holds two different values for a mapping variable during a session run: 

1. Start value of a mapping variable
2. Current value of a mapping variable

The current value of a mapping variable changes as the session progresses. To use the current value of a mapping variable within the mapping or in another transformation, create the following expression with the SETVARIABLE function:

SETVARIABLE($$MAPVAR,NULL)

At the end of a successful session, the Integration Service saves the final current value of a mapping variable to the repository.

Start Value:

The start value is the value of the variable at the start of the session. The start value could be a value defined in the parameter file for the variable, a value assigned in the pre-session variable assignment, a value saved in the repository from the previous run of the session, a user defined initial value for the variable, or the default value based on the variable datatype. The Integration Service looks for the start value in the following order: 

1. Value in parameter file
2. Value in pre-session variable assignment
3. Value saved in the repository
4. Initial value
5. Datatype default value

For example, you create a mapping variable in a mapping or mapplet and enter an initial value, but you do not define a value for the variable in a parameter file. The first time the Integration Service runs the session, it evaluates the start value of the variable to the configured initial value. The next time the session runs, the Integration Service evaluates the start value of the variable to the value saved in the repository. If you want to override the value saved in the repository before running a session, you need to define a value for the variable in a parameter file. When you define a mapping variable in the parameter file, the Integration Service uses this value instead of the value saved in the repository or the configured initial value for the variable. When you use a mapping variable ('$$MAPVAR') in an expression, the expression always returns the start value of the mapping variable. If the start value of MAPVAR is 0, then $$MAPVAR returns 0.

Current Value

Variable Datatype and Aggregation Type

The current value is the value of the variable as the session progresses. When a session starts, the current value of a variable is the same as the start value. As the session progresses, the Integration Service calculates the current value using a variable function that you set for the variable. Unlike the start value of a mapping variable, the current value can change as the Integration Service evaluates the current value of a variable as each row passes through the mapping. The final current value for a variable is saved to the repository at the end of a successful session. When a session fails to complete, the Integration Service does not update the value of the variable in the repository. The Integration Service states the value saved to the repository for each mapping variable in the session log.

When you declare a mapping variable in a mapping, you need to configure the datatype and aggregation type for the variable.

The datatype you choose for a mapping variable allows the Integration Service to pick an appropriate default value for the mapping variable. The default is used as the start value of a mapping variable when there is no value defined for a variable in the parameter file, in the repository, and there is no user defined initial value.

The Integration Service uses the aggregate type of a mapping variable to determine the final current value of the mapping variable. When you have a pipeline with multiple partitions, the Integration Service combines the variable value from each partition and saves the final current variable value into the repository.

You can create a variable with the following aggregation types:

• Count: Integer and small integer datatypes only.
• Max: All transformation datatypes except binary datatype.
• Min: All transformation datatypes except binary datatype.

You can configure a mapping variable for a Count aggregation type when it is an Integer or Small Integer. You can configure mapping variables of any datatype for Max or Min aggregation types.

To keep the variable value consistent throughout the session run, the Designer limits the variable functions you use with a variable based on aggregation type. For example, use the SetMaxVariable function for a variable with a Max aggregation type, but not with a variable with a Min aggregation type.

Variable Functions

Variable functions determine how the Integration Service calculates the current value of a mapping variable in a pipeline. Use variable functions in an expression to set the value of a mapping variable for the next session run. The transformation language provides the following variable functions to use in a mapping:

• SetMaxVariable. Sets the variable to the maximum value of a group of values. It ignores rows marked for update, delete, or reject. To use the SetMaxVariable with a mapping variable, the aggregation type of the mapping variable must be set to Max.
• SetMinVariable. Sets the variable to the minimum value of a group of values. It ignores rows marked for update, delete, or reject. To use the SetMinVariable with a mapping variable, the aggregation type of the mapping variable must be set to Min.
• SetCountVariable. Increments the variable value by one. In other words, it adds one to the variable value when a row is marked for insertion, and subtracts one when the row is marked for deletion. It ignores rows marked for update or reject. To use the SetCountVariable with a mapping variable, the aggregation type of the mapping variable must be set to Count.
• SetVariable. Sets the variable to the configured value. At the end of a session, it compares the final current value of the variable to the start value of the variable. Based on the aggregate type of the variable, it saves a final value to the repository. To use the SetVariable function with a mapping variable, the aggregation type of the mapping variable must be set to Max or Min. The SetVariable function ignores rows marked for delete or reject.

Use variable functions only once for each mapping variable in a pipeline. The Integration Service processes variable functions as it encounters them in the mapping. The order in which the Integration Service encounters variable functions in the mapping may not be the same for every session run. This may cause inconsistent results when you use the same variable function multiple times in a mapping.

The Integration Service does not save the final current value of a mapping variable to the repository when any of the following conditions are true:

• The session fails to complete.
• The session is configured for a test load.
• The session is a debug session.
• The session runs in debug mode and is configured to discard session output.

Sample Mapping:

If you want to fetch only those records which are modified/create newly after the previous run. Then you need to create a user-defined mapping variable $$LastRunDateTime (datetime datatype) that saves the Timestamp of the last row that Integration Service read in the previous session.

And in the source qualifier define the filter condition:.

Syntax:

Table.DateTime_column> $$LastRunDateTime

Note: In case if you define user mapping variable as string then you need to convert it into date datatype.

Syntax:

Table.DateTime_column > to_date($$LastRunDateTime, ‘YYYY-MM-DD HH:MM:SS’

1. In the Mapping Designer, click Mappings Or, in the Mapplet Designer.

2. Select ‘Parameters and Variables’

3. Click the Add button:

Field	Description
Name	Parameter name. The parameter name must be $$ followed by any alphanumeric or underscore characters.
Type	Variable/parameter. Select Parameter.
Datatype	Datatype of the parameter.
Precision or Scale	Precision and scale of the parameter.
Aggregation	Use for variables.
IsExprVar	Determines how the Integration Service expands the parameter in an expression string. If true, the Integration Service expands the parameter before parsing the expression. If false, the Integration Service expands the parameter after parsing the expression. Default is false. Note: If you set this field to true, you must set the parameter datatype to String, or the Integration Service fails the session.
Initial Value	Initial value of the parameter. If you do not set a value for the parameter in the parameter file, the Integration Service uses this value for the parameter during sessions. If this value is undefined, then the Integration Service uses a default value based on the datatype of the mapping variable. String=’’ Integer=0
Description	Description associated with the parameter.

Click on ‘OK’

4. Double click on Source Qualifier à Go to Properties tab.

From Source Filter click on Open Editorà Go to variables tab to added mapping variable to filter condition.

 

Select the Mapping Variable from Mapping variables folder and double click on it to add it to filter condition.

Click on ‘OK.

Click on Apply and Ok.

Note: Similar for Mapping Parameters.

Just you need to define values in Parameter file for those Mapping Parameters and Mapping Variables you declare at mapping/mapplet.

ALL TASK LIST


1.Session Task




A session is a set of instructions that tells the Integration Service how and when to move data from sources to targets. A session is a type of task, similar to other tasks available in the Workflow Manager.

In the Workflow Manager, we configure a session by creating a Session task. To run a session, we must first create a workflow to contain the Session task.

When we create a Session task, enter general information such as the session name, session schedule, and the Integration Service to run the session. We can select options to run pre-session shell commands, send On-Success or On-Failure email, and use FTP to transfer source and target files.

Configure the session to override parameters established in the mapping, such as source and target location, source and target type, error tracing levels, and transformation attributes. You can also configure the session to collect performance details for the session and store them in the PowerCenter repository. We might view performance details for a session to tune the session.

We can run as many sessions in a workflow as we need. We can run the Session tasks sequentially or concurrently, depending on the requirement.

The Integration Service creates several files and in-memory caches depending on the transformations and options used in the session.

Session Task

We can create a Session task for each mapping that we want the Integration Service to run. The Integration Service uses the instructions configured in the session to move data from sources to targets.

We can create a reusable Session task in the Task Developer. We can also create non-reusable Session tasks in the Workflow Designer as we develop the workflow. After we create the session, we can edit the session properties at any time.

Note:

1. The Session we create in Task Developer is reusable and the Session we create in Workflow Designer is non-reusable.

2. In Task Developer we can create only 3 reusable tasks Session, Command and Email.

2. Before we create a Session task, we must configure the Workflow Manager to communicate with databases we need a database connection and the Integration Service. We must assign appropriate permissions for any database, FTP, or external loader connections you configure. 

Click here for Session settings for Flat File Source and Target

Creating a Sample Session for Relational Source & Target and its Settings:

First create a workflow because to run a session or task(s) we need a workflow/worklet. Follow the link to create a workflow. Once you create a workflow/workflow follow as below to create a session to the workflow/worklet.

1. Select 'Tasks' from Menu --> Create

2. It will pop up Task Creator.

Select the task type to create: Session

Enter a new name for this task: s_m_emp_file_test (s_mappingname)

3. Click on 'Create' and it will pop up 'Mappings list' 

Select the mapping to which you want to create session, Click on 'OK' and 'Done'

Note: We can create session for valid mappings only.

4. Now, create a link between tasks Start and s_m_emp_file by using link task.

1st click on Start then drag it to s_m_emp_file

5. Now, the Session will be created in Workflow Designer.

Alternate way to create session: You can create session by click on Session icon also which is highlighted below and click in the workspace and select required mapping from the list and click on 'OK. (follow the step 3 above).

Note: The session can be create for only those mappings which are valid.

Now, we are created session only. But still we have to set Properties, Config Object, Mapping, Components details.

Double-click the Session task to open the session properties.

1) General Tab:

Enter session name, mapping name, and description for the Session task, assign resources, and configure additional task options.

Rename: You can rename session with the Rename button.

Make reusable: By check this option you can make this session reusable. (But once you make it reusable, you cannot revert it back to non-reusable session).

Description: Session description.

Mapping Name: Mapping associated with the session task.

Fail Parent if This Task Fails: Fails the parent worklet or workflow if this task fails.

Fail Parent if This Task Does Not Run: Fails the parent worklet or workflow if this task does not run.

Disable This Task: Disables the task.

Treat the Input Links as AND or OR: Runs the task when all or one of the input link conditions evaluate to True.

2) Properties Tab:

Enter session log information, test load settings, and performance configuration.

Note: You can click in '+' to expand settings.

We can configure the following settings:

a. General Options: General Options settings allow you to configure session log file name, session log file directory, parameter file name and other general session settings. 

Write Backward Compatible Session Log File: Check this if you do not want to use Log Service but instead want to write the session log to a file

Session Log File Name: Log file name (s_sessionname.log) or you can also use the $PMSessionLogFile session parameter.

Session Log File directory: Log file path, by default $PMSessionLogDir\. (You can change if you want to log in other path).

Parameter File Name: Parameter File Name used to define session parameters and override values of mapping parameters/variables.

Enable Test Load: To test  the load without writing data to the targets. It will perform all the tasks it do for normal load.

Number of Rows to Test: Define the no of records to test for load.

$Source connection value: Database connection name used to fetch source data. (You can also you session parameter as $DBConnectionName)

$Target connection value: Database connection name used to write data to target. (You can also you session parameter as $DBConnectionName)

Treat source rows as: How the source/input rows to be treat (Insert/Delete/Update/Data driven).

Note: Data driven is used if your mapping having Update Strategy transformation or a Custom transformation and the default option is Data Driven.

Commit Type: Determines if the Integration Service uses a Source-based, target-based, or user-defined commit interval.

Commit Interval: By default, the Integration Service uses a commit interval of 10,000 rows.

Commit On End Of File: By default, this option is enabled and the Integration Service performs a commit at the end of the file. This option is enabled by default for a target-based commit.

Rollback Transactions on Errors: Service will rolls back the transaction at the next commit point when it encounters a non-fatal writer error.

Recovery Strategy: Recovering the session run on failure.

• Fail session and continue the workflow: The Integration Service cannot recover the session, but it continues the workflow. This is the default session recovery strategy.
• Resume from the last checkpoint: The Integration Service saves the session state of operation and maintains target recovery tables.
• Restart task: The Integration Service runs the session again when it recovers the workflow.

 

b. Performance Settings:

The Performance settings allow you to increase memory size, collect performance details, and set configuration parameters. 

DTM buffer size: Amount of memory allocated to the session from the DTM process.

By default 'Auto'. The Workflow Manager allocates a minimum of 12 MB for DTM buffer memory.

You can specify auto or a numeric value. If you enter 2000, the Integration Service interprets the number as 2000 bytes. Append KB, MB, or GB to the value to specify other units. For example, you can specify 512MB.

Collect performance data: Collects performance details when the session runs. Use the Workflow Monitor to view performance details while the session runs.

Write Performance Data to Repository: Writes performance details for the session to the PowerCenter repository. Write performance details to the repository to view performance details for previous session runs.

Incremental Aggregation: The Integration Service performs incremental aggregation.

Reinitialize Aggregate Cache: Overwrites existing aggregate files for an incremental aggregation session.

Enable High Precision: Processes the Decimal datatype to a precision of 28.

Session Retry On Deadlock: The Integration Service retries target writes on deadlock for normal load. You can configure the Integration Service to set the number of deadlock retries and the deadlock sleep time period.

Pushdown Optimization: Default is None. The Integration Service analyzes the transformation logic, mapping, and session configuration to determine the transformation logic it can push to the database. Select one of the following pushdown optimization values:

• None: The Integration Service does not push any transformation logic to the database.
• To Source: The Integration Service pushes as much transformation logic as possible to the source database.
• To Target: The Integration Service pushes as much transformation logic as possible to the target database.
• Full: The Integration Service pushes as much transformation logic as possible to both the source database and target database.
• $$PushdownConfig: The $$PushdownConfig mapping parameter allows you to run the same session with different pushdown optimization configurations at different times.

3) Config Object tab:

Enter advanced settings, log options, and error handling configuration.

a. Advanced:

Advanced settings allow you to configure constraint-based loading, lookup caches, and buffer sizes.

Constraint based load ordering: Loads data to the targets based on primary key-foreign key constraints.

Cache LOOKUP() function: Select this if you want to cache the lookup table/file. If not selected, the Integration Service performs lookups on a row-by-row basis, unless otherwise specified in the mapping.

Default buffer block size: The size of buffer blocks used to move data and index caches from sources to targets. By default 'Auto', the Integration Service determines this value at run time.

Line Sequential buffer length: Default of 1024 bytes the PowerCenter Integration Service reads for each line. If source flat file records are larger than 1024 bytes then increase it.

Maximum Memory Allowed for Auto Memory Attributes: Maximum memory allocated for automatic cache when you configure the Integration Service to determine session cache size at run time.

If the value is set to zero, the Integration Service uses default values for memory attributes that you set to auto.

Maximum Percentage of Total Memory Allowed for Auto Memory Attributes: Maximum percentage of memory allocated for automatic cache when you configure the Integration Service to determine session cache size at run time. If the value is set to zero, the Integration Service uses default values for memory attributes that you set to auto. 

b. Log options:

Log options allow you to configure how you want to save the session log. By default, the Log Manager saves only the current session log.

Save session log by: Save session log by timestamp or by number of runs. (You can also use the $PMSessionLogCount service variable)

Save session log for these runs: Specify the number of runs of historical session log to save. $PMSessionLogCount can also be used.

Session Log File Max Size: Default is zero. If you set the size to zero the session log file size has no limit. Maximum size (in MB) of the partial session log after which we will rollover to the next partial session log file.

Session Log File Max Time Period: Default is zero. Maximum number of hours that the Integration Service writes to a session log file. Configure the maximum period to enable log file rollover by time. When the period is over, the Integration service creates another log file.

Maximum Partial Session Log Files: Default is 1. Maximum number of session log files to save. The Integration Service overwrites the oldest partial log file if the number of log files has reached the limit. If you set the maximum number to 0, the number of session log files is unlimited.

Writer Commit Statistics Log Frequency: Default is 1. Frequency that the Integration Service writes commit statistics in the session log. The Integration Service writes commit statistics to the session log after the specified number of commits occurs. The Integration Service writes commit statistics after each commit.

Writer Commit Statistics Log Interval: Time interval, in minutes, to write commit statistics to the session log. The Integration Service writes commit statistics to the session log after each time interval.

c. Error handling:

Error Handling settings allow you to determine if the session fails or continues when it encounters pre-session command errors, stored procedure errors, or a specified number of session errors.

Stop on errors: Indicates how many non-fatal errors the Integration Service can encounter before it stops the session. By default it is '0'.

Override tracing: The amount of detail that logs is depends on the tracing level that you set. You can configure tracing levels for each transformation or for the entire session. By default, the Integration Service uses tracing levels configured in the mapping.

Setting a tracing level for the session overrides the tracing levels configured for each transformation in the mapping.

Tracing Level:

1. None: Integration Service uses the tracing level set in the mapping.
2. Terse: Integration Service logs initialization information, error messages, and notification of rejected data.
3. Normal: Integration Service logs initialization and status information, errors encountered, and skipped rows due to transformation row errors. Summarizes session results, but not at the level of individual rows.
4. Verbose Initialization: In addition to normal tracing, the Integration Service logs additional initialization details, names of index and data files used, and detailed transformation statistics.
5. Verbose Data: In addition to verbose initialization tracing, the Integration Service logs each row that passes into the mapping. Also the Integration Service truncates string data to fit the precision of a column and provides detailed transformation statistics. When you configure the tracing level to verbose data, the Integration Service writes row data for all rows in a block when it processes a transformation. 

4). Mapping tab:

Enter source and target information, override transformation properties, and configure the session for partitioning.

The Transformations view of the Mapping tab contains the following nodes:

• Start Page. Describes the nodes on the Mapping tab.
• Connections. Displays the source, target, lookup, stored procedure, FTP, external loader, and queue connections. You can choose connection types and connection values. You can also edit connection object values.
• Memory Properties. Displays memory attributes that you configured on other tabs in the session properties. Configure memory attributes such as DTM buffer size, cache sizes, and default buffer block size.
• Files, Directories, and Commands. Displays file names and directories for the session. This includes session logs reject file, and target file names and directories.
• Sources. Displays the mapping sources and settings that you can configure in the session.
• Targets. Displays the mapping target and settings that you can configure in the session.
• Transformations. Displays the mapping transformations and settings that you can configure in the session.

Passing relational database connection to Source Instance.

1. Select source instance (Source Qualifier) under Sources Folder from Left Navigator.
2. Readers: Relational Reader.
3. Connection Value: Click on open browser to select relational connection.

• Select Object Type: Oracle (database type)
• Objects: project (Connection to fetch/load data)

Click 'OK'

Under properties we can override the conditions defined at mapping values. By changing attribute values.

Note: Owner Name can be defined in case you don't have any sql override at mapping/session level. The Owner Name is nothing but the schema name. We will be define this if the source table is exist in more than one schema.

1. Owner Name: It is nothing but Schema Name in which the target table exist.

2. Source Table Name: It is define in case session is used to load the data into similar structure table in same schema or another schema

Passing relational database connection to Target Instance.

1. Select the Target Instance under Targets Folder
2. Writes: Relational Writer
3. Connections Value: Click on open browser to select relational connection.

• Select Object Type: Oracle (database type)
• Objects: project (Connection to fetch/load data)

Click 'OK'

Under properties we can override the conditions defined at mapping values. By changing attribute values.

Attribute and its Value:

Target Load Type: Normal/Bulk

Insert: Select this to insert only

Update as update: Select this to update the existing records.

Update as Insert: Select this to Insert the existing records as update. (Note: You also need to select 'Insert').

Update else Insert: Select this if you using update strategy in the mapping and implemented 'Insert else Update' or 'Update else Insert' logic.

Delete: Select this to delete the records based on the mapping logic.

Truncate target table option: To truncate the target table before it start 'Insert/Update/Delete'

Reject file directory: use default unless it required to change.

Reject filename: use default or you can name to reject file.

Reject Truncated/Overflowed rows: Select this if you want to write ' truncated or overflowed rows are rejected' to the reject file while loading to target table.'

Update Override: You can add 'where' clause to use for an update session

Table Name Prefix: It is nothing but Schema Name in which the target table exist.

Target Table Name:  It is define in case session is used to load the data into similar structure table in same schema or another schema

5. Components tab:

Configure pre- or post-session shell commands and emails.

Pre-Session Command: This command will execute before the session starts.

            Pre-Session Command Type can be None/Reusable/Non-reusable.

Post-Session Success Command: This command will execute after session success.

            Post-Session Success Command Type can be None/Reusable/Non-reusable.

Post-Session Failure Command: It will execute on Session Failure.

            Post-Session Failure Command Type can be None/Reusable/Non-reusable.

On Success E-Mail: It sends email on session success.

                   On Success E-Mail Type can be None/Reusable/Non-reusable.

On Failure E-Mail: It sends email on session failure.

            On Failure E-Mail Type can be None/Reusable/Non-reusable.

Pre-session variable assignment: It will assign workflow or worklet variable values to mapping variables, mapping parameters and session parameters.

            Pre-session variable assignment Type is Non-reusable

Post-session on success variable assignment: It will assign mapping variable, mapping parameters and session parameters to parent workflow or worklet variables.

            Post-session on success variable assignment Type is Non-reusable.

Post-session on failure variable assignment: It will assign mapping variable, mapping parameters and session parameters to parent workflow or worklet variables.

            Post-session on failure variable assignment Type is Non-reusable.

Note: You can change Task type by clicking on 'Type' Which will give you drop down (None/Reusable/Non-reusable).

Click 'OK'. (Here we are done with session setting, go ahead and run your session).

We can the session in two ways:

1. Select 'Start' and right click and 'Start Task' or 'Start Workflow From Task'.

2. Select session 's_m_emp_file' and right click and 'Start Task' or 'Start Workflow From Task'.

Note:

Start Task: It will run the selected instance only.

Start Workflow From Task: It will run the workflow from the selected instance to end instance.

Command Task


The command will allow us to specify one or more shell commands to run during the workflow with the Command task.

For example:

We can specify shell commands in the Command task to delete reject files, copy a file, or archive target files.

There are two ways to use Command task:

1. Standalone Command task. We can use a Command task anywhere in the workflow or worklet to run shell commands.

2. Pre- and post-session shell command. We can call a Command task as the pre- or post-session shell command for a Session task. We can call them in COMPONENTS TAB of the session. We can run them in Pre-Session Command or Pre-Session Command or Post-Session Failure Command. By selecting the task “Type” and its value.

Note:

You can use any valid UNIX command or shell script for UNIX servers, or any valid DOS or batch file for Windows servers.

Example: To copy a ‘Test.txt’ file from one directory to another, you can use a shell command or windows command.

UNIX Syntax:

cp sales/sales_adj/Test.txt marketing/

Windows Syntax:

copy C:\sales\sales_adj/Test.txt D:\marketing\

Using Parameters and Variables

We can use parameters and variables in standalone Command tasks and pre- and post-session shell commands.

For Example: You might use a service process variable instead of hard-coding a directory name.

We can use the following parameters and variables in commands:

• Standalone Command tasks. We can use service, service process, workflow, and worklet variables in standalone Command tasks. We cannot use session parameters, mapping parameters, or mapping variables in standalone Command tasks. The Integration Service does not expand these types of parameters and variables in standalone Command tasks.
• Pre- and post-session shell commands. We can use any parameter or variable type that we define in the parameter file.

Creating a Sample Command Task:

Complete the following steps to create a Command task.

1. In the Workflow Designer or the Task Developer

Note: The Tasks created at 'Task Developer' are re-usable tasks.

2. Click Task --> Create. 

 
 3. Select Command Task for the task type to create:

 

 4. Enter a name for the Command task as 'cmd_copy_files'. Click Create. Then click Done.

 

 5. Double-click on the Command task in the workspace to open the Edit Tasks dialog box.

 

6. Go to the Commands tab, click the Add button to add a command. 

 

7. In the Name field, enter a name for the new command.

 

8. In the Command field, click the Edit button to open the Command Editor. 

 

9. Enter the command you want to run. Enter one command in the Command Editor.

You can use service, service process, workflow, and worklet variables in the command.

Example:

Windows Command:

copy $PMTargetFileDir\file\employee.txt $PMSourceFilesDir\file\employee.txt

Unix Command:

cp $PMTargetFileDir/file/employee.txt $PMSourceFilesDir/file/employee.txt

10. Click OK to close the Command Editor.

11. Repeat steps 4 to 9 to add more commands in the task.

12. Go to the Properties Tab:

a) Fail task if any command fails: If you select this option, the Integration Service stops running the commands and fails the Command task if any command fails. If you do not select this option, the Integration Service runs all the commands and treats the Command task as completed even if one of the command fails.

b) Recovery Strategy: Select either 'Fail task and continue workflow' or     'Restart task'

13. Click OK.

Note: If you want to create non-reusable command task then create it in workflow designer and follow the step 2 to step 13.

Now we have create re-usable command task and now we need to use this in session inside the workflow. (Open the workflow or worklet where you want to use this command task).

14. Select the session in the workspace, double click to edit and go to the components tab.

a) Select the task option where you want to use it 'Pre-Session Command' or 'Post-Session Success Command' or 'Post-Session Failure Command'.

 

b) Select Type 'Reusable' and click the Edit button to open the Command Editor. 

 

c) From Task Browser select the command 'cmd_copy_files', click 'Ok'

 

d) Click 'Apply' and 'Ok'.

Note:

1. If you specify non-reusable shell commands for a session, you can promote the non-reusable shell commands to a reusable Command task.

2. In Pre-Session Command the command task is use to copy the files from another location informatica server or archive the target file before it start session or rename the source/target file.

3. In Post-Session Success Command the command task is use to archive the source file after session succeeded or copy the target file to another location or rename the target/source file or trigger/stop/abort the another workflow or worklet using pmcmd command.

4. In Post-Session Failure Command the command task is use to archive the source file after session succeeded or copy the target file to another location or rename the target/source file or trigger/stop/abort the another workflow or worklet using pmcmd command.

Email Task

We can send email to designated recipients when the Integration Service runs a workflow.

For Example: If you want to track how long a session takes to complete, you can configure the session to send an email containing the time and date the session starts and completes. Or, if you want the Integration Service to notify you when a workflow suspends, you can configure the workflow to send email when it suspends.

To send email when the Integration Service runs a workflow, perform the following steps:

• Configure the Integration Service to send email. Before creating Email tasks, we need configure the Integration Service to send email (Administrator will config).
• Create Email tasks. Before you can configure a session or workflow to send email, you need to create an Email task.
• Configure sessions to send post-session email. You can configure the session to send an email when the session completes or fails. You create an Email task and use it for post-session email.
• Configure workflows to send suspension email. You can configure the workflow to send an email when the workflow suspends. You create an Email task and use it for suspension email.

The Integration Service sends the email based on the locale set for the Integration Service process running the session.

We can use parameters and variables in the email user name, subject, and text. For Email tasks and suspension email, you can use service, service process, workflow, and worklet variables. For post-session email, you can use any parameter or variable type that you can define in the parameter file. For example, you can use the $PMSuccessEmailUser or $PMFailureEmailUser service variable to specify the email recipient for post-session email.

Working with Email Tasks

We can send email during a workflow using the Email task on the Workflow Manager. We can create reusable Email tasks in the Task Developer for any type of email. Or, we can create non-reusable Email tasks in the Workflow and Worklet Designer.

Use Email tasks in any of the following locations:

• Session properties. You can configure the session to send email when the session completes or fails.
• Workflow properties. You can configure the workflow to send email when the workflow is interrupted.
• Workflows or worklets. You can include an Email task anywhere in the workflow or worklet to send email based on a condition you define.

Using Email Tasks in a Workflow or Worklet

We can use Email tasks anywhere in a workflow or worklet. For example, we might configure a workflow to send an email if a certain number of rows fail for a session.

For example, we may have a Session task in the workflow and you want the Integration Service to send an email if more than 20 rows are dropped. To do this, you create a condition in the link, and create a non-reusable Email task. The workflow sends an email if the session fails more than 20 rows are dropped.

Working with Post-Session Email

We can configure a session to send email when it fails or succeeds. We can create separate email tasks for success and failure email.

The Integration Service sends post-session email at the end of a session, after executing post-session shell commands or stored procedures. When the Integration Service encounters an error sending the email, it writes a message to the Log Service. It does not fail the session.

We can specify a reusable Email that task you create in the Task Developer for either success email or failure email. Or, we can create a non-reusable Email task for each session property. When you create a non-reusable Email task for a session, you cannot use the Email task in a workflow or worklet.

We cannot specify a non-reusable Email task you create in the Workflow or Worklet Designer for post-session email.

We can use parameters and variables in the email user name, subject, and text. Use any parameter or variable type that you can define in the parameter file. For example, we can use the service variable $PMSuccessEmailUser or $PMFailureEmailUser for the email recipient. Ensure that you specify the values of the service variables for the Integration Service that runs the session. You can also enter a parameter or variable within the email subject or text, and define it in the parameter file.

Email Variables and Format Tags

We can use email variables and format tags in an email message for post-session emails. We can use some email variables in the subject of the email. With email variables, we can include important session information in the email, such as the number of rows loaded, the session completion time, or read and write statistics. You can also attach the session log or other relevant files to the email. Use format tags in the body of the message to make the message easier to read.

Note: The Integration Service does not limit the type or size of attached files. However, since large attachments can cause problems with the email system, avoid attaching excessively large files, such as session logs generated using verbose tracing. The Integration Service generates an error message in the email if an error occurs attaching the file.

Creating a sample Email Task:

You can create Email tasks in the Task Developer, Worklet Designer, and Workflow Designer.

1. In the Task Developer, click Tasks > Create.

 

2. The Create Task dialog box appears. Select an Email task and enter a name for the task. Click Create. 

 

3. The Workflow Manager creates an Email task in the workspace, Click Done. 

 

4. Select the email task and Double-click on it to edit --> Go to the Properties tab. 

 

a) Enter the email address of the mail recipient in the Email User Name field.

b) Enter the subject of the email in the Email Subject field Or you can leave this field blank.

c) Click the Open button in the Email Text field to open the Email Editor.

5. Enter the text of the email message in the Email Editor. You can use service, service process, workflow, and worklet variables in the email text. Or, you can leave the Email Text field blank.

 

Note:

1. You can incorporate format tags and email variables in a post-session email. However, you cannot add them to an Email task outside the context of a session.

2. The Integration Service does not limit the type or size of attached files. However, since large attachments can cause problems with the email system, avoid attaching excessively large files, such as session logs generated using verbose tracing. The Integration Service generates an error message in the email if an error occurs attaching the file.

The following table describes the email variables that you can use in a post-session email:

Email Variable	Description
%a<filename>	Attach the named file. The file must be local to the Integration Service. The following file names are valid: %a<c:\data\sales.txt> or %a</users/john/data/sales.txt>. The email does not display the full path for the file. Only the attachment file name appears in the email. Note: The file name cannot include the greater than character (>) or a line break.
%b	Session start time.
%c	Session completion time.
%d	Name of the repository containing the session.
%e	Session status.
%g	Attach the session log to the message.
%i	Session elapsed time.
%l	Total rows loaded.
%m	Name of the mapping used in the session.
%n	Name of the folder containing the session.
%r	Total rows rejected.
%s	Session name.
%t	Source and target table details, including read throughput in bytes per second and write throughput in rows per second.
%u	Repository user name.
%v	Integration Service name.
%w	Workflow name.
%y	Session run mode (normal or recovery).
%z	Workflow run instance name.
Note: The Integration Service ignores %a, %g, and %t when you include them in the email subject. Include these variables in the email message only.

The following table lists the format tags you can use in an Email task:

Formatting	Format Tag
tab	\t
new line	\n

6. Click OK twice to save the changes.

Now we have create re-usable command task and now we need to use this in session inside the workflow. (Open the workflow or worklet where you want to use this command task).

7. Select the session in the workspace, double click to edit and go to the components tab.

 

Note:

1. On Success E-Mail: Send an email alter on session succeeded.
2. On Failure E-Mail: Send an email alter on session failure.

8. Select Task as On Success/Failure E-Mail, Type as Reusable/Non-reusable and Click the Open button in the Email Text field to open the Email Editor

9. Click on 'OK'.

 

Note:

If you want to modify anything in the email click on edit icon and modify it.

 

Control Task


We can use the Control task to stop, abort, or fail the top-level workflow or the parent workflow based on an input link condition. A parent workflow or worklet is the workflow or worklet that contains the Control task. We can defined the condition to the link task connected to the control task.

The following table describes the options you can configure in the Control task:

Control Option	Description
Fail Me	The Integration Service fails the Control task if you choose this option.
Fail Parent	Marks the status of the workflow or worklet that contains the Control task as failed after the workflow or worklet completes.
Stop Parent	Stops the workflow or worklet that contains the Control task.
Abort Parent	Aborts the workflow or worklet that contains the Control task.
Fail Top-Level Workflow	Fails the workflow that is running.
Stop Top-Level Workflow	Stops the workflow that is running.
Abort Top-Level Workflow	Aborts the workflow that is running.

Create a sample Control Task

Create a Control task in the workflow to stop, abort, or fail the workflow based on an input link condition.

1. In the Workflow Designer, click Tasks > Create.

2. Select Control Task for the task type.

3. Enter a name for the Control task.

4. Click Create, and then click Done.

The Workflow Manager creates and adds the Control task to the workflow.

5. Double-click the Control task in the workspace to open it.

6. Configure the control options on the Properties tab.  

Event Task

We can define events in the workflow to specify the sequence of task execution. The event is triggered based on the completion of the sequence of tasks.

• Event-Raise task. Event-Raise task represents a user-defined event. When the Integration Service runs the Event-Raise task, the Event-Raise task triggers the event. Use the Event-Raise task with the Event-Wait task to define events.
• Event-Wait task. The Event-Wait task waits for an event to occur. Once the event triggers, the Integration Service continues executing the rest of the workflow.

To coordinate the execution of the workflow, you may specify the following types of events for the Event-Wait and Event-Raise tasks:

• Predefined event. A predefined event is a file-watch event. The predefined events, use an Event-Wait task to instruct the Integration Service to wait for the specified indicator file to appear before continuing with the rest of the workflow. When the Integration Service locates the indicator file, it starts the next task in the workflow.
• User-defined event. A user-defined event is a sequence of tasks in the workflow. Use an Event-Raise task to specify the location of the user-defined event in the workflow. A user-defined event is sequence of tasks in the branch from the Start task leading to the Event-Raise task.

When all the tasks in the branch from the Start task to the Event-Raise task complete, the Event-Raise task triggers the event. The Event-Wait task waits for the Event-Raise task to trigger the event before continuing with the rest of the tasks in its branch.

Event-Raise Tasks

The Event-Raise task represents the location of a user-defined event. A user-defined event is the sequence of tasks in the branch from the Start task to the Event-Raise task. When the Integration Service runs the Event-Raise task, the Event-Raise task triggers the user-defined event.

To use an Event-Raise task, you must first declare the user-defined event. Then, create an Event-Raise task in the workflow to represent the location of the user-defined event you just declared. In the Event-Raise task properties, specify the name of a user-defined event.

Event-Wait Tasks

The Event-Wait task waits for a predefined event or a user-defined event. A predefined event is a file-watch event. When you use the Event-Wait task to wait for a predefined event, you specify an indicator file for the Integration Service to watch. The Integration Service waits for the indicator file to appear. Once the indicator file appears, the Integration Service continues running tasks after the Event-Wait task.

You can assign resources to Event-Wait tasks that wait for predefined events. You may want to assign a resource to a predefined Event-Wait task if you are running on a grid and the indicator file appears on a specific node or in a specific directory. When you assign a resource to a predefined Event-Wait task and the Integration Service is configured to check resources, the Load Balancer distributes the task to a node where the required resource is available.

Note: If you use the Event-Raise task to trigger the event when you wait for a predefined event, you may not be able to successfully recover the workflow.

You can also use the Event-Wait task to wait for a user-defined event. To use the Event-Wait task for a user-defined event, specify the name of the user-defined event in the Event-Wait task properties. The Integration Service waits for the Event-Raise task to trigger the user-defined event. Once the user-defined event is triggered, the Integration Service continues running tasks after the Event-Wait task.

Event Wait:

1. In the Workflow Designer (First you need to create a session).

2. From Menu bar select Workflows à Click Create

 

3. Select Event Wait Task for the task type.

 Enter a name for the Event Wait task name <InputFileWatcher>.

Click Create. Then click Done. 

Now, create a link between tasks Start, InputFileWatcher and s_m_employee_file2xml by using link task. 

The Workflow Designer creates and adds the Event Wait task to the workflow.

4. Double-click the Event Wait task to open the Edit Task dialog box à Go to Properties Tab

 

User Defined Event: A user-defined event is triggered by the Event-Raise task. To wait for a user-defined event, you must first use an Event-Raise task to trigger the user-defined event.

Event Name: Event name of the waiting instance.

Enable Past Events: Enable past events

Filewatch name: Path and File Name of the event for the instance waiting (defined at Event Tab)

Delete Filewatch file: Delete the file watch once the instance complete.

5. Go to Event Tab: To define file watch path and file name.

 

Back to Properties tab after file watcher defined.

 

Click on Apply and OK.

6. Save the workflow and Run the workflow.

Decision Task


Working with the Decision Task

The Decision task allows us to enter a condition that determines the execution of the workflow, similar to a link condition with the Decision task. The Decision task has a predefined variable called $Decision_task_name.condition that represents the result of the decision condition. The Integration Service evaluates the condition in the Decision task and sets the predefined condition variable to True (1) or False (0).

We can specify one decision condition per Decision task.

Depending on the workflow, we might use link conditions instead of a Decision task.

Using the Decision Task

We can use the Decision task instead of multiple link conditions in a workflow. Instead of specifying multiple link conditions, use the predefined condition variable in a Decision task to simplify link conditions.

Example: 

1. If you want to copy/move the target file to another location.

2. You have 3 sessions in a workflow and you want to run the 3rd session on success of 1st and 2nd session otherwise do not run 3rd session, you can use decision task.

Create a sample Decision task:

Complete the following steps to create a Decision task.

1. In the Workflow Designer (open any workflow if have created any).

2. From Menu bar click Tasks > Create.

3. Select Decision Task for the task type.

 

Enter a name for the Decision task name <Decision_Flow>.

Click Create. Then click Done.

Now, create a link between tasks Start and s_m_emp_file by using link task. (1st click on Start then drag it to Decision_Flow).

 
 

The Workflow Designer creates and adds the Decision task to the workspace.

4. Double-click the Decision task to open it à go to Properties Tab.

 5. Click the Open button in the Value field to open the Expression Editor.

6. In the Expression Editor, enter the condition you want the Integration Service to evaluate.

Validate the expression before you close the Expression Editor.

7. Click OK.

8. Click Apply and then OK.

9. Add a command task to move data from $PMTargetFileDir to another Location.

Now, create a link between tasks Start and s_m_emp_file by using link task. (1st click on Start then drag it to cmd_copy_files).

10.  Select the link between Decision_flow and cmd_copy files to defined condition.

Validate the expression before you close the Expression Editor, Click OK.

11. Click Apply and then OK.

12. From Menu bar workflow à click start workflow (to run the workflow)

Assignment Task


An Assignment Task is used to assign a value to a user-defined workflow variable. To use an Assignment task in the workflow, first create and add the Assignment task to the workflow.

Note: To create Workflow variable, edit workflow à go to Variables tab then added port you required. To store any values in buffer select ‘Persistent’

Then configure the Assignment task to assign values or expressions to user-defined variables. After you assign a value to a variable using the Assignment task, the Integration Service uses the assigned value for the variable during the remainder of the workflow.

We must create a variable before you can assign values to it. We cannot assign values to predefined workflow variables.

To create an Assignment task:

1. In the Workflow Designer (Open already created workflow). 

 

2. On menu click Tasks > Create 

 

3. Select Assignment Task for the task type. 

 

Enter a name for the Assignment task <Assign_Test>.

Click Create. Then click Done. 

 

Now, create a link between tasks Start and s_m_emp_file by using link task. (1st click on Start then drag it to Assign_Test).

The Workflow Designer creates and adds the Assignment task to the workflow. 

4. Double-click the Assignment task to open the Edit Task dialog box.

5. On the Expressions tab, click Add to add an assignment. 

 

6. Click the Open button in the User Defined Variables field. 

 

7. Select the variable for which you want to assign a value. Click OK. 

 

8. Click the Edit button in the Expression field to open the Expression Editor. 

 

The Expression Editor shows predefined workflow variables, user-defined workflow variables, variable functions, and boolean and arithmetic operators.

9. Enter the value or expression you want to assign.  

 

For example, if you want to assign the previous session WorkflowRunId, WorkflowName, SessionName, MappingName details to the user-defined variable $$WORKFLOWRUNID, $$WORKFLOWNAME, $$SESSIONNAME, $$MAPPINGNAME and run the next session after 3 runs of my first session in the Expression Editor.

10. Click Validate.

Validate the expression before you close the Expression Editor.

11. Repeat steps 6 to 8 to add more variable assignments.

Use the up and down arrows in the Expressions tab to change the order of the variable assignments.

12. Click OK.

 

Second Output File:

Note: I want to capture the session s_m_emp_file related details to in to flatfile by using workflow variable i am storing those details from the session s_m_emp_file and using assignment task I am passing those details to second session s_m_aduit_mapping.