We use a Data Flow to synchronise data from a Synapse Link Data Lake with an Azure SQL database. A pipeline runs hourly which triggers the Data Flow to synchronise each table in turn.
A few weeks ago, we saw SQL errors when updating one particular sink table: the Flow was trying to insert a row with null in a non-nullable column.
This seems to have been caused by the entry of 2 double quotes in one of the source data fields. When I run the Data Flow in debug mode I can see the '""' value being passed through each transformation successfully, but the sink update fails as above. I assume that the '""' is triggering a premature EOL, causing subsequent data columns to overflow into a new row.
Anyone else seen this error?
I guess I could add a DerivedColumn transformation to inspect string columns and replace any double-quotes. But really I would expect the SQL sink to be able to cope with this scenario?
We use a Data Flow to synchronise data from a Synapse Link Data Lake with an Azure SQL database. A pipeline runs hourly which triggers the Data Flow to synchronise each table in turn. A few weeks ago, we saw SQL errors when updating one particular sink table: the Flow was trying to insert a row with null in a non-nullable column. This seems to have been caused by the entry of 2 double quotes in one of the source data fields. When I run the Data Flow in debug mode I can see the '""' value being passed through each transformation successfully, but the sink update fails as above. I assume that the '""' is triggering a premature EOL, causing subsequent data columns to overflow into a new row. Anyone else seen this error? I guess I could add a DerivedColumn transformation to inspect string columns and replace any double-quotes. But really I would expect the SQL sink to be able to cope with this scenario?