none
How to pull the values from XML file and laod into SQL table using SSIS? RRS feed

  • Question

  • Hi,
    I have a xml file with the following format -

    <?xml version="1.0" encoding="ISO-8859-1"?>
    <StoreGroupControl>
    <StoreGroup>
       <Store-Code>00001</Store-Code>
       <GroupName>XXX GROUP</GroupName>
       <OrderType>1</OrderType>
       <Country>USA</Country>
    </StoreGroup>
    <StoreGroup>
       <Store-Code>00002</Store-Code>
       <GroupName>YYYY GROUP</GroupName>
       <OrderType>2</OrderType>
       <Country>USA</Country>
    </StoreGroup>
    <StoreGroup>
      <Store-Code>00003</Store-Code>
      <GroupName>YYYY GROUP</GroupName>
      <OrderType>1</OrderType>
      <Country>USA</Country>
    </StoreGroup>
    </StoreGroupControl>

    How do I pull the Store-Code,GroupName,OrderType,Country column values from this file and load it into a relation table which has colums like this.

    Store-Code
    GroupName
    OrderType
    Country
    Monday, June 17, 2019 8:52 PM

Answers

  • Hi Revathy Menon,

    1. Development environment in Visual Studio:
      SSIS creates an XSD file just once when you click that button.
    2. Run-time environment on the server:
      When you deploy the entire SSIS project to the server the XSD file will be there. There is no need to generate that XSD again.


    Tuesday, June 18, 2019 3:21 PM

All replies

  • Hi Revathy Menon,

    SSIS has XML Source Adapter to do that.

    Just add it to the Data Flow Task, the rest is business as usual.


    Monday, June 17, 2019 8:56 PM
  • great - thanks!  Daily a file in this format '<FileName>_yyyymmddhhmmss.xml' will be dropped on a particular path. My SSIS package should go there an take the file and load the values. In that case, which 'data access mode' should i choose? 
    xml file location or xml file from variable? or XML data from variable?

    similarly, what should i enter in 'XML location' and 'XSD location' fields?  keeping in mind that the file name is gonna change everytime like filename_yyyymmddhhmmss.xml format..
    Monday, June 17, 2019 9:46 PM
  • Hi Revathy Menon,

    It is better to use the XML file from variable access mode, select the user-defined variable that contains a fully qualified path to the XML file.

    Monday, June 17, 2019 10:12 PM
  • Hi Revathy Menon,

    The following links will be helpful.

    Extract Data by Using the XML Source

    Importing XML documents using SQL Server Integration Services

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, June 18, 2019 2:11 AM
  • Thanks -

    1.I tried that -ie) Selected the XML file from Variable and provided the full path. Selected the 'Use Inline Schema'.

    But after that, when I press the 'Columns' in the left hand side, I can't see the columns - getting the error as below

    pipeline component has returned HRESULT errorcode  0*C02092A3from a method call.

    2.Then I tried openrowset (bulk)  getting error as 'You dont have the permission to use Bulk'

    Can someone please help me? Basically, i have a xml file in a folder on daily basis, as mentioned in the question above , and don't have XSD file. Would like to know how to get the columns. 


    Tuesday, June 18, 2019 4:55 AM
  • Hi Revathy Menon,

    Just stay with the XML Source Adapter.

    Course of action:

    1. Uncheck 'Use inline schema' check-box.
    2. Click 'Generate XSD...' button. SSIS will generate it for you.
    3. Click 'Columns' in the left hand side, and pick 'Output name:' in the drop-down.
    Tuesday, June 18, 2019 5:20 AM
  • Great - thanks!

    However, when I automate the job, will that work going forward? Will SSIS create a xsd automatically and do this on daily basis? I want everything to be done automatically ...will that work?

    Tuesday, June 18, 2019 2:42 PM
  • Hi Revathy Menon,

    SSIS creates an XSD file once. When you deploy the entire SSIS project to the server it will be there.

    You would need to regenerate a new XSD just if your XML file structure changed.

    Tuesday, June 18, 2019 2:54 PM
  • thanks!

    Sorry if i am asking the same question again and again -

    I always receive only one xml file in the source folder. While developing the package, I press the 'Generate XSD' button and the xsd file name is populated and uts working fine. My concern is, once I developed the code and deploy the code in the server, will this happen automatically? I mean, Does the SSIS will click the 'Generate XSD' automatically on daily basis and create xsd file on its own?



    Tuesday, June 18, 2019 3:11 PM
  • Hi Revathy Menon,

    1. Development environment in Visual Studio:
      SSIS creates an XSD file just once when you click that button.
    2. Run-time environment on the server:
      When you deploy the entire SSIS project to the server the XSD file will be there. There is no need to generate that XSD again.


    Tuesday, June 18, 2019 3:21 PM
  • thanks much Yitzhak!
    Tuesday, June 18, 2019 5:24 PM