How to Pass Set Variable Output Value to SQL Query Where Clause in Copy Activity in Azure Data Factory

Kumar 41 Reputation points
2022-08-16T08:05:31.457+00:00

231481-overallflow-diagram.pngHi Experts,

I have a requirement to pass the variable value output into Sql query where clause in Copy Activity(Azure Data Factory).

Need help to assign variable value in Where clause condition - Not sure what syntax we have to follow.

Query:
SELECT columns......
FROM TABLE1 a WHERE Column1 (
Col2,
Col3,
Col4,
TO_DATE (
(SELECT TO_CHAR (EFFECTIVE_END_TIMESTAMP,
'MM/DD/YYYY:HH:MI:SSAM')
FROM TABLE2
WHERE C_NUMBER = variables('V_LKPOUTPUT')),
'MM/DD/YYYY:HH:MI:SSAM'),
TO_DATE (
(SELECT TO_CHAR (DATA_AS_OF_TIMESTAMP, 'MM/DD/YYYY:HH:MI:SSAM')
FROM TABLE2
WHERE C_NUMBER = variables('V_LKPOUTPUT')),
'MM/DD/YYYY:HH:MI:SSAM')) = 'A'
AND col5 = 'N'
and Col6 is null
and Col7 = 'Y';

231299-variables-passing-error-in-copyactivity-sqlquery-w.png

Please find the attached screenshot for quick reference to see how the SQL query is, All i need is how to frame the syntax to assign variable in where clause.

Thanks, Greatly Appreciated your help.

Regards,
Kumar

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,517 questions
0 comments No comments
{count} votes

Accepted answer
  1. Nandan Hegde 29,886 Reputation points MVP
    2022-08-16T08:14:46.483+00:00

    Hey,
    You can use
    @{variables('<<variablename>>')}

    like

    SELECT columns......
    FROM TABLE1 a WHERE Column1 (
    Col2,
    Col3,
    Col4,
    TO_DATE (
    (SELECT TO_CHAR (EFFECTIVE_END_TIMESTAMP,
    'MM/DD/YYYY:HH:MI:SSAM')
    FROM TABLE2
    WHERE C_NUMBER = @{variables('v_Lkpoutput')}),


1 additional answer

Sort by: Most helpful
  1. Kumar 41 Reputation points
    2022-08-17T07:30:46.21+00:00

    The variable type changed from 'Array' to 'String' fixed the issue.

    Formula i used to pick only value from the JSON Response from Lookup output: @last lion (split(activity('Lookup').output.value[0].PRODUCTION_CYCLE_NUMBER,':'))

    Since the issue is fixed, I'm closing this question/conversation.

    Highly appreciated your kindness for attempting to help and spending your precious time dear @NandanHegde. Thanks,

    Regards,
    Kumar.

    0 comments No comments