Informatica - How to Delete a file with Command Task

Delete a file with Command Task...

del C:\FilePath\FileName.txt

Informatica - How to Abort a session from within Mapping

In Expression variable port set

IIF("on a condition",ABORT('Error Message'))

example

IIF(Record_count>0,ABORT('Data quality Issue'))

The Source Qualifier Contains an unbound field

Severity: ERROR
Timestamp:
Node: node01
Thread: MAPPING
Process ID: 5940
Message Code: TE_7020
Message: Internal error. The Source Qualifier [SQ_sc_S] contains an unbound field [RID]. Contact Informatica Global Customer Support.


Solution

This issue occurs when there are one or more unconnected ports between the Source and Source Qualifier.

Connect all the unconnected Ports with correct datatypes.

How to remove Lookup OrderBty clause added automatically By Informatica

End your Lookup Override SQL with --

SELECT
col1 as col1
col2 as col2
WHERE col1 ="something"
and col2 in (9017,9014) --

High Precision - Decimal Rounding Off

Decimal

When a session runs without high precision, the Integration Service converts decimal values to doubles. The transformation Decimal datatype supports precision of up to 28 digits, while the Double datatype supports precision of up to 15 digits. Therefore, precision loss occurs if the decimal value has a precision greater than 15 digits.

For example, you have a mapping with Decimal (20,0) that passes the number 40012030304957666903. If the session does not run with high precision, the Integration Service converts the decimal value to double and passes 4.00120303049577 x 10¹⁹.

To ensure precision of up to 28 digits, use the Decimal datatype and enable high precision in the session properties. When you run a session with high precision, the Integration Service processes decimal values as Decimal. Precision loss does not occur in a calculation unless the result produces a value with precision greater than 28 digits. In this case, the Integration Service stores the result as a double.

Change settings under.

Session -> Poerperties Tab -> Performance -> Enable High Percission CheckBox

Database driver error... Function Name : Execute Multiple SQL Stmt : INSERT INTO

Severity: ERROR
Node: node01_
Thread: WRITER_1_*_1
Process ID: 5384
Message Code: WRT_8229
Message: Database errors occurred:
Microsoft SQL Server Native Client 11.0: Unspecified error


Database driver error...
Function Name : Execute Multiple
SQL Stmt : INSERT INTO dbo.table(col1,clo2,clo3)  VALUES ( ?, ?, ?)



Solution

This error will occur when the session is attempting to load null values into a column in the target table with a NOT NULL constraint.
To resolve this either filter out the null values or remove the NOT NULL constraint on the target column.

Debug

To Debug Enable Verbose data logs.
Session Double Click - Config Object --> Error Handling --> Override Tracing --> Verbose Data

Now run the session again and view session logs for RowData i.e. data pulled from source. I had received null values where target table is expecting it to be not null

 

Informatica - Session Performance Stats - Sql to pull performance data from Repository

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT opti.SUBJECT_ID,
       opti.WORKFLOW_ID,
       opti.WORKFLOW_RUN_ID,
       opti.INSTANCE_ID,
       opti.INSTANCE_NAME,
       opti.START_TIME,
       opti.END_TIME,
       opti.RUN_ERR_CODE,
       opti.RUN_ERR_MSG,
       ostl.MAPPING_NAME,
       ostl.SRC_SUCCESS_ROWS,
       ostl.SRC_FAILED_ROWS,
       ostl.TARG_SUCCESS_ROWS,
       ostl.TARG_SUCCESS_ROWS,
       DATEDIFF (SECOND, opti.START_TIME, opti.END_TIME) TimeDifferenceinSec,
       CASE
           WHEN DATEDIFF (ss, opti.START_TIME, opti.END_TIME) = 0 THEN 0
           ELSE ostl.TARG_SUCCESS_ROWS/DATEDIFF (SS, opti.START_TIME, opti.END_TIME)
       END AS avgRecordsPerSec
FROM [PC_REP].[dbo].[OPB_TASK_INST_RUN] opti
INNER JOIN [PC_REP].[dbo].[OPB_SESS_TASK_LOG] ostl ON ostl.WORKFLOW_RUN_ID=opti.WORKFLOW_RUN_ID
AND ostl.INSTANCE_ID=opti.INSTANCE_ID
AND INSTANCE_NAME LIKE '%EnterTheNameOfsession%'
ORDER BY ostl.WORKFLOW_RUN_ID DESC 

Or For all task instance types.

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT --opti.SUBJECT_ID,
 --     opti.WORKFLOW_ID,
 --   opti.WORKFLOW_RUN_ID,
 -- opti.INSTANCE_ID,
 opti.INSTANCE_NAME,
 opti.START_TIME,
 opti.END_TIME,
 DATEDIFF (SECOND, opti.START_TIME, opti.END_TIME) TimeDifferenceinSec, --opti.RUN_ERR_CODE,
 -- opti.RUN_ERR_MSG,
 --ostl.MAPPING_NAME,
 ostl.SRC_SUCCESS_ROWS, --ostl.SRC_FAILED_ROWS,
 --ostl.TARG_SUCCESS_ROWS,
 --ostl.TARG_SUCCESS_ROWS,
 CASE
     WHEN DATEDIFF (ss, opti.START_TIME, opti.END_TIME) = 0 THEN 0
     ELSE ostl.TARG_SUCCESS_ROWS/DATEDIFF (SS, opti.START_TIME, opti.END_TIME)
 END AS avgRecordsPerSec
FROM [PC_REP].[dbo].[OPB_TASK_INST_RUN] opti --change to inner join if you are intrested only in session stats and change to left outer join if you are after other tasks.
--but for other tasks last 2 columns will/may not have any data
LEFT OUTER JOIN [PC_REP].[dbo].[OPB_SESS_TASK_LOG] ostl ON ostl.WORKFLOW_RUN_ID=opti.WORKFLOW_RUN_ID
AND ostl.INSTANCE_ID=opti.INSTANCE_ID 
--and RUN_ERR_CODE=0

WHERE INSTANCE_NAME LIKE '%TaskName%'
ORDER BY opti.START_TIME DESC --and   ostl.WORKFLOW_RUN_ID=646762
 ----and ostl.INSTANCE_ID=4472