Azure Dataflow Issue

Question

Hi,

I am running a pipeline which is containing only a Dataflow. This dataflow is just reading the data from excel and inserting data to azure synapse sql db and deleting the existing records from DB. My source excel containing 135000+ records. When I run the pipeline for the first time, It executed fast and successful. And the data is inserted in DB.

When I rerun the pipeline, pipeline run is not completing. it keeps on running for more than 40 mins. No errors are throw, and the run is not completed. When I click on the debugging, it shows the activity is queued. It is in the same state for very long time.
It is queued in deletion step. Is it a problem in reading back from DB?

If I delete all records in DB and run pipeline, data is loading fine.

Why this issue is happening? wat could be the issue?

We are unable to find the cause.

Answer 1

Hi @Santhi Dhanuskodi ,

From your screenshots It seems you are using two sink transformations. Any specific reason for it?

You can use Pre SQL Scripts field under settings tab and write your delete script there to delete rows from DB before inserting.

But still if you want to use saperate sink transformation for deleting rows, then please make sure below points.

• Make sure you are using Alter row transformation before delete Sink and applying delete policy on rows which you want to delete.
• Under dataflow settings, make sure to use sink execute order correctly. First select delete sink and then insert sink.
  

Hope this helps. Please let me know how it goes.

---------------

Please consider hitting Accept Answer button. Accepted answers help community as well.