SSIS – Insert fails on column with a default value


Adding a ‘Created On’ field is a great idea for determining when a particular record was created in your data warehouse. This can help with a number of things not least of which is trouble shooting.

I recently added a new CreatedOn field with a default of GETDATE(), however I purposefully didn’t hook this up in my ETL so that it would automatically be populated by SQL Server itself.

I quickly ran into a problem when the package used to populate this table started failing with the below error:

“An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Cannot insert the value NULL into column ‘CreatedOn’, table ‘Sales.fact.Package’; column does not allow nulls. INSERT fails.”.”

I checked my package and this field wasn’t hooked up, so what was going on? It should have been getting a NULL inserted and then SQL Server should have put in a value of GETDATE()?!!

The problem was with the Keep Nulls check box in the OLEDB Destination:


SSIS will override the default on the column upon insert. Because I don’t need to keep any of the nulls unchecking this check box solved the problem.

There’s a good article here on with a bit more information.bit more information.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s