1. write sql in mapping only, then refresh to workflow.
2. be care of data type and length.
3. naming convention and description standard
4, check the result option when using the lookup transformation.
5, each port in source qualifier should be bounded to the source.
6. CHECK IF there is useless targets or source in the mapping, which can cause the
ERROR : Failed to prepare the task.
And can't get the session log, even though the mapping and workflow are valid.
7. In the lookup transformation, need to take care of the datatype in the db.
Ex : char, varchar
8. The lookup transformation will compare the NULL Value, and retrun the matched record.
9. Confiugre the connection $Source/$Target in the mapping desinger for the lookup trans, to make it be consistent with the session property.
tips
1. lookup override sql -- use 'AS, the number, order of the ports should be as same as the sql statement
2. TARGET LOAD PLAN
performance
1. joiner -- make the smaller file the master.
2. In general unconnected lookups should be used if possible since less data is cached.
3. using Sorted Input in Aggregator and Joiner transformations when input rows > 250,000
dateadd()
convert(datetime, '$$SRC_FLT_DT')
CREATE_DATE > to_date(‘$$mv_INCREMENT_DATE’,’MM-DD-YYYY HH24:MI:SS’)
SETMAXVARIABLE($$mv_INCREMENT_DATE,CREATE_DATE)
FIRST( ITEM_NAME, ITEM_PRICE > 40 )
============================================================================================================
pmcmd abortworkflow -uv USERNAME -pv PASSWORD -sv $INTEGRATION_SERVICE -d