Created Table has a function assigning default values to NOT NULL columns.From SSIS,the default function is not getting invoked

Question

I am trying to run an insert statement against a tables which has few NOT Null columns in it.In case the values are not assigned to those columns then a default function defined assigns the values. While i am trying to run the Insert statement from SSIS the package is failing due to NOT Null Constraint. Please suggest.

If i assign values to all the columns it is running successfully but i want the default values to be assigned automatically rather than passing every time.

Below is the tables script: ALTER TABLE [Fee] ADD DEFAULT ([dbo].svf_GetUser) FOR [CreatedBy] GO

[OLE DB Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'CreatedBy', table 'SAI.dbo.Fee'; column does not allow nulls. INSERT fails.".

Answer 1

If you are passing null into insert statment then it will error out by giving can not insert null into Not null column but if your insert statement is like INSERT INTO TABLE (COL1, COL2,COL3) VALUES(1,2, NULL) then remove that column(COL3)from here it will take default value.

Answer 2

I realize this question is 18mo old at the time of my reply, but I just experienced this error in an SSIS workflow I was working on, where a field in my target table was defined as:

[OBSERVATION_GUID] [uniqueidentifier] NOT NULL DEFAULT newid()

The table had a clustered columnstore index, so in the SSIS package I had to use "Table or View - Fast Load". The error message you describe in your question was thrown. The answer is to uncheck "Keep Nulls". I have null values in other fields of my table, but turning this option off did not effect them (like turn them to empty strings or something). It appears "Keep Nulls" really just prevents the DEFAULT override of an inserted null value to trigger.