Fetch Azure SQL table data from ADF to use as input in copy activity

0
votes

I have 4 SQL tables.

  1. Employee(which contains employee information)
  2. Engineers( same structure as EMployee but no input)

3)Student( same structure as EMployee but no input)

4)Metadata ( contains the source and destination information such as source: Employee destination: Student)

My goal is to create an ADF pipeline that will fetch metadata table information (source and destination) and will perform copy activity from the source to destination as per data in the Metadata table.

Employee Table

enter image description here

Metadata Table

Flag Source destination 2 dbo.Employee dbo.Engineer

Engineer Table

ID First_name Last_name

I want my ADF pipeline to fetch the source and destination data from Metadata table and perform copy activity from source to destination as per mentioned in Metadata table.

I have tried the below approaches but not succeeded yet.

getMetadata---> copyActivity [ not able to fetch the metadata table data]

getmetadata--->foreach loop----> copy activity [ again for able to fetch the data]

1
azureazure-sql-databaseazure-data-factoryazure-data-factory-2azure-data-factory-pipeline
Please show use theses table schema and the output data which your expect. - Leon Yue
Edited now. Thanks! - Amrita Ghatak
Hi @Amrita Ghatak, You're welcome. Please see my answer. If you have any other concern please feel free to let me know, thank you. - Leon Yue
Does flag indicate which ID to copy or do you copy the entire table? In @LeonYue answer this will copy entire table and insert all rows to the destination - if your id is PK this will fail on any subsequent runs with the same table - Jason Welch
@JasonWelch, I just made a example. You could build a query according the foreach item in source query operation to get the data you want. - Leon Yue

1 Answers

1
votes

You should use Lookup active not Get Metadata active.

According you problem,, please follow this workaround:

  1. Using Lookup active to get the content of the table Metadata. enter image description here
  2. For each active to loop the each row of Metadata: enter image description here
  3. Foreach inner actives, create the copy active which source and sink all set with foreach item. For example: enter image description here enter image description here

HTH.