Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Joshrodgers123
Advocate V
Advocate V

Copy Activity - On-Prem to Warehouse - Staging

Trying to copy data from on-prem SQL Server to a Warehouse. It says that staging is required, but only allows you to use ADLS or Blob storage - not OneLake.

 

Is this really the expected functionality? Do we need to bring additional Azure resources to get data from on-prem to a Warehouse?

 

Joshrodgers123_0-1712098494895.pngJoshrodgers123_1-1712098523145.png

Joshrodgers123_2-1712098571199.png

 

13 REPLIES 13
dbrowne
Microsoft Employee
Microsoft Employee

Supporting OneLake as staging for on-premise to DW is on the roadmap.  In the mean time, it's better to simply load to a Lakehouse in the same workspace as the Warehouse.  Those lakehouse tables will be accessable from the SQL endpoint, and you can load from there to the Warehouse if you want to.

 

I was waiting for on premise connection support for pipelines, because dataflows gen2 had many issues and with them resolved, they still use much more CU's than pipelines (at least copying from warehouse to lakehouse test promises). Now setting up lakehouse and then warehouse again would need adittional resources. Now i understand i should move to lakehouse and rebuild all reports, views, etc. Lakehouse tables does not support spaces in column names, o it is one of the things that is a nightmare to set up copy activities when renaming hundreds of columns...

Joshrodgers123
Advocate V
Advocate V

Hi @v-cboorla-msft, please see @matkvaid's response. He explained my question perfectly.

Hi @Joshrodgers123 

 

Glad that you query got resolved and @matkvaid thank you for sharing the same with the community as it can be helpful to others.
Please continue using Fabric Community for further queries.

 

Thank you.

 

My questions was not answered. @matkvaid reiterated what my question was. We are waiting on you, @v-cboorla-msft or the product team to provide an answer.

@v-cboorla-msft Responding like very bad version of chat bot 😭

Hi @Joshrodgers123@matkvaid 

 

Apologies for the misunderstandig.

We will reach out to the internal team to get some help on this .
We will update you once we hear back from them.
Appreciate your patience.

 

Thanks.

v-cboorla-msft
Community Support
Community Support

Hi @Joshrodgers123 

 

Thanks for using Microsoft Fabric Community.

Apologies for the inconvenience.

Data Warehouse COPY command directly supports Azure Blob Storage and Azure Data Lake Storage Gen2 as source data stores. If your source data meets the criteria described in this section, use COPY command to copy directly from the source data store to Data Warehouse.

vcboorlamsft_0-1712139389615.png

vcboorlamsft_1-1712140253197.png

For more details please refer : Direct copy by using COPY command.

                                               : Create a table.

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

 

I hope this information helps.

 

Thanks.

My question was referring to the copy activity in data pipelines (SQL server source/warehouse destination), not the copy command in the warehouse. 

Hi @Joshrodgers123 

 

Apologies for the delay in response from my end.

In order to understand your specific data copy scenario, Could you please share some more details about your copy data activity. This will help me to understand the query and guide in better way. If possible, could you provide information on the following:

 

Data Transformation Specifications: Are there any transformations being applied to the data during the copy process?

Screenshots: If feasible, sharing screenshots of the source and sink configuration within your data copy activity would be highly beneficial for visualization purposes.

 

Thanks.

It is very simple. Copy activity from On Premise Data Gateway requires external  Azure Blob or Azure Gen2 storage account for staging. Copy from Azure SQL allows to select staging in Workspace - the question is why?

This is Azure SQL source

azure.png

And this one is On premise SQL server:

OPDG.png

Hi @Joshrodgers123 

 

Following up to see whether your query got resolved or still facing the issue. In case if you have any resolution please do share that same with the community as it can be helpful to others.
If the issue still persists, please do let us know and we will try to help.

 

Thank you.

I have just clarified a question, that was understandable for everyone except You...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Oct Fabric Update Carousel

Fabric Monthly Update - October 2024

Check out the October 2024 Fabric update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.