locked
Lookup in Azure Data Factory RRS feed

  • Question

  • Hi,

    Azure Data Factory V2

    We need to move data between Azure SQL and Oracle database. When moving the data from Azure SQL to Oracle database, we need to lookup some ID column on Oracle database tables and then populate its corresponding description. For example if the data from Azure has only Country_ID, we need to do lookup on Oracle database to get the corresponding country name and then populate the result to Oracle database table. I am new to Azure data factory and couldn't figure out how to tackle this scenario. Please let me know how to do the lookup ?

    Only workaround I can think of is copying the Oracle database table to Azure and then do the lookup logic in Copy Activity SQL. We don't want to take that approach as we had to maintain another table and refresh.

    Thanks!

    Wednesday, February 6, 2019 9:04 PM

All replies

  • Hi Ebin,

    You can have a custom activity to connect to Azure SQL, fetch data, transform the data and then write to Oracle DB using its connector.

    To read more about custom activities in ADF, please refer this doc :

    https://docs.microsoft.com/en-us/azure/data-factory/transform-data-using-dotnet-custom-activity

    Let us know if this helps. Else, we can continue to probe into the issue further.


    MSDN

    Thursday, February 7, 2019 9:19 AM
  • Hi Ebin,

    You could invoke a stored procedure script in Oracle sink to do the lookup and data copy.

    In the stored procedure script, defines how description will be populated based on Country_ID, and how Azure SQL columns will be mapped and copied into Oracle table. This link gives you an example on Azure SQL, please check it out. :)

    Thanks.

    Saturday, February 9, 2019 8:54 AM
  • 1) You can copy the distinct values using copy activity and store it in BLOB

    2) Retrieve distinct values from Oralce Database and store it in BLOB

    3) Use dataflow to read them and then create output to write into BLOB and then provision it to Blob (copy activity)

    Another method is,

    Use stored procedure activity to sql server and use for each activity for the output of the stored procedure activty - i have not used this but i surely used the above steps and it works. 

    Monday, January 13, 2020 9:55 PM
  • This is what a lookup "transformation" in dataflow does. 
    • Proposed as answer by dataflowuser Tuesday, January 14, 2020 6:21 AM
    Tuesday, January 14, 2020 6:21 AM