dynamic expression in pre SQL script on Azure SQL sink results in error running pipeline

Question

Hi, I'm running into an issue with a pre SQL script on a Azure SQL Sink in a Data Flow. What I'm attempting to do is the following:

• read a CSV file containing records that have a start and end date
• delete records from the sink Database that have the same start and end date

So in order to do achieve this I created a data flow as shown in the following screenshot:

The top flow reads a CSV source file, does a check, renames columns to match Database columns, cast columns to match database column types, filter certain records and finally add a derived column that concatenates the start and end date values formatted as 'MM/dd/yyyy-MM/dd/yyyy'.

The second flow will read the same CSV source file and determines the start/end date combinations in the source file and stores these (unique) values in a cache (column name 'billingPeriodStartAndEndDate').

In the DbCostDataOut sink I have pre SQL Dynamic expression that performs a delete on the sink table:

concat('delete from [dbo].[CspCostData] where [', 'BillingPeriodStartAndEndDates', '] in (', replace(replace(replace(toString(map(CachedBillingPeriod#outputs(), #item.billingPeriodStartAndEndDate)), '[', ''), ']', ''), '"', "'"), ')')

The query I'm trying to get is:

delete from [dbo].[CspCostData] where [BillingPeriodStartAndEndDate] in ('04/01/2023-04/30/2023', '05/01/2023-05/31/2023')

However when running the pipeline, the data flow fails with the following error message:

The '04/01/2023-04/30/2023' is the combination of start / end dates of the source data.

When I put the query as a string literal in pre SQL script field, no error occurs.

Is there any way where I can see the generated SQL query that is executed? Or does anyone know what is causing this error?

Answer 1

Hi Erik Post,

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

Thanks for sharing your workaround. This helps community as well.

You can also consider below approach too. Just adding for community reference.

have two source transformations one for your csv file and another for your sink data. And then consider using joins or lookup or exists transformations finally use alter row transformation to mark columns for delete and in Sink transformation under setting Allow delete and then delete records.

Hope this helps too.

---

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

Answer 2

Answering my own question here, but it turned out that there was an issue with the expression result and single and double quotes. By using reduce I was able to get a correct expression result that lead to a correct SQL query being executed without errors.