Azure Data Factory - Lookup Activity

Vivek Komarla Bhaskar 911 Reputation points
2022-11-08T12:15:13.84+00:00

I'm calling a procedure using a lookup activity in Azure Data Factory.
NOTE: The reason to use Lookup here is, I wanted to store the OUTPUT parameter value from procedure into a variable in ADF for future use.

Below works,
DECLARE @ADFOutputMsg VARCHAR;
EXEC Test.spAsRunTVA @ReportDate = '2022-06-01', @OutputMsg = @ADFOutputMsg OUTPUT;
SELECT @ADFOutputMsg As OutputMsg;

258255-works.png

But when I want to pass dynamic parameters, it doesn't like,
DECLARE @ADFOutputMsg VARCHAR;
EXEC @{pipeline().parameters.SchemaName}.spAsRunTVA @ReportDate = @{substring(pipeline().parameters.FileName,8,10)}, @OutputMsg = ADFOutputMsg OUTPUT;
SELECT @ADFOutputMsg As OutputMsg;

258268-doesnt-work.png

Please if someone can suggest me a fix for this, I would like to pass dynamic values to the Query.

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

Accepted answer
  1. Anonymous
    2022-11-08T14:24:17.853+00:00

    I suspect it is because you are missing the ' marks around the date in the second version. Maybe something like this

    EXEC @{pipeline().parameters.SchemaName}.spAsRunTVA @ReportDate = @{substring(pipeline().parameters.FileName,8,10)}, @OutputMsg = ADFOutputMsg OUTPUT;

    I am not sure if you can add them directly like this but something similar is needed so they are part of the sql statement.


0 additional answers

Sort by: Most helpful