Fabric SQL DB connector from Data Factory

[Mayumi_2021]

I am trying to copy data from an on-premise SQL DB and to SQL DB on Fabric (which is in Preview). I am using Fabric Data Factory for the copy activity. I can preview data from the source which is using connection on Data gateway. I can also view target table while connecting to the Fabric SQL DB connection that is there on the catelog because data factory and SQL DB are on the same workspace. However while loading data, my pipeline is failing with error : The activity was running on gateway, but some cloud connections are incompatible with it. To ensure compatibility, please enable the option "Allow this connection to be used with gateways.".

I am failing to understand where to enable this option as I am getting the target connection from the suggestion

[Mayumi_2021 · Accepted solution]

Hi @BashirIsmail 
Unfortunately not resolved yet. As an workaround, I am landing my data in LH as a middle step and can do LH to SQL DB transfer.

[keith_smith1]

I think this error is popping up as the result of a change made on the Fabric side. Comparing an old Copy Task with a new one side by side reveals a new field. It appears the old Copy Tasks are unaffected by the new change which requires you to manually allow a Fabric resource to connect to a Data Gateway resource. This surely lead to confusion for us when all of the existing Copy Tasks were working fine but the new ones threw this error.

To resolve this you must allow Gateway connections on the destination connection. Open your Copy Task, click the Destination tab, and next to your Connection you can press Edit and there you will find the "Allow this connection to be used with data gateways" checkbox.

[v-veshwara-msft]

Hi @Mayumi_2021 ,

We’re following up  regarding your query. If you have raised a support ticket with Microsoft, could you please confirm whether the issue has been resolved through the support?

If so, we kindly encourage you to share the resolution or any key insights here, as it may benefit others facing similar challenges.

Also, thank you for mentioning the temporary workaround of landing data in the Lakehouse as an intermediate step before transferring it to the SQL DB - that’s helpful information for the community.

If you need further assistance in the future, please feel free to create a new thread in the Microsoft Fabric Community Forum. We’ll be glad to support you.

Thank you for your participation and understanding.

[BashirIsmail]

did you get solution for this issue, because I facing the same issue ?

[Mayumi_2021 · Accepted solution]

Hi @BashirIsmail 
Unfortunately not resolved yet. As an workaround, I am landing my data in LH as a middle step and can do LH to SQL DB transfer.

[Mayumi_2021]

Hi,

This problem is still not solved. The SQLDB connection name is automatically appearing while choosing the connection (Like the Lakehouse and warehouse in the same workspace), I have not created the connection so that I can change any parameter on it. 

Even though I create a connection for the sql DB, it does not appear in the connection dropdown in pipeline.

[v-veshwara-msft · Accepted solution]

Hi @Mayumi_2021 ,
Thanks for getting back and sorry for late reply.

Since your source is an on-premises SQL DB, the activity is running on a gateway. However, Fabric SQL DB is a cloud-based service, and cloud connections are not compatible with the gateway by default. To resolve this issue, you need to manually create a connection for Fabric SQL DB and enable the option "Allow this connection to be used with gateways" in the connection settings.

Steps:

1. Go to Settings --> Manage Connections and Gateways.
2. Create a new connection for Fabric SQL DB.
3. In the connection settings, enable "Allow this connection to be used with gateways."
4. Save the changes and retry the pipeline execution.

If the issue persists, check the error logs and ensure the correct authentication method is used. If further assistance is needed, consider raising a support ticket. 
Microsoft Fabric Support and Status | Microsoft Fabric

Hope this helps.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.

Best Regards,
Vinay.

[v-veshwara-msft]

Hi @Mayumi_2021 ,

Thanks for posting in the Microsoft Fabric Community.

You can find the "Allow this connection to be used with gateways" option in the Data Connections settings:

1. Navigate to Settings-->Manage Connections and Gateways.
2. Locate your data connection and open its settings.
3. Enable the option to ensure compatibility with the gateway.

Once enabled, save the changes and retry your pipeline. This should resolve the issue and allow data to flow successfully from the on-premises SQL DB to Fabric SQL DB.

Please refer to this thread for further details:
Solved: Fabric - Microsoft Fabric Community

Let us know if you need any additional assistance!

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.

Best Regards,
Vinay.

[v-veshwara-msft]

Hi @Mayumi_2021 ,
We haven't heard from you since last response and just wanted to check whether the solutions provided met your needs. If yes, please accept as solution to help others benefit. If not, please reach out.
Thank you.

[lbendlin]

you do that on your gateway

Merge or append on-premises and cloud data sources - Power BI | Microsoft Learn

[Mayumi_2021]

Hi @lbendlin ,
I already have that set up in the gateway. 
Since the error says "Allow this connection to be used with gateways.", It seems I need to do something in the cloud connection which I am unable to do anything.