SQL Server import wizard treat blank as null instead of zero 0

Possibly save 1 hour of your time: There are times when you need to import csv file with a nullable integer column. However, when you use the SQL Server database >> Task >> Import wizard, you will notice the result shows integer 0 instead of null values when the import csv file shows blank.

There is no feature on this wizard to treat blanks as nulls. The alternative is to use SQL Server Integration Service (SSIS).

But you can do it via an SSIS package to do ETL on the data from the CSV file into a SQL Server database.

You can use Data Flow Task. Inside the Data Flow Task, you can have a Flat File Source and an OLE DB Destination.

import csv to database.png

Click on OLD DB Destination, and there is option to Keep nulls.

OLE DB Destination - keep nulls

If you don’t know SSIS, you can always use the import data feature. In the last step, there is an option to Save SSIS Package and choose File System and the next screen will ask where you want to save. After you saved, then you can load that into a SQL Server Integration Services project.

save ssis package

Book:Professional Microsoft SQL Server 2012 Integration Services


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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s