ADF Data Flow Empty String Handling

2022-11-10T22:12:21.25+00:00

i have problems in my Data Flow with EmptyString Handling. I made an easy example (see below) which examples my problem.

  1. Source: select '' as test (or any empty string of a table column)
  2. DerivedColumn. New Colum EmptyStringExpression with Expression iif(test == '', 'ok', 'nok') to show the problem.
  3. Sink write to table. Preview result is what i expect (see below), but in the sink table its different to the Preview, result there is 'nok'. It seems that the preview result is different to what the data flow is really writing to the sink table at the end. any help? Can't unterstand it...

emptyStringExpression

![259149-image.png

preview of the sink.

259241-image.png

result of the table with new insert row

259195-image.png

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,380 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,657 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Michael Lehmann - EGELI Informatik 1 Reputation point
    2022-11-11T10:40:08.757+00:00

    Yes of course. My original dataflow was ofcourse different, but with this example i can reproduce the problem that i had.

    259518-image.png

    259493-image.png


  2. SSingh-MSFT 16,371 Reputation points Moderator
    2022-11-14T05:50:03.76+00:00

    Hi @Michael Lehmann - EGELI Informatik ,

    Thanks for the reply with screenshots.

    I tried to reproduce as below using source as synapse and query:

    259916-sourceemptystring.gif

    Then added derived column, sink and sql db witn result givng as: "ok" for empty string:

    259976-drsinksqldbemptystring.gif

    At both Data preview of the SQL DB sink and in the DB column has "ok" stored for empty string.

    Could you please share more details about the settings/configuration in your pipeline which I might have missed in my reproduction.
    Additionally, please try to Publish changes and perform a trigger execution and check if you got same data in preview and DB.

    Thanks in advance for your response.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.