Unable to return lookup value to variable using Lookup_ext function

422 Views

Hi Guys,

I tried many ways to output a lookup value to the parameter or global variable using lookup_ext function in Mapping Tab, but still cant make it work. I am using Data Services XI3.1

The senario is:

1) I used Query Transform to output 1 column as New_Col at Schema Out.

2) Then at Mapping Tab, I inserted lookup_ext function (using the Lookup_ext function Windows) as such :

lookup_ext(\[Control_DS.DBO.EXIST_FILE_ITEM,'PRE_LOAD_CACHE','MAX'], \[LOOKUP_YES],\['NO'],\[SOURCE_SYSTEM,'=',FF_Header_Record.SrcSys],\[],\[$Check_Header]) SET ("run_as_separate_process"='no', "output_cols_info"='<?xml version="1.0" encoding="UTF-8"?><output_cols_info><col index="1" expression="no"/>

</output_cols_info>' )

3) The function supposed to return success lookup value 'YES' from lookup table Control_DS.DBO.EXIST_FILE_ITEM to the column New_Col and also the parameter (or global variable) of $Check_Header. However, after execution, only the New_Col being set to 'YES', which this column actually is used as dummy purpose, but the aim is to update $Check_Header, but it is still empty or Null.

The purpose of the exercise is I am trying to find a way to return the arbitary value or flag set inside the Data Flow to outside calling parent Work Flow or Job.

I have read through the manual but still cant figure out what went wrong. Please help.

Thank you.