Not able copy data from ADLS gen2 to SQL Server using ADF

0
votes

I am not able to copy data from ADLS gen2 to SQL Server (its not Azure SQL) using ADF.

What I have done is like this:
Created Data Set: Adls gen2 dataset Src
SQL Server DataSet tgt

But it doesn't allow me to choose tgt as my sink, though it lists down to choose the sink if the data set is either from (Azure SQL or Data Lake).

2
sql-serverazure-sql-databaseazure-data-factoryazure-data-lakeazure-data-lake-gen2
Hi @Vinod Kumar, If my answer is helpful for you, hope you can accept it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Thank you. If you have any other concerns, please feel free to let me know. – Leon Yue

2 Answers

0
votes

You will have to create an Integration Runtime and configure the same in your SQL Server Linked Service in ADF.

SQL Server is supported as sink, you can find the details here

As SQL Server is a different compute environment than Azure, you will have to create IR (Integration Runtime) so that Azure and SQL Server can communicate with each other.

Integration Runtime

0
votes

If you want create on-premise SQL Server as dataset, you must install the Self-hosted integration manually:

  • A self-hosted integration runtime can run copy activities between a cloud data store and a data store in a private network. It also can dispatch transform activities against compute resources in an on-premises network or an Azure virtual network. The installation of a self-hosted integration runtime needs an on-premises machine or a virtual machine inside a private network.

If you're using Data Flow, Data Flow doesn't support self-hosted integration so that we can't use SQL Server as connector: enter image description here

You must use Copy active instead.

HTH.