locked
Azure Data Factory Dynamic Schema Mapping in Copy Data Activity RRS feed

  • Question

  • I have a configuration table which has schema mappings for different tables in the database. The requirement is to use this table via a ForEach activity to get the schema mappings for different tables. I have the following questions.

    1. Is it possible to pass the items array value (the value itself is in JSON format) to the translator property in the Copy Data activity?
    2. I have figured out that it is possible to pass dynamic schema mapping through a parameter. My question now is how do we change the value of the parameter dynamically based on each iteration of the ForEach activity based on the value in the items array?

    Thank you


    Sohi

    Wednesday, April 17, 2019 8:46 PM

Answers

  • Hi there,

    Lookup activity + For each activity should meet your requirement, see the below sample solution:

    1. use a lookup activity to fetch all schema mappings from your configuration table:

    2. pass the output of the lookup activity to 'items' in foreach activity

    3. create a copy activity in foreach activity, reference @item in column mapping

    Useful link: foreach activity: https://docs.microsoft.com/en-us/azure/data-factory/control-flow-for-each-activity 

    lookup activity: https://docs.microsoft.com/en-us/azure/data-factory/control-flow-lookup-activity

    Thursday, April 18, 2019 7:53 AM

All replies

  • Hi there,

    Lookup activity + For each activity should meet your requirement, see the below sample solution:

    1. use a lookup activity to fetch all schema mappings from your configuration table:

    2. pass the output of the lookup activity to 'items' in foreach activity

    3. create a copy activity in foreach activity, reference @item in column mapping

    Useful link: foreach activity: https://docs.microsoft.com/en-us/azure/data-factory/control-flow-for-each-activity 

    lookup activity: https://docs.microsoft.com/en-us/azure/data-factory/control-flow-lookup-activity

    Thursday, April 18, 2019 7:53 AM
  • Hello Sohi and thank you for your question.  Here is a related MSDN thread which I believe has the same goal.
    https://social.msdn.microsoft.com/Forums/azure/en-US/3e87a117-b3e8-4554-b3af-9434a15e9c66/how-to-do-a-dynamic-column-mapping-in-copy-activity?forum=AzureDataFactory
    Here is documentation
    https://docs.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping#schema-mapping
    Friday, April 19, 2019 6:15 PM