locked
XML Data Source Task SSIS RRS feed

  • Question

  • I am trying to use the XML source task in SSIS to import xml data into a  table. Below is the XML.   I have 3 options  


    1. XML file location -- Retrieve data from an XML file.
     
    2. XML file from variable -- Specify the XML file name in a variable.

    3. XML data from variable -- Retrieve XML data from a variable.

    Option 1 is working for me but I want do achieve the same using option 3. I declared a package variable and selected a datatype as string and tried set the value to xml data below , but it is throwing an error, any suggestions would be appreciated

    <FILEGROUP FileGroupName="NICE">
      <COLUMN ColumnName="LAST_NAME">
        <ColumnDelimiter />
        <ColumnType>FIXED WIDTH</ColumnType>
        <ColumnWidth>20</ColumnWidth>
        <ColumnDataType>DT_STR</ColumnDataType>
        <ColumStartPOS>23</ColumStartPOS>
        <ColumnEndPOS>42</ColumnEndPOS>
      </COLUMN>
      <COLUMN ColumnName="FIRST_NAME">
        <ColumnDelimiter />
        <ColumnType>FIXED WIDTH</ColumnType>
        <ColumnWidth>15</ColumnWidth>
        <ColumnDataType>DT_STR</ColumnDataType>
        <ColumStartPOS>44</ColumStartPOS>
        <ColumnEndPOS>58</ColumnEndPOS>
      </COLUMN>
      <COLUMN ColumnName="CITY">
        <ColumnDelimiter />
        <ColumnType>FIXED WIDTH</ColumnType>
        <ColumnWidth>20</ColumnWidth>
        <ColumnDataType>DT_STR</ColumnDataType>
        <ColumStartPOS>89</ColumStartPOS>
        <ColumnEndPOS>108</ColumnEndPOS>
      </COLUMN>
      <COLUMN ColumnName="STATE">
        <ColumnDelimiter />
        <ColumnType>FIXED WIDTH</ColumnType>
        <ColumnWidth>2</ColumnWidth>
        <ColumnDataType>DT_STR</ColumnDataType>
        <ColumStartPOS>109</ColumStartPOS>
        <ColumnEndPOS>110</ColumnEndPOS>
      </COLUMN>
      <COLUMN ColumnName="ZIP_CODE">
        <ColumnDelimiter />
        <ColumnType>FIXED WIDTH</ColumnType>
        <ColumnWidth>9</ColumnWidth>
        <ColumnDataType>DT_STR</ColumnDataType>
        <ColumStartPOS>111</ColumStartPOS>
        <ColumnEndPOS>119</ColumnEndPOS>
      </COLUMN>
     </FILEGROUP> 

    Thursday, July 15, 2010 10:25 PM

Answers

  • and did you got any error?

    did you set default value for your variable? the default value is needed because ssis will check it compile time. you can put sample xml data inside the variable for default value.


    http://www.rad.pasfu.com
    • Marked as answer by Raymond-Lee Sunday, July 25, 2010 12:46 PM
    Monday, July 19, 2010 6:12 PM

All replies

  • I declared a package variable and selected a datatype as string and tried set the value to xml data below , but it is throwing an error, any suggestions would be appreciated

     

    what error you received exactly?

    do you have problem in filling variable with XML data

    or in reading XML data from variable in XML Source?


    http://www.rad.pasfu.com
    Sunday, July 18, 2010 5:55 AM
  • I cannot read XML data from the variable
    Monday, July 19, 2010 4:09 PM
  • and did you got any error?

    did you set default value for your variable? the default value is needed because ssis will check it compile time. you can put sample xml data inside the variable for default value.


    http://www.rad.pasfu.com
    • Marked as answer by Raymond-Lee Sunday, July 25, 2010 12:46 PM
    Monday, July 19, 2010 6:12 PM
  • I got it , its working now

    Monday, July 19, 2010 9:33 PM