locked
SSIS Data Flow task ? RRS feed

  • Question

  • Hello:

    I am inserting data in my SSIS Data Flow Task from Source to destination.

    In my destination table, i have Identity column & that column is also member of Primary Key.

    When i run my data task i get following error as shown below. i am trying to see if i can avoid\remove that ID Column in destination to carry on my INSERT.

    I right click on that OLE DB Destination & selected Show Advance Editior - Input & Output Properties -- Ole DB Destination Input -- External Column and from there i can remove that ID Column, but that column again pops up & i get the same error message as shown below.

    any help or pointer is greatly appreciated.

    Thanks

    Error:-

    [Final Insert Log - 2 [283]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E2F.
    An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80040E2F  Description: "The statement has been terminated.".
    An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80040E2F  Description Cannot insert the value NULL into column table  column does not allow nulls INSERT fails

    Sunday, November 4, 2012 12:20 AM

Answers

  • An identity column by default will increment according to your settings unless it is disabled. When it is enabled you should not provide any value otherwise you get and error. IN your case most likely the error is being caused by another column.
    • Marked as answer by Eileen Zhao Monday, November 19, 2012 2:53 AM
    Monday, November 5, 2012 11:11 PM

All replies

  • You need to check the "Keep Identity" check box on the properties page of your OLEDB Destination.
    Sunday, November 4, 2012 12:30 AM
  • Hello:

    Thanks for reply, i have keep identity check, still i am getting same error.

    Thanks for your help

    Thanks

    Sunday, November 4, 2012 12:44 AM
  • Put a data viewer between source and destination and see what values are coming in for the Identity column.

    Also validate what Identity value has been on in the destination column.


    Vikash Kumar Singh || www.singhvikash.in

    Sunday, November 4, 2012 1:25 AM
  • Well then, you are trying to insert a NULL value into a column (other than the identity column) that doesn't allow NULL values.  Try creating an identical temporary table where all the columns allow NULL and loading your data there, then query the table to see where the NULL's are.
    Sunday, November 4, 2012 1:25 AM
  • Hello:

    Thanks for your replies.

    My SOURCE columns are 14 & i have 15 columns in my destination.

    The ID column (Identity) only exist in destination & not in source.

    So coming back to original question i want to avoid\ignore that ID column in my OLE DB Destination task.

    i think i might have to use TSQL\Script TASK if i am not able to resolve. if i use this task then i cannot use Balance Data Distributor that is already there.

    Again, any help is greatly appreciated.

    Thanks

    Sunday, November 4, 2012 1:33 AM
  • Why can't you just set that column to <ignore> on the mapping page?

    If that's not working, you are trying to insert NULL into a column that is defined as NOT NULL. Can you post the DDL for your destination table?


    Sunday, November 4, 2012 1:58 AM
  • i tried all but didn't helped. at the end i dropped that column, which was workaround for me.

    even ignoring that in task for that column didn't helped.

    Thanks

    Monday, November 5, 2012 8:38 PM
  • An identity column by default will increment according to your settings unless it is disabled. When it is enabled you should not provide any value otherwise you get and error. IN your case most likely the error is being caused by another column.
    • Marked as answer by Eileen Zhao Monday, November 19, 2012 2:53 AM
    Monday, November 5, 2012 11:11 PM
  • We should also add that it depends on what you're using for a Destination connection manager. Is it OLEDB or is it ADO.NET??  If you use OLE DB, then you'll get a little check box 'Keep Identity' which you can check.  If you use ADO.NET you'll have to control the IDENTITY_INSERT of the destination table manually (via a SQL Task in SSIS) with the T-SQL statement ... SET IDENTITY_INSERT <<table name>>  [ ON ; OFF ]

    HTH

    Thursday, July 25, 2013 8:53 PM