ELT data from one to another SQL Servers

[KartikN]

My use case is to ELT data from Server 1 to Server 2. Both are on-prem SQL Servers.

Server 1: Operation database

Server 2: DWH

Step1: Extract delta data from Server1 based on last DWH load date and store it in SRCDF (not an issue)

Step2: Extract All data from Server2 and store it in TRGDF (not an issue)

Step3: Prepare UPDATEDF and INSERTDF after necessary transformation (not an issue)

Step4: Update UPDATEDF & insert INSERTDF in Server2 using Notebook (Issue). 

If any of you have come across similar use case then please share your solution (or) advise any other option to consider. Thanks.

[FabianSchut · Accepted solution]

Hi, copying from and to an on-premise sql server with a Notebook remains indeed difficult in Fabric. I've seen this video in other blog posts as a suggestion to get data from an on-premise sql server to Fabric: https://www.youtube.com/watch?v=nAo_J6UFB9Y. Although this setup is still on my wishlist, I did not test this myself. In your case, it is the other way around too. You do not want to get the data from the on-premise sql server, but you want to write to it. Not sure if this same setup could also help, but it may point you in the right direction.

If this does not work, my suggestion is to use data pipelines or data flow gen 2 that use a connection with the on-premises data gateway (preferrably on a stand-alone VM) that has a connection to the sink on-premise sql server.

[prasbharat]

@KartikN - before i jump on to the solution, wish to ask few questions as below:

1. What specific issue are you encountering in Step 4? (e.g., timeout, data mismatch, partial updates, or performance lag?) Can you share any error messages or logs?

2. Are you trying to update and insert the data in a single operation, or are you performing them separately?

3. Does the UPDATEDF and INSERTDF data match the schema and constraints of the target table in Server 2? For example, are there any primary keys, unique constraints, or data type mismatches?]

4. What is the size of UPDATEDF and INSERTDF? Is it possible that the volume is causing performance issues during the write operation?

5. Is the target table in Server 2 being accessed or modified by other processes during Step 4?

Regards,

Prasana

[AndyDDC]

Hi @KartikN can I ask why you need/want Fabric to do this?  It means moving data from on-prem to cloud and then back again.  Is there no way of running any ETL processes purely on-prem between the 2 servers?

[KartikN]

Hi @AndyDDC , this is a transient use case where eventually the on-prem target server will be replaced by Fabric Warehouse. Hence using Fabric for the ETL process.

[FabianSchut · Accepted solution]

Hi, copying from and to an on-premise sql server with a Notebook remains indeed difficult in Fabric. I've seen this video in other blog posts as a suggestion to get data from an on-premise sql server to Fabric: https://www.youtube.com/watch?v=nAo_J6UFB9Y. Although this setup is still on my wishlist, I did not test this myself. In your case, it is the other way around too. You do not want to get the data from the on-premise sql server, but you want to write to it. Not sure if this same setup could also help, but it may point you in the right direction.

If this does not work, my suggestion is to use data pipelines or data flow gen 2 that use a connection with the on-premises data gateway (preferrably on a stand-alone VM) that has a connection to the sink on-premise sql server.

[KartikN · Accepted solution]

Hi @FabianSchut, Please refer to (12) Connecting to SQL Database in Fabric: A Step-by-Step Guide | LinkedIn. Like ODBC, we can execute INSERT/DELETE/UPDAT/MERGET SQL statements in JDBC as well by creating cursors. This needed jaydebeapi extension.