Search code examples
ssisoledbssis-2012dataflowtaskflatfiledestination

How to retain null values in flat file destination in ssis package

I have created an ssis package. in dataflow task, i am passing data from oledbsource to flat file destination. i want to retain null values in flat file but it is coming blank.

Solution

  • As @Hadi has provided an example of how to handle NULLS being read from a flatfile. I understand you want to write NULLs to a flatfile. As has been explained, flatfiles do not have a concept of NULLs. Unlike SQL they do not have datatype. Everything is a string. A NULL in a flatfile is equal to an empty string.

    From your statement

    i want to retain null values in flat file but it is coming blank.

    I can only presume that you want to print the text "NULL" into the flatfile. For that, you can use a Derived Column component. Place it between your OLE DB Source and your Flat File Destination. Inside the Derived Column component detect the null values and cast them from a true NULL value into a string value "NULL" using the following ternary expression.

    ISNULL([MyColumn]) ? "NULL" : [MyColumn]

    Hope this helps. If you needed something else, please feel free to clarify your question.