How to import blanks as nulls instead of zeros while importing txt using wizard

I'm using the Import Wizard to load a text file and need blanks in integer fields to be nulls, but only zeros are inserted.

How to import it properly?

Unfortunately that's not possible if you insist on using the wizard, you would need to edit the package using Visual Studio.

There is an option 'keep null's' when editing the package in Visual Studio

Given this .csv file:

StringField,IntField
a,1
b,5
c,
d,6
e,
f,8
g,
h,
i,10
j,

and this table definition

CREATE TABLE [dbo].[NullTest](
    [StringField] [varchar](50) NULL,
    [IntField] [int] NULL
) ON [PRIMARY]

GO

When you import the data using the wizard the nulls are converted to 0's

However, if at the end of the wizard you select "save package as" instead of "run immediately" as in this screen

The file can be saved somewhere on the file system.

If you then create a new Integration Services project, you can add an existing package like this

If you browse to the file you just saved and view it you will see one data flow task (you will see an execute SQL task too if you selected to create the table instead of appending to an existing one)

Double click the data flow to edit it, and double click the data source to check the "retain nulls" option

Then double click the destination to view the properties, then change the "Keep nulls" property to true.

If you then click the green arrow in the toolbar or select debug > start debugging from the menu to execute your package the end result is this

In the end the wizard is only a wizard. It generates an SSIS package for you and chooses some defaults, unfortunately not the defaults you want.

If you want to keep nulls there isn't really any other way than editing the package with Visual Studio.

Then again, it might be easier to just create the package yourself in Visual Studio or use @MaxVernon's T-SQL solution.

StackExchange Database Administrators Q#123184, answer score: 8