Synapse pipline dataflow truncate and insert

Question

I have a pipline in Synapse that only excecutes a dataflow. The dataflow has a Sink that is supposed to truncate the table and the insert new data. I have tried both to use Pre SQL script, and to use the table action. If the table is empty before I execute the pipeline the table becomes populated, but if the table is allready populated when I execute the pipeline the table only becomes truncated and no data is written. Has anyone had the same problem and solved it? If so, pleas reach out :)

Answer 1

Personally I would have a control pipeline that ran the truncate table statement in a stored proc with a parameter passthrough and internal user that has the permissions on the db to alter, then a wait to ensure it is clean and ready to move on to the data flow load task.

Answer 2

Hi @IJH ,

Thankyou for using Microsoft Q&A platform and posting your query.

As I understand your query , it seems like you are trying to truncate the destination table before the data load using Data Flow. In the sink transformation , you have tried to use both the approaches- using table action as 'Truncate table' as well as using Pre SQL scripts to write SQL query to truncate table before load. However, it is not working as expected. Please correct me if my understanding is wrong.

Please check the table schema and columns in the destination is same as the source columns .

Sharing the video for reference where I tried to reproduce your issue . For that, I used both options to truncate the table and reload the data. I didn't find any issue . It worked as expected for me.

Hope this will help. Please let us know if any further queries.

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

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators