Field Defaults not used on DTS import

[whool]

I am importing a CSV file to a DB table using DTS.(SQL 2000) The destination table has defaults set for all NOT NULL fields.
Eg

Create Table TableName
(
field1 ... ,
field2 ... ,
CustomerExists bit NOT NULL DEFAULT 0
)

I find that when a NULL is encountered in the import file, rather than the default being used in its place, the import fails with a Constraint violation error.

Why does this happen? If I insert records one by one using INSERT INTO the default is used, no probs.


Yael

 

[jester777]

I've tried this on my SQL7 installation (haven't got 2000 installed). Found it works OK if I defined the DTS task as a bulk insert task. Look at the properties of the BULK INSERT TASK, one of the options is whether too KEEP NULL VALUES or not, by default the setting is not. This means defaults will be used instead of NULLS.

See below

Table Definition:
TABLE1
field1 varchar(20) NULL,
field2 varchar(20) NULL ,
customerexists int NOT NULL DEFAULT 0

Input File:
record,one,1
record,two,2
record,three,3
record,four,4
record,five,5
record,six,
record,,7
,,8
,,


Output from select after Bulk Insert DTS task.
field1 field2 customerexists
-------------------- -------------------- --------------
record one 1
record two 2
record three 3
record four 4
record five 5
record six 0
record NULL 7
NULL NULL 8
NULL NULL 0

(9 row(s) affected)

As you can see even though the input file had a couple of NULLS for the column customerexists these have been ignored and the default value inserted.

 

[whool]

No problems with Bulk Insert - works fine. But I cant do data transformations with Bulk Insert.

Thanks anyway.

Yael