none
having problem reading xml data from ssis

    Pertanyaan

  • I want to run the following stor proc from ssis, I am using data flow task OLE DB Command

    ALTER PROCEDURE [dbo].[sp_XMLResponse]
     @RecID varchar(25),
     @sXMLData nvarchar(max),
     @RunDt Date
    AS
    BEGIN
     
     declare @xXMLData xml
     declare @dRunDt  date
     
     SET NOCOUNT ON;
     
     set @xXMLData = CONVERT(xml,@sXMLData)
     set @dRunDt = CONVERT(date,@sRunDt,101)
    
        INSERT INTO dbo.XMLResponse(RecID, XMLdata, RunDt)
     VALUES (@RecID,@xXMLData,@dRunDt)
    END
    

     I wrote the following SQL Command

    EXEC sp_XMLResponse
    @RecID ?,MLData = ?,
    @RunDt ?

    Input Columns have the following three columns in OLE DB Command, Input and Output Ptoperties tab.
    RecID DT_STR (length = 25)
    XMLResponse DT_NTEXT
    RunDt DT_DBDATE

    In the External Columns I have added
    Param_0 DT_STR,
    Param_1 DT_NTEXT,
    Param_2 DT_DATE

    Initially the data type of  @sXMLData  was xml and I was using Insert command in the OLE DB Command object. but now I want to use stor proc and having problem passing XML column.

    I referenced the following post but that did not help either.

    http://social.msdn.microsoft.com/Forums/cs-CZ/sqlcs/thread/278f3e95-784b-4bff-b915-79d88c12b723

    Thanks.

    12 Maret 2012 20:25

Jawaban

  • In my stor proc parameters I changed the data type from xml to varchar(max) and then used converrt method to change from nvarchar(max) to xml. For some reason I was having problem configuring my ssis controls.


    13 Maret 2012 16:26

Semua Balasan

  • OK, I see you want to simply upload some XML into a table, then why not to use the XML source?

    You also do not tell what is the problem. I can see that you did

    declare @xXMLData xml

    Then use XMLResponse DT_NTEXT why? How do you expect that to map??


    Arthur My Blog

    12 Maret 2012 20:42
    Moderator
  • I am getting XML value from script component in data flow task. data type is dt_ntext

    string xmlresponse xmlresponse = xml string Row.CaseQryResponse.AddBlobData(System.Text.Encoding.UTF8.GetBytes(xmlresponse)); // = System.Text.Encoding.Unicode.GetString

    Getting Package Validation Error

    Operand type clash: int is incompatible wiht xml

    Unable to retrieve destination column descriptions from teh parameters of the SQL command

    12 Maret 2012 21:32
  • Not sure how to use xml source.

    I have another stor proc that will shred xml into tables.

    12 Maret 2012 21:37
  • I am getting XML value from script component in data flow task. data type is dt_ntext

    string xmlresponse xmlresponse = xml string Row.CaseQryResponse.AddBlobData(System.Text.Encoding.UTF8.GetBytes(xmlresponse)); // = System.Text.Encoding.Unicode.GetString

    Getting Package Validation Error

    Operand type clash: int is incompatible wiht xml

    Unable to retrieve destination column descriptions from teh parameters of the SQL command

    XML is just plain text, then you need to just use either the Unicode text all the way or the regular WT_STR.

    Furthermore, if you are already getting the XML, then why not to just use the OLEDB destination and simply map the Script Component to the destination, a string to XML should just work.


    Arthur My Blog

    13 Maret 2012 13:53
    Moderator
  • In my stor proc parameters I changed the data type from xml to varchar(max) and then used converrt method to change from nvarchar(max) to xml. For some reason I was having problem configuring my ssis controls.


    13 Maret 2012 16:26