Dynamic Data sync between Azure synapse analytics to SQL server

We have a requirement to set up a pipeline in ADF that copy all tables of Azure synapse analytics to SQL server dynamically.
It should select all tables dynamically from synapse analytics db and copy the same table at the destination SQL server. It should be set up as incremental load.

This can be achieved by using lookup, foreach, and copy activities.

Steps:

We need 3 datasets here.

1) Create Synapse analytics dataset for lookup activity (source server)
2) Create Synapse analytics dataset for copy source (source server)
3) Create Azure SQL dataset for copy sink (destination server)

Leave table as empty for Synapse dataset (for lookup activity)
Screenshot for analytics dataset for lookup activity:

Lookup:

On the canvas drag the lookup activity.

Select query option and enter: select name from sys.tables

This query will give all the list of tables from your synapse database.

Foreach:

Drag the foreach and connect to lookup.

On the settings tab enter: @activity(‘Lookup1’).output.value

Click the pencil icon on the activities and drag the copy activity. Connect the source dataset (synapse)

Copy source dataset:

On the source dataset, add a string parameter and dynamically map that parameter in the connection settings tab

Parametername: Inputtable_name
Table: @dataset().Inputtable_name

Copy data activity:

On the source, You will see the Parameter.
Then Add, @item().name
in the data set properties(we are taking the name from the lookup activity, which is our source table names)

On the sink:( In the copy activity)
Sink dataset:
Similarly, add a string parameter and dynamically map that parameter in the connection settings tab on the sink dataset
Parametername: Destinationtable
Table: @dataset().Destinationtable

On the Sink, You will see the Parameter.
Add- @item().name
And select autocreate table option.

Run the pipeline.

Note: when it comes for large tables, more than 5000, see the below.
• The Lookup activity can return up to 5000 rows; if the result set contains more records, the first 5000 rows will be returned.
• The Lookup activity output supports up to 4 MB in size, activity will fail if the size exceeds the limit.
• The longest duration for Lookup activity before a timeout is 24 hours.
• When we use a query or stored procedure to lookup data, make sure to return one and exact one result set. Otherwise, the Lookup activity will fail.