none
SSIS - Failure inserting into the read-only column

    Question

  • HI,
    I have an XML source task going into an OLE DB destination task in SSIS.  I want to import the XML file into the SQL Table.  But I want the Primary Key within the XML to be inserted into the table.  I want the primary key set on the table.  However I get the following error:

    SSIS - Failure inserting into the read-only column

    This is very poor, because I don't want SSIS or SQL to take over the identity of the row.
    In addition, I've noticed on forums/blogs that you can check the 'Keep Identity' option, but this has made no difference whatsoever.
    Also, when I click edit on the OLE DB destination editor, I can't see the options for the insert that I had previously when I added the connection manager.

    Please can I get some assistance on this?
    Monday, February 8, 2010 5:20 PM

Answers

  • MArking 'Keep identity' option (when using fast load as data access mode) in the OLE DB destiantion component should work. What error do you get when doing so?


    http://rafael-salas.blogspot.com | Don’t forget to mark the post(s) that answered your question
    • Marked as answer by Jonswaino Tuesday, February 9, 2010 9:49 AM
    Monday, February 8, 2010 6:38 PM
    Moderator
  • If you check the Keep Identity option, make sure that you have a column mapped to the identity column in the destination.

    Normally when inserting into a table that has an identity column, you don't map an input column to the identity column in the OLE DB Destionation - you just leave it unhooked.
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    • Marked as answer by Jonswaino Tuesday, February 9, 2010 9:49 AM
    Monday, February 8, 2010 7:37 PM
    Moderator

All replies

  • Is the column an identity column?
    Hope this helps !!
    Sudeep   |    My Blog
    Monday, February 8, 2010 5:22 PM
  • yes it is.
    Monday, February 8, 2010 5:23 PM
  • MArking 'Keep identity' option (when using fast load as data access mode) in the OLE DB destiantion component should work. What error do you get when doing so?


    http://rafael-salas.blogspot.com | Don’t forget to mark the post(s) that answered your question
    • Marked as answer by Jonswaino Tuesday, February 9, 2010 9:49 AM
    Monday, February 8, 2010 6:38 PM
    Moderator
  • If you check the Keep Identity option, make sure that you have a column mapped to the identity column in the destination.

    Normally when inserting into a table that has an identity column, you don't map an input column to the identity column in the OLE DB Destionation - you just leave it unhooked.
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    • Marked as answer by Jonswaino Tuesday, February 9, 2010 9:49 AM
    Monday, February 8, 2010 7:37 PM
    Moderator
  • I have managed it now, thanks for your patience and enduring with my impatience and lack of knowledge;)
    What I found was that the Keep Indentity is not shown unless you pick the Table or view - 'Fast Load' option.  Its not clear why this makes a difference to the options available.  I'm sure there is a reason, I'll check the MSDN docs.






    Tuesday, February 9, 2010 9:18 AM