Write to destination error On Dataflow Gen2 with On-premise Data gateway(OPDG)

An important consideration for Dataflow Gen2 used for ETL with OPDG is your network firewall settings.

When you read data from on premise data sources into Fabric, most likely it may succeed. However, you could encounter error when writing the data to a fabric destination (Lakehouse, Warehouse etc.) as shown below:

When a dataflow Gen 2 fails, check the errors to identify the root cause. Most cases, it is because port 1433 has been blocked on your internal network firewall setting.

To resolve the issue, simply set the rule to allow port 1433 on the OPDG machine in your network.

In Fabric the following are important OPDG network firewall settings:

·        Protocol: TCP

·        Endpoints: .datawarehouse.pbidedicated.windows.net, .datawarehouse.fabric.microsoft.com, *.dfs.fabric.microsoft.com

·        Port: 1433

Check to be sure your firewall rules are configured properly to allow fabric write to destination.

What if you can’t change the firewall rules for any reason, what can you do?

Here is a work around:

·        Split the dataflow into two.

·        First dataflow read data from your on-premises data source and store into a staging area in Fabric. Ensure you delete the destination option on this dataflow.

·        The second dataflow reads data from your Fabric staging and write to your desired destination in Fabric e.g Lakehouse.

Note: You’ll need to disable Enable staging in the second dataflow to avoid duplicated staging data.

Reference : On-premises data gateway considerations for data destinations in Dataflow Gen2 - Microsoft Fabric | Microsoft Learn