Copy Activity - On-Prem to Warehouse - Staging

[Joshrodgers123]

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?

[dbrowne]

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.

[matkvaid]

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]

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

[v-cboorla-msft]

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.

[Joshrodgers123]

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.

[matkvaid]

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

[v-cboorla-msft]

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]

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.

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.

[Joshrodgers123]

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

[v-cboorla-msft]

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.

[matkvaid]

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

And this one is On premise SQL server:

[v-cboorla-msft]

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.

[matkvaid]

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