SSIS Tip – Xpath for Root Level Attributes (XML Tasks)

For some reason, SSIS’s XML Data source does not allow you to directly access root level attributes. Therefore, we’ve got to use a XML Task and variables in order to pull this data in.

  1. Create a variable for every attribute, use the string data type (change later in data flow if need be)
  2. Use the following example (fill your own Xpath in the Second Operand)

This example will only pull in one value. Should there be multiple attributes with the same name, you’ll have to use a Foreach loop.

One Comment on “SSIS Tip – Xpath for Root Level Attributes (XML Tasks)”

  1. Great tip!
    It is pretty typical of SSIS however that there is this ‘hidden’ constraint on the way it works. It is exactly this kind of thing that sucks up loads of developer time, and I’ve been on a few evaluations where developers have struggled with this kind of thing as compared to other ETL tools.

    It’s a shame, and I’m sure a patch/tweak to SSIS to allow the root schema to become an output wouldn’t kill the dev team!

Leave a Reply

Your email address will not be published. Required fields are marked *