Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332

    Unanswered: SSIS - Failure inserting into the read-only column "ColumnName"

    Hi folks, I have a little challenge for you, I hope

    The short story is as follows: I have multiple rather well normalized tables, and a view which connect them all togheter. In one of the tables I have an xml column, which I in the view to varchar(4000), since SSIS doesn't like xml all that much. To make the view updateable I have an instead of trigger placed on it, which works quite perfectly from T-SQL. SSIS however, claims that I cannot insert to a read-only column. Does anyone have the slightest idea how I can get around this?

    The only solution I can think of is having two columns for the XML data, one as varchar and the other as varchar, then having a trigger updating the xml column when the varchar is updated, but that would require recursive triggers, which I am not too happy with.

    Cheers
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    xml column, can't say I've ever come across that one before!
    Can you post the create statement for the table that has this column?
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    In SQL Server 2005 there is a new datatype for XML data called, well xml

    create table MyTable (
    Id int identity,
    MyXmlColumn xml
    );
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Haha, well I'll shut up then



    ..Actually no I wont.
    Useful article: http://davidhayden.com/blog/dave/arc...4/11/2909.aspx
    Oh and added an image too!

    HTH
    Attached Thumbnails Attached Thumbnails insertxml.jpg  
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh and this site might be useful too http://www.15seconds.com/issue/050803.htm

    Quote Originally Posted by From the above link
    Inserting and Selecting on an xml Column
    Once you have created your table, you insert an XML document into an xml column in the same way as you would for any other built-in data type. You can insert it as a string value, or use the CAST or CONVERT functions to specifically convert it to an xml type:
    Code:
    INSERT INTO MyTable(MyKey, MyXml) 
    VALUES (1, 'xml-document-string')
    Code:
    INSERT INTO MyTable(MyKey, MyXml) 
    VALUES (1, CAST('xml-document-string') AS xml))
    Code:
    INSERT INTO MyTable(MyKey, MyXml) 
    VALUES (1, CONVERT(xml, 'xml-document-string'))
    When extracting an XML document from an xml column you can convert it to a char, varchar, nchar or nvarchar type if you want to specify the character format:
    Code:
    SELECT CONVERT(nvarchar(500), MyXmlDoc, 'utf-8') 
    FROM MyTable WHERE ... etc. ...
    George
    Home | Blog

  6. #6
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    I know you read my original post. This is, as I said, exactly what I'm doing in my view, and it works perfectly from T-SQL, but SSIS does not let me insert data into the view (despite the fact that it works fine in T-SQL).
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    None of the 3 insert methods above work? I'm suprised, I thought the first or second would crack it - I assumed this was a syntax (compatability) thing, but I guess not! Hmm, interesting...
    George
    Home | Blog

  8. #8
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    SSIS sees the column (in the view) as varchar(4000) as intended, so type conversion is not the issue. The issue appears to be that the column in the view is made with a function (cast), and thus SSIS assumes the column not to be valid for inserts and updates, as it apparently is unaware of the instead of trigger that I have placed on the view. Most unfortunate, and pretty close to a bug in my opinion.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  9. #9
    Join Date
    Nov 2005
    Posts
    122
    A wild guess: Have you tried adding the WITH VIEW_METADATA attribute to the view?

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So the issue is not so much XML, but that SSIS thinks the column is not updateable even though there is an INSTEAD OF trigger on it.

    I'd say this is a glitch in SSIS, and you should contact Microsoft about it.

    May I ask what you mean by "SSIS doesn't like xml all that much"? You should be able to use the new XML related functions within SSIS.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jun 2008
    Posts
    1

    Thumbs up Answer

    ANSWER: I had similar issue and finally found the magic solution. In order for SSIS to detect the INSTEAD OF trigger, you have to set one of the the OLE DB DESTINATION properties, FastLoadOptions to "FIRE_TRIGGERS".

    This is not a drop down value, you have to type in this value!

    Although this thread is very old, thought no one should waste time figuring this out...




    Quote Originally Posted by blindman
    So the issue is not so much XML, but that SSIS thinks the column is not updateable even though there is an INSTEAD OF trigger on it.

    I'd say this is a glitch in SSIS, and you should contact Microsoft about it.

    May I ask what you mean by "SSIS doesn't like xml all that much"? You should be able to use the new XML related functions within SSIS.

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I don't think it's a glitch in SSIS. We are talking about 2 different methods of loading data. SSIS uses bulk load (or copy, or builk insert, which ever you'd like to call it), while in your T-SQL you're using INSERT. BULK INSERT bypasses the execution of the trigger, thus the target column is viewed as READ_ONLY. In your T-SQL script you're using INSERT, which fires the trigger, and that's why you're successful in T-SQL and keep failing in SSIS.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •