Share via

In ADF copy activity NULL values in Source are converting to Empty string in Sink

Romana 15 Reputation points
2023-10-20T16:12:11.1866667+00:00

Hi team,

I am loading data from Snowflake DB to Azure SQL DB. Int and Date columns having NULL values in Source are being inserted as empty string in Sink by default resulting in pipeline to fail with error “the given value of type string cannot be converted to type date of the specified target column”.

Appreciate any input to resolve this. Thanks

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,711 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Amira Bedhiafi 16,071 Reputation points
    2023-10-21T20:22:42.2733333+00:00

    Can you try creating a derived columns where you can handle NULL values using expressions.

    iif(isNull(your_date_column), toDate('1900-01-01'), your_date_column)
iif(isNull(your_int_column), 0, your_int_column)
    2 people found this answer helpful.
    0 comments
  2. aby cyriac 5 Reputation points
    2023-11-20T22:00:52.3933333+00:00

    I had a similar sql query in a copy activity which was working just fine handling the nulls neatly. Now we have a pipeline in production which we will have to scrap and recreate just because the copy activity started to convert null to empty string out of nowhere.

    Thanks to engineers at @microsoft for ensuring our job security.

    1 person found this answer helpful.
  3. ShaikMaheer-MSFT 38,126 Reputation points Microsoft Employee
    2023-10-22T05:15:23.38+00:00

    Hi Romana,

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

    I hope here, you are trying to load data directly from Snowflake DB to Azure SQL DB without any staging storage. If yes, seems with Copy activity, when we take nulls from Snowflake DB intermittently, it will get treated as empty strings and finally ending up with above error.

    To avoid above issue, kindly consider using dataflows in Azure Data Factory. In dataflows, after source transformation consider using derived column transformation to check values for empty and pass some default values as 0 or 9999-01-01 something like that. OR
    Use conditional flow transformation to separate empty string rows, and then while sinking them to SQL DB, under sink transformation mappings tab remove mappings for that date and integer columns and have default value set as null for those columns in SQL DB table definition. That way, I hope it will automatically insert nulls into SQL DB table.

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

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