Gen2 Dataflow to Warehouse - really slow performance

I'm experiencing the same performance issues with Dataflow Gen 2 using an On-prem data gateway:

• Pulling the data from the underlying SQL database is quick. I can see that in the refresh details (about 10 tables each taking less than 1 min to pull the data).

• However, the second Dataflow Gen 2 step where it's writing the data (WriteToDataDestination) to the warehouse takes ages and eventually times out. It succeeds in writing about 3-4 tables but then fails.

"104100 Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Microsoft SQL: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"

Is there a way to see what jobs/requests are running on the warehouse? It seems that running too many requests (e.g., 1 Dataflow Gen 2 with many tables or running multiple Dataflows Gen 2 at the same time against the same Warehouse) results in locking up the Warehouse.
Is there even a way to stop all current jobs?

I'm inclined to look for other solutions, but notebooks and fast copy are still not supported for on-premise sources as of Jan 2024. Many new features will be coming for Dataflow Gen 2 in Q1 of 2024, though. As a next step, I will be trying Dataflow Gen 2 to store data into the lakehouse to get a properly robust ETL process set up and let you know if that resolved it.

What kind of on-prem datasource are thinking of? I haven't tried to access any onprem sql databases or other sources. I just used API calls to several endpoints. But maybe you could try using odbc/jdbc from spark if you want to connect to sql?

spark = SparkSession.builder \
.appName("YourAppName") \
.config("spark.jars", "/path/to/your/sqljdbc42.jar") \ # Path to your SQL Server JDBC driver
.getOrCreate()

df = spark.read.jdbc(url=jdbc_url, table="your_table", properties=connection_properties)

Or:

from pyspark import SparkContext, SparkConf, SQLContext

appName = "PySpark SQL Server Example - via JDBC"
master = "local"
conf = SparkConf() \
.setAppName(appName) \
.setMaster(master) \
.set("spark.driver.extraClassPath", "sqljdbc_7.2/enu/mssql-jdbc-7.2.1.jre8.jar")

sc = SparkContext(conf=conf)
sqlContext = SQLContext(sc)
spark = sqlContext.sparkSession

database = "test"
table = "dbo.Employees"
user = "username"
password = "password"

jdbcDF = spark.read.format("jdbc") \
.option("url", f"jdbc:sqlserver://localhost:1433;databaseName={database}") \
.option("dbtable", table) \
.option("user", user) \
.option("password", password) \
.option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver") \
.load()

jdbcDF.show()

Or:

from pyspark import SparkContext, SparkConf, SQLContext
import pyodbc
import pandas as pd

appName = "PySpark SQL Server Example - via ODBC"
master = "local"
conf = SparkConf() \
.setAppName(appName) \
.setMaster(master)

sc = SparkContext(conf=conf)
sqlContext = SQLContext(sc)
spark = sqlContext.sparkSession

database = "test"
table = "dbo.Employees"
user = "username"
password = "password"

conn = pyodbc.connect(f'DRIVER={{ODBC Driver 13 for SQL Server}};SERVER=localhost,1433;DATABASE={database};UID={user};PWD={password}')
query = f"SELECT * FROM {table}"
pdf = pd.read_sql(query, conn)
sparkDF = spark.createDataFrame(pdf)
sparkDF.show()

Maybe you need to install the pyodbc: !pip install pyodbc

Please let me know if it works.