none
How to process match and Nomatch data in ADF data flow? RRS feed

  • Question

  • Hi,

    I am working on to transfer data from one database to another using ADF dataflow (Mapping dataflow). There are some scenario where I need to look for lookup value and process the data further.

    Here is an example.

    Table A (Source) : Name, TypeName

    Table B(Destination): Name , TypeID

    TableC(Lookup): TypeName, TypeID

    Data flow will process NAME straightforward but to get TypeID at destination it will go through the lookup table where TypeName will match and generate the ID. however, if Typename doesn't match then it will give default ID '00000'.

    This process was really quick in SSIS but in ADF I have been trying Join, lookup, Exists, Conditional split, I am not able to achieve it. May be I am doing in wrong order or using wrong function.

    Any help will be appreciated!.

    Thanks

    Friday, December 6, 2019 1:20 AM

All replies

  • Use Lookup to add reference data from another source to your Data Flow. The Lookup transform requires a defined source that points to your reference table and matches on key fields.
    Select the key fields that you wish to match on between the incoming stream fields and the fields from the reference source. You must first have created a new source on the Data Flow design canvas to use as the right-side for the lookup.
    Friday, December 6, 2019 4:39 AM
  • Hi,

    I am working on to transfer data from one database to another using ADF dataflow (Mapping dataflow). There are some scenario where I need to look for lookup value and process the data further.

    Have you checked below blogs? See if it helps:

    Create Generic SCD Pattern in ADF Mapping Data Flows
    AZURE DATA FACTORY DATA FLOW: BUILDING SLOWLY CHANGING DIMENSIONS


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Friday, December 6, 2019 6:45 AM
  • Hi,

    Is there a way you can explain me with screen shot on look up wise because I tried to use lookup and output is not as expected.

    Thanks

    Friday, December 6, 2019 8:12 PM
  • Hi Thanks for your response.

    and I  went through your link but none of the link gave what I am looking for also I tried what solution have applied in your link. Is there anyway you can explain with your experience along with screen shot?

    Thanks

    Friday, December 6, 2019 8:13 PM
  • 2 sources: Source1 and LookupSource

    Source1 -> Lookup transform [connect to LookupSource, join on Name,TypeName] -> Derived Column [typeID: iif (isMatch(), LookupSource.TypeID, '00000')] -> Sink


    Monday, December 9, 2019 6:53 AM
  • Thanks Mkro for your response. 

    I have resolved the issue by change in database design.

    Client got me with new database design after I found couple of loopholes in design. 

    Now everything working fine.

    Thanks

    Wednesday, December 11, 2019 3:47 AM
  • Thank you for updating us AmitBDesai
    Monday, December 16, 2019 6:16 PM
    Moderator