Dynamic parameters in Azure Data Factory in dataflow activity

Question

Dynamic parameters in Azure Data Factory in dataflow activity

Aditya Raj 286

i have use case of reading from azure sql table and iterating over each row .Each row has source and target table name and join condition on which i need to select data and then merge data into target table. I need to do this activity using Azure Data Factory . How can i implement it. I need to make it as generic using dynamic parameters.
As I am trying to merge data from one snowflake table to another, so I am using dataflow
i am getting error

{"StatusCode":"DFExecutorUserError","Message":"Job failed due to reason: at Sink 'sink1'(Line 8/Col 0): Input transformation 'target' not found","Details":""}

I am trying but I am getting error.106261-activity2.pdf

![106196-image.png ]7

ShaikMaheer-MSFT 38,521 Reputation points Microsoft Employee

2021-06-18T08:13:58.127+00:00

Hi @Aditya Raj ,

Could you please help on below clarifications to understand query better and provide detailed solution.

Each row has source and target table name and join condition on which i need to select data

I can understand that your row contains Source Table and Target Table name. but you mentioned that Join condition also will be there. Kindly provide a sample for this.
By seeing your query screenshots, I can understand that you are trying to take data from source table and loading it in to target table. That's it right? Nothing more right? No join is getting used here right? Kindly help to understand this
ShaikMaheer-MSFT 38,521 Reputation points Microsoft Employee

2021-06-21T06:34:07.143+00:00

Hi @Aditya Raj ,

Could you please update on above comment clarifications. So that we can help you in your resolution with detailed explanation. Thank you.
Aditya Raj 286 Reputation points

2021-06-21T10:18:22.077+00:00

Basically I have two table source and target. What I am trying to achieve is merge source tables data to target table i.e, update if data is present in target and insert if not present based on unique columns. For this merge operation only, I need to join on both source and target based on unique columns. This list of source table and their target table ,unique key(list of comma separated unique columns) are column present in another table. List of unique columns on which I need to join data is not fixed ,it is dynamic. I am not sure how to create joins on dynamic list of columns.
Aditya Raj 286 Reputation points

2021-06-21T10:58:00.127+00:00

I tried and getting error : Condition expression doesn't support complex or array type
As i don't know name of columns, it has dynamic columns.
ShaikMaheer-MSFT 38,521 Reputation points Microsoft Employee

2021-06-23T05:10:07.99+00:00

Hi @Aditya Raj ,

Just checking in to see if the below answer provided by @ShaikMaheer-MSFT helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

Accepted answer

1 additional answer

Your answer

ShaikMaheer-MSFT 38,521 Reputation points Microsoft Employee

2021-06-18T08:13:58.127+00:00

Hi @Aditya Raj ,

Could you please help on below clarifications to understand query better and provide detailed solution.

Each row has source and target table name and join condition on which i need to select data

I can understand that your row contains Source Table and Target Table name. but you mentioned that Join condition also will be there. Kindly provide a sample for this.
By seeing your query screenshots, I can understand that you are trying to take data from source table and loading it in to target table. That's it right? Nothing more right? No join is getting used here right? Kindly help to understand this
ShaikMaheer-MSFT 38,521 Reputation points Microsoft Employee

2021-06-21T06:34:07.143+00:00

Hi @Aditya Raj ,

Could you please update on above comment clarifications. So that we can help you in your resolution with detailed explanation. Thank you.
Aditya Raj 286 Reputation points

2021-06-21T10:18:22.077+00:00

Basically I have two table source and target. What I am trying to achieve is merge source tables data to target table i.e, update if data is present in target and insert if not present based on unique columns. For this merge operation only, I need to join on both source and target based on unique columns. This list of source table and their target table ,unique key(list of comma separated unique columns) are column present in another table. List of unique columns on which I need to join data is not fixed ,it is dynamic. I am not sure how to create joins on dynamic list of columns.
Aditya Raj 286 Reputation points

2021-06-21T10:58:00.127+00:00

I tried and getting error : Condition expression doesn't support complex or array type
As i don't know name of columns, it has dynamic columns.
ShaikMaheer-MSFT 38,521 Reputation points Microsoft Employee

2021-06-23T05:10:07.99+00:00

Hi @Aditya Raj ,

Just checking in to see if the below answer provided by @ShaikMaheer-MSFT helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

Answer 1

Hi @Aditya Raj ,

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

To create Join condition dynamically please check below detailed explanation.

Step 1: Create a Parameter in Data flow holds value "depid,depname" and we should use these columns(depid & depname) for join condition dynamically

Step 2: Added Source(employee data) and Sink(department data) transformations

Step 3: Join Transformation. Make sure to select Boardcast as Fixed and check Boardcast options.
Added Join condition dynamically by splitting parameter value.

Also, for SCD type2 implementation you can refer below vlog from product team
https://www.youtube.com/watch?v=tc283k8CWh8

Hope this will help

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

Please accept an answer if correct. 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 email-notifications.

Answer 2

Kiran-MSFT 696 Microsoft Employee

The best option is to use the inline option in dataflow source and sink and pass parameters

Aditya Raj 286 Reputation points

2021-06-17T06:18:28.777+00:00

i i tried it but not working.
Kiran-MSFT 696 Reputation points Microsoft Employee

2021-06-17T06:20:53.977+00:00

Can you paste the DSL script (script button next to code)? Use the inline option for both source and sink
Aditya Raj 286 Reputation points

2021-06-17T06:41:03.01+00:00
Kiran-MSFT 696 Reputation points Microsoft Employee

2021-06-17T06:42:34.457+00:00

Click on the script button on the canvas..it is the top right corner. UI screens can miss detail
Aditya Raj 286 Reputation points

2021-06-17T06:45:46.543+00:00

parameters{
parameter1 as string,
parameter2 as string
}
source(allowSchemaDrift: true,
validateSchema: false,
format: 'query',
query: ('select * from '+$parameter1),
store: 'snowflake') ~> source
source sink(allowSchemaDrift: true,
validateSchema: false,
format: 'table',
tableName: ($parameter2),
schemaName: 'PUBLIC',
deletable: false,
insertable: true,
updateable: false,
upsertable: false,
store: 'snowflake',
skipDuplicateMapInputs: true,
skipDuplicateMapOutputs: true,
stageInsert: true) ~> sink2
ShaikMaheer-MSFT 38,521 Reputation points Microsoft Employee

2021-06-24T09:16:54.49+00:00

Hi @Aditya Raj ,

Thank you for accepting answer.

Share via

Dynamic parameters in Azure Data Factory in dataflow activity

1 additional answer

Your answer