Cannot insert the value NULL into column with IMPORT/EXPORT

Question

Post reply

Cannot insert the value NULL into column with IMPORT/EXPORT

tripower

SSCrazy

Points: 2035
More actions
December 2, 2009 at 11:09 am

#143277

I setup a simple import/export task to pull one table into another. The data destination has a simple auto increment key field setup along with three data fields from the the source table. "Enable identity insert" is checked and yet I get the following error: 'Cannot insert the value NULL into column'
Why and how do I get this going?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

sabyasm SSCarpal Tunnel Points: 4324 More actions · Answer 1

[font="Comic Sans MS"]

1) For my better understanding are you using the OLE DB/SQLServer destination with "Keep identity" checked?

2) This might sound very obvious - but did you check if any of the other non-identity column is set to be NOT NULL and the code is trying to insert NULL value from source?

[/font]

[font="Comic Sans MS"]--
Sabya[/font]

Phil Parkin SSC Guru Points: 246961 More actions · Answer 2

I suspect that the error message is telling you the truth - setting IDENTITY INSERT does not mean that you can put NULLs into a PK field.

You need to double check your source data.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

evelyn.bidenkova SSC Veteran Points: 266 More actions · Answer 3

HI - I seem to have similar problem - when importing data from FLAT txt file to table in SQL Server via .dtsx file empty records in txt are not populated to SSMS table as NULL but simply as emply cells without any value.

How can I set SSIS package to import such cells as NULL rather then empty cell?

Thanks for any help!

Ev.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

Isn't an empty cell without a value equal to NULL?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

evelyn.bidenkova SSC Veteran Points: 266 More actions · Answer 5

nope.

It is usefull having NULL values in the columns rather then empty cells in cases where for example coalesce or function isNull ([attribute],[attribute]) is used.

empty cell just simply doesnt create desired results.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

How do you define empty cells? As an empty string: "" ?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

evelyn.bidenkova SSC Veteran Points: 266 More actions · Answer 7

evelyn.bidenkova

SSC Veteran

Points: 266

July 16, 2010 at 3:53 am

#1193973

yes

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

Then add a derived column in your data flow that converts the emptry strings to null values.

You need an expression that resembles this:

[myColumn] == "" ? NULL(DT_WSTR, 10) : [myColumn]

This expression is for a nvarchar(10) column.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

PaVeRa22 SSCommitted Points: 1965 More actions · Answer 9

evelyn.bidenkova (7/15/2010)
HI - I seem to have similar problem - when importing data from FLAT txt file to table in SQL Server via .dtsx file empty records in txt are not populated to SSMS table as NULL but simply as emply cells without any value.
How can I set SSIS package to import such cells as NULL rather then empty cell?
Thanks for any help!
Ev.

inside DFT, select check box "retain null values from the sources as null values in the data flow" for flat file edit propreties

also, pls see attached image for source and output result too, i hope thats what you are looking for....

evelyn.bidenkova SSC Veteran Points: 266 More actions · Answer 10

To: PaVeRa22

PaVeRa22 you are star! It worked - how could I miss that small check box? 😉

Many thanks!

Eve

:w00t:

PaVeRa22 SSCommitted Points: 1965 More actions · Answer 11

evelyn.bidenkova (7/19/2010)
To: PaVeRa22
PaVeRa22 you are star! It worked - how could I miss that small check box? 😉
Many thanks!
Eve
:w00t:

Sounds good, glad to hear that issue was resolved with that flag.

Cheers

bhavesh55 Grasshopper Points: 20 More actions · Answer 12

Can you please inform what do I do if the destination table field is Amount ?
[myColumn] == "" ? NULL(DT_WSTR, 10) : [myColumn]
can you please provide above expression for money or numeric field ?

Phil Parkin SSC Guru Points: 246961 More actions · Answer 13

bhavesh55 - Friday, April 28, 2017 1:13 PM
Can you please inform what do I do if the destination table field is Amount ?
[myColumn] == "" ? NULL(DT_WSTR, 10) : [myColumn]
can you please provide above expression for money or numeric field ?

Hello and welcome to the forum.
Please note that the thread you have responded to is 7 years old. I therefore recommend that you create a brand new topic instead & describe your question there in detail.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

bhavesh55 Grasshopper Points: 20 More actions · Answer 14

Hi Phil,
I posted new question but didn't receive any response. My question is
SSIS Derived column expression:
https://ask.sqlservercentral.com/questions/142807/ssis-derived-column-expression-1.html#answer-142820

I would appreciate your time and help in advance.