My team is using an SSIS Package that runs in ADF SSIS IR(D4V3) through self hosted integration runtime in an Azure VM.
The SSIS package is ran through ADF pipeline(execute SSIS package) and connects to Azure SSAS to pull 5M rows of data using DAX query (about 3GB total).
On manual query, it takes about 60 seconds to pull 1M rows of data. However, the SSIS package takes 10 hours to pull the data. The average usage of memory of the SSAS was below the limit (50GB) which was around 35GB. Upon doing verbose logging to check, it shows multiple lines saying "Data Flow Task: Rows were provided to a data flow component as input." and another one saying the DFT engine will call a component method. I see 50k rows loading incrementally in the database periodically (every 10~20 minutes or so).
I am using ADO NET source and OLE destination to Azure Database(standard, DTU 200,S4 tier). Access Mode is SQL Command for source with DAX query and Access Mode for Table or view fast load for destination. CPU utilization is 20~40% and Memory Usage out of 16GB is about 4~12GB for the ADF SSIS IR.
My question here is:
- What could cause the slow issue here? Manual query is only 60 seconds or less for 1/5 of the data. No spilling to disk should occur since memory usage is under the limit. Also, sometimes there is a log Data Flow Task : Primeoutput will be called on a component. Primeoutput will be called on a component : ADO NET Source. and package is stuck. What could be the issue?
- What is the indicator that all the data have been pulled from the source? If the data is loading in the database, does that mean connection and pulling the data to SSIS package in-memory is finished?