Dynamic sql server table name inside copy activity

Question

There is a copy activity inside the ADF pipeline which in the source tab has the linked service to sql server and the table option is selected and in the query section I have placed

select * from tbl_company

This works fine

Now I want to make this table name dynamic by passing in a parameter, p_table_name

How can I have a sql query in the query box to have something like:

select * from @pipeline.parameters.p_table_name

thank you

Answer 1

Hi @arkiboys  
Thanks for the question and using MS Q&A platform.

You're on the right track! To make the table name dynamic in your Copy activity, you can use a parameterized query. However, you can't directly use the @pipeline.parameters.p_table_name syntax in the query box.

Instead, you can use the @{...} syntax to reference the parameter and concatenate it with the rest of the query. Here's an example:

SELECT * FROM @{pipeline().parameters.p_table_name}

By using @{...}, you're telling ADF to evaluate the expression inside as an Azure Data Factory expression. The pipeline().parameters.p_table_name part retrieves the value of the p_table_name parameter from the pipeline. When you run the pipeline, ADF will replace @{pipeline().parameters.p_table_name} with the actual table name value passed as a parameter.

Make sure to define the p_table_name parameter in your pipeline parameters and pass the desired table name as a value when you execute the pipeline.

Alternatively, you can also use the query property in the dataset to achieve this. In your SQL Server dataset, go to the "Query" tab and enter:

SELECT * FROM @@{p_table_name}

Then, in your Copy activity, select the dataset and the query will be executed with the dynamic table name.

Hope this helps! Let me know if you have any further questions.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.