topic Re: Can't load data from SQL Server into Lakehouse. Can't add a data destination. in Dataflow

Can't load data from SQL Server into Lakehouse. Can't add a data destination.

cristinaojer — Mon, 22 Apr 2024 16:25:55 GMT

I'm facing some issues when trying to get data from SQL Server database into Fabric Lakehouse. I'm using an on-premises data gateway updated up to it’s latest version.

We’ve tried to get this data through a Copy Activity. When running the pipeline, the process starts but never ends. We made a trial just o ingest 4 rows from the source table but the execution of the pipeline still never ends. Source data is read since we could preview it through ‘preview data’ button when configuring the activity. It seems the problem is when loading data into the lakehouse.
We’ve tried to get this data also through a Dataflow Gen2. In this case, we could query the source table and transform the data, but when trying to add a destination for the data, an unkwon error is reported

Is this a common error? How could we troubleshoot it?

Re: Can't load data from SQL Server into Lakehouse. Can't add a data destination.

amitchandak — Tue, 23 Apr 2024 02:47:15 GMT

cristinaojer , For some customers it worked with the March 2024 gateway, but for some, it worked only with the April 2024 Gateway(Data Pipelines). But with April 2024 many of our dataflows are failing with false error.

Re: Can't load data from SQL Server into Lakehouse. Can't add a data destination.

v-cboorla-msft — Tue, 23 Apr 2024 03:59:05 GMT

Hi cristinaojer

Thanks for using Microsoft Fabric Community. amitchandak thank you for sharing the details.

As we have two releases in the month of April.

April 2024 update (3000.218.8)

April 2024 update (3000.218.7)

Request you to install the latest version of on-prem gateway and try to establish the connection, this might help you.

If the issue still persists, please do let us know we will try to help.

Hope this helps.

Thank you.

Re: Can't load data from SQL Server into Lakehouse. Can't add a data destination.

v-cboorla-msft — Thu, 25 Apr 2024 09:15:11 GMT

Hi cristinaojer

Thanks.

Re: Can't load data from SQL Server into Lakehouse. Can't add a data destination.

v-cboorla-msft — Mon, 29 Apr 2024 05:33:44 GMT

Hi cristinaojer

When using Microsoft Fabric Dataflow Gen2 with an on-premises data gateway, you might encounter issues with the dataflow refresh process. The underlying problem occurs when the gateway is unable to connect to the dataflow staging Lakehouse in order to read the data before copying it to the desired data destination. This issue can occur regardless of the type of data destination being used.

The firewall rules on the gateway server and/or customer's proxy servers need to be updated to allow outbound traffic from the gateway server to the following:

Protocol: TCP
Endpoints: *.datawarehouse.pbidedicated.windows.net, *.datawarehouse.fabric.microsoft.com, *.dfs.fabric.microsoft.com
Port: 1433

Please refer to the documentation for more details : Set new firewall rules on server running the gateway

For similar issue please refer : Error when trying to create a dataflow gen 2. - Microsoft Fabric Community

I hope this information helps.

Thank you.

Re: Can't load data from SQL Server into Lakehouse. Can't add a data destination.

v-cboorla-msft — Tue, 30 Apr 2024 09:58:17 GMT

Hi cristinaojer

Thanks.

Re: Can't load data from SQL Server into Lakehouse. Can't add a data destination.

v-cboorla-msft — Wed, 01 May 2024 17:46:03 GMT

Hi cristinaojer

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others .
If you have any question relating to the current thread, please do let us know and we will try out best to help you.
In case if you have any other question on a different issue, we request you to open a new thread.

Thanks.

Re: Can't load data from SQL Server into Lakehouse. Can't add a data destination.

DerekCC — Thu, 02 May 2024 17:24:52 GMT

I am facing a very similar issue. On Prem SQL through an OPDG to a Lakehouse using CopyData. The table only has 7 rows, it previews, but it never writes any data; all I receive after it times out is a generic error: InternalServerError executing pipeline Fabric

I just updated my OPDG to the most recent version (3000.218.9 - April 2024) in hopes that this would help, but no difference.

Re: Can't load data from SQL Server into Lakehouse. Can't add a data destination.

cristinaojer — Fri, 03 May 2024 06:31:42 GMT

Hi v-cboorla-msft,

Thank you very much for the response. Gateway admins opened firewall rules and data loading worked fine for DataFlow Gen2! But I'm still having the problem with Copy Activity... I'm waiting for my collegues to update the last version of the gateway and I'll write back to this post with the result

Thanks,

Re: Can't load data from SQL Server into Lakehouse. Can't add a data destination.

brewed — Sat, 04 May 2024 14:48:22 GMT

Experiencing the sames issues. Dataflow Gen2 successfully loads onprem SQL tables to Fabric lakehouse tables, but a copy activity in a Fabric pipeline dosn't work. I also setup an ADF pipeline to copy to the lakehouse and get the same results.

The copy activity is creating the table in the lakehouse as expected. When looking at the underlying files of the table, a folder called '_delta_log' is there with files small json files within it. Looks like each of these files is being created after a timeout occurs? Here's the contents of the json:

{"protocol":{"minReaderVersion":1,"minWriterVersion":2,"readerFeatures":null,"writerFeatures":null}}

{"metaData":{"id":"8db4335b-4ae9-4b5e-8262-3e5096d44d39","name":"delta","description":"","format":{"provider":"parquet","options":{}},"schemaString":"{\"type\":\"struct\",\"fields\": <columns names removed>]}","partitionColumns":[],"createdTime":1714817850126,"configuration":{}}}

{"commitInfo":{"operation":"Write","engineInfo":"Microsoft.DI.Delta","additionalCommitInfo":null}}

No records are being inserted into the lakehouse. Data preview works in the pipeline.

I also tried landing the files as lakehouse files instead of tables, but still did not work. Seems like the issue is with writing data to the lakehouse from a pipeline copy activity (either Fabfic or ADF).

Re: Can't load data from SQL Server into Lakehouse. Can't add a data destination.

cristinaojer — Tue, 07 May 2024 07:06:12 GMT

So do I, but same result.

Re: Can't load data from SQL Server into Lakehouse. Can't add a data destination.

DerekN_CC — Wed, 08 May 2024 15:36:02 GMT

v-cboorla-msft I'm curious if you have any feedback on this issue.

Re: Can't load data from SQL Server into Lakehouse. Can't add a data destination.

re84uk — Fri, 17 May 2024 13:19:57 GMT

We still have this issue, endless in progress status when attempting to load data from on-prem SQL Server to Lakehouse via on-prem gateway. We have an MS support ticket logged, unfortunately at this time no one seems to have a resolution. This is extremely frustrating as due to dataflows gen2 not being as parameterisable as data pipeline's, setting up initial data ingestion is very time consuming.

Re: Can't load data from SQL Server into Lakehouse. Can't add a data destination.

re84uk — Tue, 11 Jun 2024 09:33:13 GMT

Do we know of any progress on this? I have a support ticket logged with Microsoft however not really getting anywhere.