CopyData from OnPremise connection stuck in QUEUED status

[wi11iamr_]

I have an On Premise Data Gateway using Windows Authentication to a connect to a SQL Server database. (screenshot below)

I'd like to build a pipeline that copies data from my OnPremise SQL Server to a new Azure SQL Database.

When creating a Pipeline I select this gateway as my connection; the connection tests successful within the CopyData activity, and it correctly lists the databases on that server. It also successfuly Previews Data from any selected table.

When I run the pipeline it remains stuck in QUEUED status indefinitely. I left it overnight and for 15hours, and it remeined QUEUED.

To simplify my testing I changed to use a very simply Query (select N'9876' as ItemNo) in my source. This also remaines QUEUED.

In a 2nd test, using this same simple query, I just changed my connection from the Gateway to another test Azure SQL Database that I have - this ran perfectly and completed within 20seconds.

This then confirms for me my problem lies with the OnPremise Data Gateway, however I find no errors reported by the Pipeline and all components test successfully.

I'd appreciate any advice on where I might find some logs to interrogate more detail about the Pipeline's actions, and/or the OnPremise Gateway's ecord of incoming connections etc.

PowerBI Data Connection to OnPremise GatewayPipeline config using OnPremise Data GatewayPipeline Status stuck in QUEUED status

[Fgarcia1986]

Hi,

I was facing the same problem today(04-24-2024)

First of all, i updated my gateway for the latest version and then i tryed again and the activity status was queued yet.

I went again to copy data activity -> source -> database and selected again my database source and worked on both modes append and overwrite.

In your case either you have to update again the gateway or select the database again.

Check as solution if worked to help the community.

Cheers

[marcoG]

For me it continues to remain in the queue forever even with the latest version of the gateway.

I use a proxy for the internet connection

[wi11iamr_]

Confirming that while a Pipeline task is unsuccessful using the OnPrem Gateway and remains in QUEUED state, changing instead to a Data Flow Gen 2 task using the same OnPrem Gateway connection works just fine.

[v-cboorla-msft]

Hi @wi11iamr_ 

Thanks for using Microsoft Fabric Community.

Could you please upgrade your on-prem gateway version and try to run the pipeline.

For latest version : On-premises data gateway

For additional information you can refer to the similar thread : Fabric Data Pipeline - Copy activity stays in queu... - Microsoft Fabric Community

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

I hope this information helps.

Thank you.

[v-cboorla-msft]

Hi @wi11iamr_ 

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.
Otherwise, will respond back with the more details and we will try to help.

Thanks.

[marcoG]

I think it is the same problem:

https://community.fabric.microsoft.com/t5/Data-Pipelines/The-copy-from-sql-server-on-premise-has-bee...

[wi11iamr_]

Thanks @marcoG , I'm following your post hoping something comes of it.

In the meantime, taking from the responses in your post I'm looking into my OnPrem Gateway's connectivity and trying to run the Diagnostics Network Ports Test. When I try the "Start new test" it briefly flashes the message "Current test status: Retrieving servers list" but then disappears back to just "Start new test". The Ports Test then does nothing.

Checking my firewall I see my OnPrem Gateway is contacting wabi-south-east-asia-comp-ev2.southeastasia.cloudapp.azure.com for this "Servers List". There are no errors or blocked requests on my firewall so I don't know that I can change anything (we do not have a proxy, thus no proxy config issues).

We're trying to interrogate our firewall in more detail to see if there is any blocking on the inbound response from Azure for this test, but I'm not so sure we'll uncover anything.

That said, given my Pipeline issue is that the CopyData activity just sits in QUEUED status without any errors, it does make a little bit of sense to me that perhaps the issue is a connectivity/networking related issue. I'm going to pursue my Gateway's Network Ports Test problem until I can get it to run, and then hopefully it either shows me some tests that fail, or my Pipeline's CopyData form an OnPrem Gateway then starts to work 🙂

[wi11iamr_]

Closing out the sympton of my OnPrem Gateway's Diagnostics Network Ports Test not working - after checking firewall logs, tracert results and restarting services, the final resolution to restart the entire Gateway server resolved this issue and my Network Ports Test could then run, and pass all tests successfully... Needless to say, with the Network Ports Test now working, this did not resolve anything with my CopyData pipeline staying stuck in the QUEUED status. This QUEUED issue continues.

[marcoG]

It still doesn't work for me...It's really frustrating not to have any support

[marcoG]

I have installed the latest version(3000.218.7) of the gateway but the pipeline always queues indefinitely

[itchyeyeballs]

Are you writing to a lakehouse and setting partitions? Ours was causing an issue but didnt error, just sat on queued.

[marcoG]

No, I am copying to ADLS Gen2 parquet file but the copy remains in the queue indefinitely