Azure Data Flow Pre SQL Script - Dynamic Content

Question

hi,

I want to run a presql script in the Data flow in SINK. I want to delete exiting records for particular year.
This particular year will be coming from source excel file.

Say I have a file for 2021 data and loaded that data into DB. when I rerun the pipeline for the same excel file I want to delete 2021 related records in DB and insert fresh. This table may contain multiple years data. So everytime a new file arrives for a particular year, I want to delete that respective records and reload the new data.

I can read the year value from source file column. And I can keep it as derived column. How Can I write a presql script to delete?

delete from <TableName> where year = <sourcefile.year>

how can i do this in data flow?
Pls help!

Answer 1

Hi @Santhi Dhanuskodi ,

Thank you for posting query in Microsoft Q&A Platform.

You can achieve this using cached Sink in Azure Data flow. You need use derived transformation to get year from date value and then use cached sink to catch that year values. And then use cached output in pre-sql field of SQL Sink transformation.

Please check below example for better idea.

Please check below to understand more about cached sinks and cached lookups to achieve your requirement.
Cache Sink and Cached lookup in Mapping Data Flow in Azure Data Factory

Hope this helps. Please let me know if any further queries.

----------

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

Answer 2

Hi @Santhi Dhanuskodi ,

you can pass the Year as a Parameters to the Dataflow (read the metadata --> item name, extract the year --> pass the year to the Dataflow as a parameter) --> and then in the SINK, write the DELETE query in the Pre-SQL scripts using the add dynamic content (you will see the parameters there).

Hope this helps. Thanks!