CODEWITHSUNDEEP
ssisoledbssis-2012dataflowtaskflatfiledestination
Asin
Asin

Reputation: 73

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.

Upvotes: 4

Views: 3025

Answers (3)

Troy Witthoeft
Troy Witthoeft

Reputation: 2676

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.

Upvotes: 2

NeemAce
NeemAce

Reputation: 11

Use derived column transformation in SSIS, Try with the following code. Replace [ColumnName] with your column.

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

Upvotes: 1

Hadi
Hadi

Reputation: 37368

In flat files there is not difference between null and blanks. so if flat file are readed using an application or another ssis package you have to check that value is not null or empty using functions like String.IsNullOrEmpty() and IsNull()

I.E.:

In a script component you can check the column value like the following:

If Not Row.inCol_IsNull AndAlso _
   Not String.IsNullOrEmpty(Row.InCol) Then

  'Do SomeThing
  Row.OutCol = Row.inCol

  Else

  Row.OutCol_IsNull = True

  En If

OR

if using an application

 If not strValue is nothing andAlso _
    not string.IsNullOrEmpty(strvalue) then

 ' do something
 end If

Upvotes: 1

Related Questions