Azure Data Factory - Lookup Activity

Question

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;

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;

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

Answer 1

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.