Fabric Data Pipeline - Copy activity stays in queued status

[vidhyan]

I have a data pipeline with a copy activity that copies data from a table in sql server into Fabric - Warehouse. The copy activity stays in queued status for a very long time and never gets picked up for processing. What could be the reason? How can I troubleshoot this issue? A similar pipeline that copies data into a lakehouse works fine. 

[nicworth]

Hi All,

I am having the exact same issue. I have installed the latest version of the OnPremise Gateway (3000.218.8) and no matter what I do the PL in fabric always shows a stat of 'Queued'. All I am trying to do is copy data from On-Premise SQL server to Azure Blob Storage Account. I have tested using MS Sample data to the BlobStorage account to rule out any issues connecting to the Storage Account, this worked fine. As soon as I change the source to my on-premise SQL Server I have this 'Queued' issue, I can preview the data using the same gateway connection fine, it is just an issue as soon as I try and use the on-premise SQL server as a source. 

I have checked the network ports test and all tests are successful, I have disabled FW just incase that was causing an issue and the problem still persists. 

We have check the gateway logs I am not seeing and errors in the ErrorLogs generate that is pointing to an issue. In the IntegrationRuntimeInfo log that is generated I can see a lot of verbose errors stating that Assemblies could not be found, see below for assemblies it is complaining about -

Can't find assembly for System.IO.Compression, Version=4.1.2.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

Can't find assembly for Microsoft.DataTransfer.Common.MetadataManagement, Version=1.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35

Can't find assembly for Microsoft.DataTransfer.SharedSecurityLibrary, Version=1.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35

Can't find assembly for sapnco, Version=3.0.0.42, Culture=neutral, PublicKeyToken=50436dca5c7f7d23

Can't find assembly for sapnco, Version=3.1.0.42, Culture=neutral, PublicKeyToken=50436dca5c7f7d23

Can't find assembly for Microsoft.DataTransfer.TransferTask.resources, Version=1.1.0.0, Culture=en-US, PublicKeyToken=31bf3856ad364e35

Could these errors be related to the issue I am experiening?

Thanks

Nic

[nicworth]

Hi All,

Just to followup on my issue, just in case anyone experiences the same. I have identified what was causing my problem and it was due to the Azure Blob Storage connection that I had created to use for staging the data between On-Premise SQL server and the Fabric Data Warehouse in the Pipeline. On the connection to Azure Blob Storage Account I needed to select 'Allow this connection to be used with On-Premises data gateways.'

Once this was enabled the Pipeline started to work fine and no longer remained as 'Queued'.

I am surprised that no error's or messages were displayed to indicate this as the problem as up until now I found the pipeline validation messages quite useful.

Hopefully this will help others in the future.

Thanks

Nic

[vidhyan]

@JFTxJ  In which screen do you see the check box for 'Allow this connection to be used with On-Premises data gateways.'

[nicworth]

Apologies for the late reply, it is in the screen where you setup the connections (Manage Connections and Gateways), Connection you created to the blob storage account used for staging the data.

[JFTxJ]

Hi @vidhyan ,

I was facing the same issue as you.  In my case (hope it helps you), after working with MS support, a new version of the on-prem data gateway was released (on April 18th 2024 - 3000.218.8).  As soon as I installed this new version, the pipelines started working.

[vidhyan]

Thanks @JFTxJ . Does the data factory that is part of fabric instance use a gateway? If so, where can I find the gateway? In my case I am copying data from a SQL Server in an Azure VM to Warehouse residing in fabric. Data pipeline I created is also part of the fabric instance.

[JFTxJ]

My appologies.  I read your post too quickly and assumed that you were having the same issue as me.  In my case, this issue was only happenning when I was copying data from my on-prem SQL server (in a VPC on AWS).  Very sorry to have mis-lead you.

[v-cboorla-msft]

Hi @vidhyan 

Thanks @JFTxJ  for sharing the details.

For the latest version of on-prem gateway please refer : On-premises data gateway

I hope this helps.

Thank you.

[vidhyan]

Hello @v-cboorla-msft  . Adding screenshots below. I am just testing with a narrow table that has a few records. What is puzzling is that when I perform the same copy data activity that copies into a lakehouse works. The issue is with Warehouse.
Source : SQL Server installed in an Azure VM, connectivity to this from Fabric exists.
Destination: Fabric Warehouse
Data Transformations : None

[v-cboorla-msft]

Hi @vidhyan 

In the Settings menu, under Data Store Type, could you please change the selection to "Workspace" instead of "External". After doing the changes we recommend re-running the pipeline, this might help you in resolving your issue. 

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

Note : If you are copying the data using cloud connection for loading the data to Warehouse, Microsoft Fabric support onelake as staging.

Hope this helps.

Thank you.

[vidhyan]

I get validation error if I switch to 'workspace'.

[v-cboorla-msft]

Hi @vidhyan 

Apologies for the issue that you facing here.

Could you please share the details of your connection settings that you are uisng. This will help me to guide you in better way.

Thank you.

[vidhyan]

@v-cboorla-msft The warehouse and pipeline are part of the workspace. The copy activity does not ask for any connection string, see screenshots below. I execute the pipeline by clicking the Run button on the pipeline screen. I have logged into https://app.fabric.microsoft.com/ as me using my company account.

[v-cboorla-msft]

Hi @vidhyan 

Thank you for sharing the details. But could you please try to send the below details.

Thank you.

[vidhyan]

@v-cboorla-msft  Are you saying that I should use 'SQL Server' as my destination instead of 'Warehouse'? Even if I try to do that, I don't have a login I can use. 

[v-cboorla-msft]

Hi @vidhyan 

No, I am asking you to provide the external source connection details. Please find the screenshots for reference.

This will help me in understand the ask and guide you in better way.

Thank you.

[vidhyan]

@v-cboorla-msft  Here is the connection for the Source SQL Server.

Also see, the data copied from Source to staging area in the storage account.. This tells me it is able to connect to the source and copy the data to staging. It fails when it tries to connect to destination 'Warehouse'.

[v-cboorla-msft]

Hi @vidhyan 

Try connecting to the souce by selecting Data gateway as "none" and run the pipeline.

If the issue still persists.

Please reach out to our support team to gain deeper insights and explore potential solutions. It's highly recommended that you reach out to our support team. Their expertise will be invaluable in suggesting the most appropriate approach.

Please go ahead and raise a support ticket to reach our support team:

https://support.fabric.microsoft.com/support

After creating a Support ticket please provide the ticket number as it would help us to track for more information.

Thank you.

[vidhyan]

@v-cboorla-msft Thanks for your help. 

[v-cboorla-msft]

Hi @vidhyan 

Thank you sharing the details.

I have successfully replicated the scenario and confirmed the pipeline functioned as expected. Please find the attached screenshots.

Source:

Destination:

Mapping:

Settings:

Copy data details:

Could you please recheck the pipeline configuration, data source connections. Also under settings tab try to select workspace instead of external for Data store type and try to execute the pipeline.

If the issue still persists, please do let us know. Glad to help.

Note : If you are copying the data using cloud connection for loading the data to Warehouse, Microsoft Fabric support onelake as staging.

I hope this information helps.

Thank you.