Monday, 1 September 2014

XSD location hardcoded in XML source

Case
The path of the XSD file in the XML Source component is hardcoded, but the path on my production environment is different than my development environment. The XML source doesn't have expressions. How do I make this XSD path configurable?
XML Source Connection Manager Page
without Connection Managers



























Solution
Unfortunately the XML source component does lack some very basic functionalities like the use of a Connection Manager (although ironically it's called the Connection Manager page). The source can use variables as input, but there isn't such option for the XSD file.
XML Task can use Connection Managers


























1) Find Expressions
The XML Source Component doesn't support expressions, but the Data Flow Task itself does. Go to the properties of the Data Flow Task and locate the expressions and click on the ... button

Select Data Flow Task and press F4 to get properties


























2) Select Property
Locate the XMLSchemaDefinition property of your XML Source in the Property Expression Editor and add an expression on it by clicking on the ... button.
Property Expression Editor
















3) Add Expression
Now you can either replace its value by a variable or a parameter if you use SSIS 2012 and above.
Expression Builder
























That's it. Now you have a workaround for the absence of a real Connection Manager. An alternative could be to use a Script Component as XML source or an XML file with an inline XSD schema.

1 comment:

  1. very useful, I hunted for a way to do this, I wouldn't have thought to look at the properties of the DFT. thanks

    ReplyDelete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.