0

I am building a parameterised Mapping dataflow pipeline and have run into a problem that I need help with.

My ADF Load is based on a config file, a sample of which is given below:

Config File

I would like the ability to join using the Stagekeys column in my config file using the EXISTS transformation shown below

Mapping Data Flows here

Any suggestions on how I can achieve it?

Kind Regards

Sarath
  • 221
  • 2
  • 9

1 Answers1

1

If my understanding was right we can parameterize key columns and prepare Exists Expression.

FYI, attached condition for single key we can extend that with multi keys as "source1@keyColumn1 == source2@keyColumn1 && source1@keyColumn2 == source2@keyColumn2"

--Dataflow Parameter Dataflow Parameter

--Exists Expressions Exists Condition

For multiple keys from same target table can use following expression and send key columns as array

array(byNames($pKeyColumns,'sourceADLSCSV')) == array(byNames($pKeyColumns,'targetASQL'))

--Pipeline Parameter Pipeline Parameter

--Dataflow Parameter Dataflow Parameter*

--Exists Expressions enter image description here

Suvarna Yenugudhati
  • 11
  • 2
  • Hi Suvarna, this may not work as the code only deals with a business key. I have multiple keys to consider for my JOIN. My keys are loaded into the mapping data flows as an array [a.b,c,d], and each of these columns in the array needs to be in my EXISTS JOIN. – Sarath Apr 23 '22 at 11:32
  • Hi Sarath, is multiple join keys from multiple target able or same target table ? – Suvarna Yenugudhati Apr 23 '22 at 21:07
  • Hi SuvarnaThe multiple key columns are from the same table – Sarath Apr 23 '22 at 22:50
  • changing "byName" to "byNames" should work for multiple keys as array : toString(byNames($pKeyColumns,'sourceADLSCSV')) == toString(byNames($pKeyColumns,'targetASQL')) – Suvarna Yenugudhati Apr 24 '22 at 00:49
  • Hi Suvarna, you get the following error when attempting that. Function 'toString' does not take 5 arguments – Sarath Apr 24 '22 at 07:03
  • 1
    No Sarath, let me try for 5 or more keys then get back to you. – Suvarna Yenugudhati Apr 24 '22 at 19:23
  • Hi Sarath, tested it seems 'toSting' has issue with 5 or more fields better to use 'array'; i.e., "array(byNames($pKeyColumns,'sourceADLSCSV')) == array(byNames($pKeyColumns,'targetASQL'))" – Suvarna Yenugudhati Apr 24 '22 at 22:45
  • In addition 'toString' function only can convert a primitive datatype to a string. – Suvarna Yenugudhati Apr 24 '22 at 22:51