Archive for July 2008

Using the XML Task to Run an XPath Query

I came across a question about this the other day, and thought I’d share the answer here, as there doesn’t seem to be much information out there about it.  The questioner wanted to know how to read a portion of an XML document into a variable. Fortunately, the XML Task allows you to do this by running an XPath query. To configure the task, follow these steps:

  1. Set the operation type to XPATH.
  2. Specify a source for the XML (file, variable, or directly input).
  3. Set the OperationResult to save the results in a variable.
  4. Set the SecondOperandType to Direct Input.
  5. Specify the XPath query in the SecondOperand.
  6. Set the PutResultInOneNode option to False.
  7. Set the XPathOperation to Node list.

image

If, after configuring this, the variable is empty, it’s likely that the XPath isn’t correct. This can be caused by a number of things. A good way to test your XPath is this web page: http://www.whitebeam.org/library/guide/TechNotes/xpathtestbed.rhtm It allows you to upload your XML file, and then run your XPath query against it.

Another common problem occurs when the XML file uses namespaces. If it does, you can try using XPath like this: //*[local-name()='Setup' and  namespace-uri()='http://test.org']

Hopefully this helps, if you are working with XPath in SSIS.

Presenting at the SSWUG Virtual BI Conference

I’m going to be presenting a few sessions at the upcoming SSWUG Virtual Business Intelligence Conference. It’s occurring from September 24-26, 2008. It has a very impressive list of speakers (I’m really not sure how I made it on the list – it’s quite an honor). I’ll be presenting on configurations in SSIS, unit testing SSIS (one of my favorite topics), and the new Report Builder functionality in SQL Server 2008.

I’m looking forward to it, but it will be a new experience for me. The sessions will be recorded in advance, and then the speakers will be online during their presentation time slot to answer questions from the audience. It sounds interesting, and since it’s being broadcast online, there’s potential for a much wider range of attendees.

If you’re interested in the conference, please check out the web site.

Presenting at Midlands PASS

I’m presenting at the Midlands PASS chapter in Columbia, SC tomorrow night, July 17th. I’ll be presenting on SSIS configurations and some common approaches for implementing them. Configurations are one of the best features about SSIS, but also one of the most confusing. If you’re in the area, and would like to learn more, please stop by.