There is a bug in the copy activity when copying data from an on-prem source to a Fabric warehouse.
BTW I have a support ticket open for this on which I'm gaining zero traction despite providing intricate detail.
My pipeline is set up as follows:
- Source: Azure Database for Postgres SQL (connecting via on-prem gateway)
- Destination: Warehouse within same workspace
- Staging: External Azure data lake gen 2 storage account (connecting via on-prem gateway)
Result:
- Data is successfully retrieved from the Azure postgres DB via the on-prem dateway
- Data is successfully copied to the staging folder in the Azure data lake gen 2 storage account via the on-prem gateway
- Data is not copied from the staging folder to the Fabric warehouse as it bypasses the on-prem gateway when connecting to the storage account. This is despite the connection being configured to use an on-prem gateway and succeeding for the step above.
Storage account logs
If there's anything in Fabric that actually works without an elaborate workaround then I'm yet to find it 🙄.
In may case, it appears that my gateway connection stayed up and I see my data in Fabric DW as below. Root cause in your scenaro seems to be connection switch from gateway. We need to look at it further. I will update support team on this.
I will update support team on this. When can we talk? Let us sync up off-line.
Thanks,
Sunil
Hi @tsunloan
I am from product group. I was able to copy mysql on-prem to Fabric DW using gateway. I had to make sure staging also connects through gateway. If needed, we can hop into call and I will go over how my setup works. I see you have not been able to get proper help. You rightfully asked for product group help. Here we are.
Source settings:
Staging Settings:
HI @sunil_sabat . I would love to have a call to run through it with you. I'll send you a PM with my contact details.
The staging storgage account is configured to use the on-prem data gateway and the storage account logs show that the gateway is used to connect for most of the connections from the pipeline but at the very end of the flow, the data warehouse SQL service attempts to connect directly to the storage account without going via the gateway and fails.
Staging account config. Test connection succeeds and I can browse to select the storage path
Storage account logs for pipeline execution.
It copies the data to the staging account successfully but then the warehouse fails to retrieve it from staging as it's connecting without using the gateway. The connections in red are from the Azure SQL service (warehouse).
This doesn't work either as it compains about the data type... The source table I'm testing is as simple as it gets having nothing but two varchar columns.
I did have some luck going from Azure Postgres -> Lakehouse filesystem -> Data warehouse with staging disabled.
It works for a single test table but I'm yet to try and pump hundreds of tables through it (our target end state).
It's rather silly going from Structured -> Unstructured -> Structured.
It's also silly introducing yet another workaround to cater for another fundamental product design flaw in Fabric.
Our goal here is simplify our data arcthitecture by replacing Azure Synapse with Fabric... It's no simpler with all of these workarounds.
