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.