“source and sink in the copy activity must be connected via the same self-hosted integration runtime.”

Question

I have a requirement where I need to run azure data factory pipeline from copying data from On-Premise database to Azure SQL Server Virtual Machine database(using VM data server due to limitations with azure SQL database and Azure SQL managed instance).

Hence I have created two self-hosted integration Runtime, 1 for on-prem VM database server, and another for azure VM data server.

But when I validate or run the pipeline, getting below error message "source and sink in the copy activity must be connected via the same self-hosted integration runtime."

Can some please suggest the possible solution if any...

Answer 1

Basically it means that you cannot have 2 self-hosted integration runtimes in a single copy activity. What you are trying to do is copy from an onpremise source to another onpremise sink, but the IRs are different.

You will need to use 2 copy activities and an intermediate storage. For example:

1st copy: take the data from the source sql to a blob storage as a csv.

2nd copy: grab that csv and insert its data into sink database.

This way, you will be using only 1 IR for each copy activity.

Hope this helped!