Forum Discussion
ELT data from one to another SQL Servers
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.
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.
Anonymous - 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