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

[bmgmzp]

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.

 

[MDXer]

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

ISNULL(ColumnName) ? "DefaultValue" : ColumnName

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

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[bmgmzp]

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.

 

[FlaBusInt]

How do you derive the column that has NULLS?

 

[MDXer]

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

 

[bmgmzp]

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?

 

[MDXer]

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

 

[bmgmzp]

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!