Sfrgttk

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?

I see you are attempting to use the Wizard to import data. You may be better off manually creating the table with the column definitions you desire, and using the T-SQL BULK INSERT command to import the data.

 USE tempdb;



 CREATE TABLE dbo.TestSource

 (

   SomeNumber INT NULL

 );

I created a plain-text sample file named C:temptest.txt which contains the following rows:

 1



 3

 4



 6

 7



 9

This statement imports the rows from the text file into the TestSource table:

 BULK INSERT dbo.TestSource

    FROM 'C:temptest.txt'

    WITH (

       DATAFILETYPE = 'char',

       FIELDTERMINATOR = ',',

       KEEPNULLS

    );

This shows the contents after the import is complete:

 SELECT *

 FROM dbo.TestSource;

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.

IF you have at least SSMS v 17.3 and are importing to a newly created table you can use the Import Flat File to SQL Wizard for this.

Running it against the CSV file in Tom's answer.

With the resulting table looking like