topic Re: Copy Activity - On-Prem to Warehouse - Staging in Pipelines

Copy Activity - On-Prem to Warehouse - Staging

Joshrodgers123 — Tue, 02 Apr 2024 22:56:48 GMT

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?

Re: Copy Activity - On-Prem to Warehouse - Staging

v-cboorla-msft — Wed, 03 Apr 2024 10:31:12 GMT

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.

Re: Copy Activity - On-Prem to Warehouse - Staging

Joshrodgers123 — Wed, 03 Apr 2024 12:57:50 GMT

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

Re: Copy Activity - On-Prem to Warehouse - Staging

v-cboorla-msft — Tue, 16 Apr 2024 10:42:49 GMT

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.

Re: Copy Activity - On-Prem to Warehouse - Staging

matkvaid — Fri, 19 Apr 2024 06:13:56 GMT

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:

Re: Copy Activity - On-Prem to Warehouse - Staging

v-cboorla-msft — Fri, 19 Apr 2024 13:30:23 GMT

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.

Re: Copy Activity - On-Prem to Warehouse - Staging

matkvaid — Fri, 19 Apr 2024 13:31:39 GMT

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

Re: Copy Activity - On-Prem to Warehouse - Staging

Joshrodgers123 — Fri, 19 Apr 2024 15:43:13 GMT

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

Re: Copy Activity - On-Prem to Warehouse - Staging

v-cboorla-msft — Fri, 19 Apr 2024 15:50:03 GMT

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.

Re: Copy Activity - On-Prem to Warehouse - Staging

Joshrodgers123 — Fri, 19 Apr 2024 15:51:34 GMT

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.

Re: Copy Activity - On-Prem to Warehouse - Staging

matkvaid — Fri, 19 Apr 2024 15:55:03 GMT

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

Re: Copy Activity - On-Prem to Warehouse - Staging

v-cboorla-msft — Fri, 19 Apr 2024 16:01:46 GMT

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.

Re: Copy Activity - On-Prem to Warehouse - Staging

dbrowne — Sat, 20 Apr 2024 18:30:39 GMT

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.

Re: Copy Activity - On-Prem to Warehouse - Staging

matkvaid — Mon, 22 Apr 2024 08:24:03 GMT

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...