Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

OLE DB error... inserting nulls... 1

Status
Not open for further replies.

bmgmzp

Programmer
Aug 18, 2006
84
US
I'm creating an SSIS package in the BID and I keep getting this error:

[gray][tt][Destination 9 - STILabels [1060]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'ReportIDMultiple', table 'STI.dbo.STILabels'; column does not allow nulls. INSERT fails.". [/tt][/gray]

The problem is that the destination table has a field in it that the source table doesn't and this fields doesn't allow nulls. When I try to execute the SSIS package it gives me the above error because its trying to insert a null into the field. I'm not sure why its doing this or how to fix it.
 
The error is saying that a column in your destination does not allow nulls but the data in your data flow has nulls. you can fix this by running your data through a derived column task and checking for nulls and then assiging a default value if a null is encountered. The format is

Code:
ISNULL(ColumnName) ? "DefaultValue" : ColumnName

Set it to replace the column and you should be good.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck
 
I dont understand why my data flow has nulls. The column in my destination table doesnt exist in my source table. I would think that the data flow would just ignore the field in the destination table.
 
The data flow is ignoring the column that doesn't exist. THe error is a result of trying to insert data into the destination.

If the column doesn't exist in your data flow then what value gets inserted when you add a record? A Null if the column does not allow Nulls then obviously it would throw the error.

You can use a Derived column to add the column in question and place a default value in it.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck
 
Thank you Paul for your advice.

However the problem is that the column is an Identity column, so i don't want a default value. I just assumed that when a new record gets added that it would just keep incrementing itself.

Should I just create an identical identity field in the source table and then allow identity insert into the identity column in the destination table?
 
if the column is labeled as an identity column in the table definition then this is not the column producing the error. How many other columns are set to disallow nulls.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck
 
By Jove I think I've got it. You were half right MDXer, I had other columns that didnt allow nulls AND i had the keep identity box checked. once i unchecked that and addressed the other columns the problem went away.

THANKS AGAIN! STAR!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top