azure-synapse

Synapse pipeline datasets

heyy guys,

I am currently in the process of ingesting data from SQL Server to my storage account for Synapse Analytics. I originally selected around 10 tables I wanted to pull in from SQL server, and managed to import them to my bronze layer, the wizard did all of the hard parts like creating the pipeline, datasets etc to pull in the data.

But now there is another table I want to pull in, and I don't know how to adjust the pipeline/dataset to include this additional table :(. I know that I will need it in the future when I decide on some of the other tables I want to pull in, would really appreciate any advice on this.

Thanks in advance :)

Solution

  • You can follow the below approach to dynamically copy Azure sql tables to sink

    Step 1 : SET VARIABLE Create a list of Array for the list of table names and using them in SET VARIABLE Activity.

    Provide the new table in the Array list.

    enter image description here

    Step 2: FOR EACH In FOR EACH items use @variables('tables') You can utilize the Edit feature in the SQL dataset.

    enter image description here

    Step 3: You can define a dataset parameter for the Source table name. Enable the Edit checkbox in the SQL Source dataset and use this dataset parameter.

    enter image description here

    Optionally, you can also use a dataset parameter for the database name. In this example, the database name is specified directly as 'dbo'.

    enter image description here

    Step 4: Copy Activity Insdie the FOR EACH use the copy activty to move the data. enter image description here