SSIS: How to import null string as null mark in flat files

Scenario:

I have some requirements: 1. Save the SQL result as CSV file 2. Import the data from the CSV file into another SQL database. There was not a way to copy the data from one sql to another sql. So I was saving the SQL result as CSV file it was saving NULL values as NULL string the CSV file. As a result of this when I was importing the CSV file into SQL table it had NULL string rather than NULL value.

SQL:

CSV:

Solution:

There is no way you can enter null values in excel. So best bet is:

1. Save the SQL result as CSV file
2. Open the CSV file in notepad++ and replace all  the NULL with empty string (not a space)
3. Replace a column delimeter to use comma rather than tab if you need
4. Save the file as xlsx. If you import excel file to sql table then it does put blank as NULL, it you import csv, it does put blank as blank string
5. SSIS data flow task: Destination editor–>Check option – keep nulls
6. Now it will the keep the null as null

Alternative:

1. Load the flat file with NULL string in the staging table
2. Add a SQL task to update the staging table with NULL where NULL string

 

SQL String: Replace NULL string literal with null value

Scenario:

I have a table with a string literal value ‘NULL’ or ‘null’ and I want to repalce them with the null value.

Solution:

NULLIF Returns a null value if the two specified expressions are equal.

NULLIF ( expression , expression )

You can use NULLIF:

/*here it will compare mark column value with string 'null' if they are equal it will return null*/
SELECT NULLIF(Mark,'null') 
FROM tblname;

SSIS String: Replace empty string with null for integer column

Scenario:

My source SQL table column was ‘nvarchar(50)’. My destination SQL table column was ‘money‘ data type. Some of my source column had a blank space value. So I had to convert them first to NULL(DT_WSTR, 10) if it is empty string then convert them to currency.

Expression: (DT_CY) (TRIM(Gift_amt)==”” ?  NULL(DT_WSTR,10)  :   [Gift_amt])

 

Note:

(DT_I4)( TRIM( «character_expression»  ) == “” ? NULL(DT_WSTR, 50)  :  «character_expression»  )

(DT_I4)( TRIM( Campaign) == “” ? NULL(DT_WSTR, 50) :  Campaign)