SSIS – Insert fails on column with a default value

Standard

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:

DefaultGetdate

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 www.sqlservercentral.com with a bit more information.bit more information.

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