Informatica Expression Code:

Chr(10): Line Feed character 
Chr(13): Carriage Return character. 
Chr(12): Form Feed

Expression Code to remove the above chr's when loading a flat file to database tables or target flat file.

replacestr(1,YourTextColumn,'|',chr(10),chr(13),chr(12),' ') 

SQL: Pivot multiple rows into a single row for a Primary Key using XML_PATH.

Sample Data:

Desired Output Format:

Passing Value of Mapping Variable to Workflow Variable

Passing Value of Mapping Variable to Workflow Variable

Declare a  Mapping Variable

Assign it in expression, this will be used at the workflow level. If the Variable assigned is 1, then we move forward with the ETL execution else write the data into Error tables and fail the File which is currently being Processed.

Create Variable at the Workflow Level, Workflows --> Edit--> Variable

Pre-Session Variable Assignment

Post - Session Variable Assignment

Control ETL workflow Execution based on the variable values, in this case the next session is triggered. Happy Path! 

Negative Case: Error Session is triggered. 

Workflow Name, Session Name, Mapping Name, Start Time, End Time, First Fatal Error Message

Informatica Repo: SQL Sever

select wflow.WORKFLOW_NAME, trun.INSTANCE_NAME, task.MAPPING_NAME, trun.START_TIME, trun.END_TIME, trun.RUN_ERR_MSG
from dbo.OPB_TASK_INST_RUN trun, dbo.OPB_SESS_TASK_LOG task, dbo.OPB_WFLOW_RUN wflow
where trun.RUN_ERR_CODE <> 0
and trun.START_TIME > DATEADD(mi, -60, getdate())
and trun.WORKFLOW_ID = task.WORKFLOW_ID
and trun.WORKFLOW_RUN_ID = task.WORKFLOW_RUN_ID
and trun.INSTANCE_ID = task.INSTANCE_ID
and trun.WORKFLOW_ID = wflow.WORKFLOW_ID
and trun.WORKFLOW_RUN_ID = wflow.WORKFLOW_RUN_ID
order by trun.START_TIME desc

Informatica Workflow Execution Dashboard Metadata Query:

Folder Name, Workflow Name, Last Start Time, Time since Last Execution

Many times because of various reasons Informatica scheduled workflows come off schedule. In an environment where continuous monitoring is not available and the operations or development team wants to keep a track of the ETL jobs on a need by basis, the below query can be used which may be set up in your BI tool or run on ad-hoc basis.

Informatica Repository used is SQL Sever. Modify the query based on the Repository Database.

select SUBJECT_AREA, WORKFLOW_NAME, LAST_START_TIME, CONVERT(VARCHAR(12), DATEADD(MS, DATEDIFF(MS, last_start_time, getdate()), 0), 114) time_since_last_execution
from (
select SUBJECT_AREA, WORKFLOW_NAME, max(start_time) last_start_time from [dbo].[REP_WFLOW_RUN]
where START_TIME between DATEADD(HOUR, -24, GETDATE()) and getdate()
group by SUBJECT_AREA, WORKFLOW_NAME) a
order by LAST_START_TIME desc

Source File: CurrentlyProcessedFileName

Informatica PowerCenter provides an option to know the file name which is currently being processed by the ETL workflow. 

Please follow the screen shots below which indicate the steps to set it up.

Source Analyser:

However PowerCenter gives you the complete path to the file location, to get only the file name for further ETL validation/Integration.

Expression Code to get only the currently Processed File Name:

xml file:

REVERSE(ltrim(rtrim(CurrentlyProcessedFileName)))

IIF(INSTR(var_CurrentlyProcessedFileName,'\')>0,REVERSE(SUBSTR(var_CurrentlyProcessedFileName,1,INSTR(var_CurrentlyProcessedFileName,'\')-1)),REVERSE(var_CurrentlyProcessedFileName))

Incremental Loading in Informatica using Mapping Variable

Lets understand what is a Mapping Variable:

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 when we run the session. Variable functions like SetMaxVariable, SetMinVariable, SetVariable, SetCountVariable are used 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 we 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 the parameter file.

Lets see the steps to create a Mapping Variable which can be used in incremental loading.

Step 1:

How to create mapping variable in informatica?

Step 2:

To define a mapping variable to be used for incremental loading (for this illustration we are in a assumption that your source system is a relational source (RDBMS))

Step 3:

In the Source Qualifier we need to Generate SQL and add a filter, WHERE condition to filter records that are greater than the $$LAST_RUN_DATE

Step 4: Part 1: 

To define the logic where the Informatica integration services saves the timestamp when the session is executed, for the next run, it will use that saved timestamp to read the records from the relational source incrementally. We need to create a expression transformation right after the Source Qualifier to set the variable.

Step 4: Part 2:

In the expression transformation create a OUT port and use the code as show below

Step 5:

After the session has executed we can see the saved timestamp in the workflow manager as below:-