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:

n1

CSV:

n2

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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s