The Data flows only supports Azure Integration runtime. If your source data doesn't need any data flow transformations and if you want only a copy to on-prem SQL database, you can directly use copy activity which supports self-hosted IR.
If your source data from storage needs dataflow transformations, then first transform source data to storage account using Azure IR in data flows. After that, use copy activity to copy the transformed data from storage account to on-prem SQL database.
Please go through the below sample demonstration:
First create the required datasets. In this sample we need 3 datasets. one for source data from storage account, second one to store transformed data in storage account and the last one is on-prem dataset using SQL server linked service and self hosted IR (Here I am using Azure SQL database for sample).
Source data from storage account:
Used csv for sample.
Dataset to store transformed data:
For final target, give your on-prem dataset in copy activity sink.
Use Data flow to trasform:
Give the source and sink datasets.
In the sink settings give our same file name to transform the data to a single file in storage account.
Now the data will be stored in the above storage account. Use this as source in the copy activity.
Copy activity to copy transformed data to on-prem:
Source:
In sink give your on-prem dataset.
You can get the data like below.
NOTE:
Here I have used Azure SQL database in copy sink, you can use on-prem SQL database .
