如何在Azure Data Factory中动态填充参数同步本地SQL Server新增表

Step 1: Fetch SQL Server Table List with Lookup Activity #

Add a Lookup activity to your pipeline:

• Configure its source to use your existing local SQL Server linked service.
• In the query field, use this SQL to retrieve all user tables (adjust schema filters if needed):

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' 
-- Add AND TABLE_SCHEMA = 'YourTargetSchema' to limit to a specific schema
-- Add AND TABLE_NAME NOT IN ('ExcludedTable1', 'ExcludedTable2') to skip specific tables

• Disable the First row only option to ensure all table names are returned.

Note: Ensure your SQL Server linked service credentials have permission to query the INFORMATION_SCHEMA views.

Step 2: Transform Lookup Output to a Flat Table Name Array #

The Lookup activity returns an array of objects (e.g., [{"TABLE_NAME": "Customer"}, {"TABLE_NAME": "Order"}]). To convert this into a simple array of table names for the ForEach loop, use this expression:

@activity('Lookup Table List').output.value[*].TABLE_NAME

This expression extracts the TABLE_NAME value from each object, resulting in an array like ["Customer", "Order"].

Step 3: Configure ForEach Activity to Iterate Over Tables #

Add a ForEach activity immediately after the Lookup:

• Set the Items field to the array expression from Step 2.
• Leave Sequential execution disabled for parallel processing (adjust the concurrency limit in settings if you need to control how many tables are copied at once).

Step 4: Add Copy Data Activity Inside the ForEach Loop #

Within the ForEach container, add a Copy Data activity to handle per-table copying:

Source Configuration:

• Use your SQL Server linked service.
• Set the Table name to the dynamic value @item() (this references the current table name in the loop).

Sink Configuration:

• Use your Azure Storage linked service (Blob Storage or ADLS Gen2).
• Set the File path to your target container/folder, with the file name defined as @item() + '.parquet' (ensures a unique Parquet file per table).
• Select Parquet as the sink format, and adjust compression or other format settings as required.

Step 5: Test and Validate #

Run the pipeline to confirm:

• The Lookup activity correctly pulls all existing tables.
• The ForEach loop iterates over each table name in the array.
• Each table is copied to Azure Storage as a properly named Parquet file.

Going forward, any new tables added to your local SQL Server will be automatically detected and copied without manual pipeline edits.

内容的提问来源于stack exchange，提问作者Salah K.