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))