Can I use headers of a table in pipeline query dynamically in azure data factory?

Question

I want to use the name of headers in pipeline query dynamically. Example :- Consider there are 2 tables employee and client.

I want to make a pipeline to copy these tables from source to sink in azure data factory. But I want to write a query for that by choosing specific columns, I want. Is there any way to choose those columns dynamically. I can use table name dynamically by using parameters.

Answer 1

You pull the column list of a table using lookup activity.

As shown below, I am getting the column list of table tb1 using the below query. This query pulls the column names from the Information schema based on the given schema and table values. And using the string_agg() function, we are merging all column_name rows to a single string with a comma delimiter.

You can pass the table and schema name dynamically.

SELECT STRING_AGG( ISNULL(COLUMN_NAME, ' '), ',') AS COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'tb1'

Lookup activity:

Lookup activity output:

You can pass this lookup output to a variable or use it directly in the later activities or in your query in place of the column list.

@activity('Lookup1').output.firstRow.COLUMN_NAME

Variable output:

Answer 2

Here's a similar stackflow question that you can use Suggested way for ADF to trigger pipeline by SQL table change Try to search this up and find similar questions on google