I have the following query in our ADF Copy Activity
SELECT
deltaTable.*
FROM Data.deltaTable
LEFT OUTER JOIN Data.targetTable
ON deltaTable.signature = targetTable.signature
WHERE targetTable.signature IS NULL
Can someone let me know how to parameterize the query. When I attempt to parameterize the query I get the error:
Parameter schema was not found under EX_SourceToRaw_Single_Table
The following code is my attempt:
@concat('SELECT * FROM ',pipeline().parameters.schema,'.',pipeline().parameters.DeltaTable)
LEFT OUTER JOIN pipeline().parameters.schema,'.',pipeline().parameters.TargetTable)
ON pipeline().parameters.DeltaTable).signature = pipeline().parameters.TargetTable).signature
WHERE pipeline().parameters.TargetTable).signature IS NULL
The deltaTable and the TargetTable both look like the following:
==========================================================================================================
| CountryName | CountryISO2 | CountryISO3 | SalesRegion | signature |
==========================================================================================================
| Belgium | CHA | 10 | EMEA |800e559a27d68f0478b6|
| | | | |1c4c9f009e2418e86697|
| | | | |1b6e54b549b51b1367ab|
| | | | | 450d |
----------------------------------------------------------------------------------------------------------
| Wales | steveO | WAL | Welsh |e8c5149d54986dfe9ac9|
| | | | |5a60a76b07603fe17c28|
| | | | |2b552ec8255f123b279a|
| | | | | 533a |
----------------------------------------------------------------------------------------------------------
| Germany | DE | deletedupd | EMEA |1232b1bd91d14a87ed83|
| | | | |0f770d74cd8cabb87153|
| | | | |5c4c2b7ff5bcb873fa80|
| | | | | d851 |
----------------------------------------------------------------------------------------------------------
| Italy | IT | ITA | EMEA |584cf66de2f4af9eb4db|
| | | | |febefea808b1b4e6a357|
| | | | |87fcac1061de88cfb798|
| | | | | 56df |
----------------------------------------------------------------------------------------------------------
After creating the schema
, DeltaTable
,TargetTable
parameters in ADF pipeline, use the below expression in the query option of the copy activity. Here, for sample I have used lookup activity query option which will work same as copy activity query option.
@concat('SELECT ',pipeline().parameters.DeltaTable,'.* FROM ',pipeline().parameters.schema,'.',pipeline().parameters.DeltaTable,' LEFT OUTER JOIN ',pipeline().parameters.schema,'.',pipeline().parameters.TargetTable,' ON ',pipeline().parameters.DeltaTable,'.signature = ',pipeline().parameters.TargetTable,'.signature WHERE ',pipeline().parameters.TargetTable,'.signature IS NULL')
Open the dynamic content in the query and give the above expression as shown below.
To check generated query, you can go to the pipeline run -> activity run -> Input. Here, it will show the query that was generated from the above expression. You can see it generated the required query.
If you get any error, you can cross the check the generated query here and can modify it as per your requirement.