OLE DB Destination and unique identifiers

• Garth Michel

  SSC-Addicted

  Points: 445

  More actions

  February 9, 2009 at 4:40 am

  #127937

  Hi again

  So I'm now getting an error when I'm trying to insert my data into the DB:

  [OLE DB Destination [3432]] 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 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 'id', table 'Darwin.dbo.tblServiceCDR'; column does not allow nulls. INSERT fails.".

  Here's the table I'm trying to insert into:

  CREATE TABLE [dbo].[tblServiceCDR](

  [id] [uniqueidentifier] NOT NULL,

  [serviceID] [varchar](255) NOT NULL,

  [ddi] [nvarchar](255) NOT NULL,

  [alias] [nvarchar](255) NULL,

  [answered] [nvarchar](255) NOT NULL,

  [released] [nvarchar](255) NOT NULL,

  [callerID] [nvarchar](255) NULL,

  [postcode] [nvarchar](255) NULL,

  [region] [nvarchar](255) NULL,

  [holdTime] [nvarchar](255) NULL,

  [keypad] [nvarchar](255) NULL,

  CONSTRAINT [PK_tblServiceCDR] PRIMARY KEY CLUSTERED

  (

  In the OLE DB Destination command, I've set the Data Access Mode to "Table or view" and in the mappings column, I've got ignore for the id field. So I'm not sure why its giving me grief about the id field.

  Any help?

  Thanks

  G

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  February 9, 2009 at 5:44 am

  #940407

  You have to create the table with a default value of NEWID(), i.e.

  CREATE TABLE [dbo].[tblServiceCDR](

  [id] [uniqueidentifier] NOT NULL DEFAULT NEWID(),

  --Ramesh
  

  ---

• behera.prasanta

  Mr or Mrs. 500

  Points: 521

  More actions

  September 28, 2011 at 8:01 am

  #1387788

  I am getting the following error in my data flow task:

  [OLE DB Destination [3432]] 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 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 'id', table 'Darwin.dbo.tblServiceCDR'; column does not allow nulls. INSERT fails.".

  Just to let you know that in SSIS destination mapping, no source column is mapped into this destion column "id" and this destination column is not null.

• kramaswamy

  SSCoach

  Points: 18181

  More actions

  September 28, 2011 at 9:07 am

  #1387839

  You should start a new thread instead of using an existing one.

  Is your ID column in your database a field with a default value, either explicitly defined or defined by using an identity seed?

  If not, then you will get an error if you have no source column mapped to the destination ID column, since when you don't map a column it automatically takes the default value of the column.