SQL dynamic query in ADF

Question

I have SQL dynamic query with a "?" that needs to be replaced with pipeline parameters. The fetched query has a question mark "?" how to replace that?

I tried passing the parameters but ADF doesn't recognize the ? symbol and the correct parameters are not passed

Answer 1

• You can either use concat function to concatenate the query with your dynamic content i.e., your parameters or you can use string interpolation (@{...}).
• Either way, you can build a dynamic query where you will be using parameters (with values as you wish).
• Consider, you want to build a query as specified in the comment, and have 2 parameters as shown in the below image:

• You can use the following dynamic content to build the query. Enclose the parameter within @{...} (string interpolation) wherever required:

Declare @SourceID int, @datafileid int;
Set @SourceID=@{pipeline().parameters.source_id};
Set @datafileid=@{pipeline().parameters.datafield}

• The above would generate the query as shown in the below image:

• You can either directly use similar dynamic content wherever you are using the query or store the value in a set variable activity and use that variable in place of query.