h1

SSIS Error Trying to insert Null Values

December 1, 2011

I was running into an issue today that was driving me a bit crazy.  I was trying to load a table that had about 35 columns, some set as NULL, others as NOT NULL.  On all of the NOT NULL columns there were default values set for them. So when I mapped all of the columns from the source to the destination, there were a few of the NOT NULL Columns that didn’t have a source, but since they had default values I wasn’t worried about them at all.  Now this was on a client machine, so I can’t show you exactly what happened, but here is an example I came up with.

It’s a simple name table from a flat file source.

image

I went and added the Sex column to the destination table, set it to not allow nulls and made the default value ‘F’.  In the source there is no column for Sex.  Now at this point I believed that I had left all other values at their default setting, and that was my mistake.  But before we get to that lets see what was happening to me and see if you can figure it out.

[OLEDB_DST Name List [55]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: “Microsoft SQL Server Native Client 10.0”  Hresult: 0x80004005  Description: “The statement has been terminated.”.
An OLE DB record is available.  Source: “Microsoft SQL Server Native Client 10.0”  Hresult: 0x80004005  Description: “Cannot insert the value NULL into column ‘Sex’, table ‘Demo.dbo.NameList’; column does not allow nulls. INSERT fails.”.
This made no sense to me at all.  I knew the column wasn’t allowing nulls, but I wasn’t mapping anything directly to that column and it had a default value set, so why were nulls still trying to ruin my day.  To the forums I went, and I found this topic http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/4554075c-5f68-4e59-9f7c-8d46c05e8ec6  and it a little reading but a reply by Hunchback hit the nail on the head.  Somehow, for reasons I can’t remember I checked the box next to keep nulls and that was causing all of my issues.

image

As soon as I unchecked that box, my problem was solved and the table loaded just fine.  I hope this helps somebody out there.  At least now I know I won’t take as long to correct that mistake the next time I make it.  I can’t promise my self I won’t do it again, because I am notorious, at least in my own mind, for accidentally checking boxes I didn’t mean to check.

Advertisements

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: