Azure Data Factory Copy Data activity - Use variables/expressions in mapping to dynamically select correct incoming column

Question

I have the below mappings for a Copy activity in ADF:

"translator": {  
            "type": "TabularTranslator",  
            "mappings": [  
                {  
                    "source": {  
                        "path": "$['id']"  
                    },  
                    "sink": {  
                        "name": "TicketID"  
                    }  
                },  
                {  
                    "source": {  
                        "path": "$['summary']"  
                    },  
                    "sink": {  
                        "name": "TicketSummary"  
                    }  
                },  
                {  
                    "source": {  
                        "path": "$['status']['name']"  
                    },  
                    "sink": {  
                        "name": "TicketStatus"  
                    }  
                },  
                {  
                    "source": {  
                        "path": "$['company']['identifier']"  
                    },  
                    "sink": {  
                        "name": "CustomerAccountNumber"  
                    }  
                },  
                {  
                    "source": {  
                        "path": "$['company']['name']"  
                    },  
                    "sink": {  
                        "name": "CustomerName"  
                    }  
                },  
                {  
                    "source": {  
                        "path": "$['customFields'][74]['value']"  
                    },  
                    "sink": {  
                        "name": "Landlord"  
                    }  
                },  
                {  
                    "source": {  
                        "path": "$['customFields'][75]['value']"  
                    },  
                    "sink": {  
                        "name": "Building"  
                    }  
                }  
            ],  
            "collectionReference": "",  
            "mapComplexValuesToString": false  
        }  

The challenge I need to overcome is that the array indexes of the custom fields of the last two sources might change. So I've created an Azure Function which calculates the correct array index. However I can't work out how to use the Azure Function output value in the source path string - I have tried to refer to it using an expression like @activity('Get Building Field Index').output but as it's expecting a JSON path, this doesn't work and produces an error:

JSON path $['customFields'][@activity('Get Building Field Index').outputS]['value'] is invalid.

Is there a different way to achieve what I am trying to do?

Thanks in advance

Answer 1

Hi @Jonathan Nathanson ,

Thank you for posting query in Microsoft Q&A Platform.

Are you able to see the output of Azure function in the output json of Azure Function activity? I hope your azure function activity output json holds that data.

You need to see the output json of Azure function activity to understand which key exactly holds your index value and write expression accordingly to access it.

For example, let's say azure function activity name is Get Building Field Index and in the output json of it index key holds key value. Then we need to write expression as below.
@activity('Get Building Field Index').output.index

Please check below video that helps to explain how to write expressions to read values from output json of activities.
How to read JSON output of one Activity in to another Activity in Azure Data Factory

Hope this helps. Please let me know if any queries.

--------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification