How To Define an XSD file for an SSIS XML Source using an expression

I’ve been looking at migrating some SQL Server Integration Services (SSIS) packages lately, and as part of the move, am parameterising a lot of settings to improve the ease of future configuration changes.  One of these changes involved a package that reads from an XML source and uses an XML Schema Definition (XSD) file to validate the source file.  However, with the XML source being in the data flow, you’re not allowed to set an expression on this property to assign a value at runtime.  Or at least not directly.  So, if you want to assign the XSD location from a variable or project parameter, just follow these simple steps.

It’s actually very easy to achieve this result, however the dynamic value has to be assigned via the Control Flow, not directly in the Data Flow.

First of all, set up a variable, or project parameter that will contain the path to your XSD file.  You can either assign this explicitly or create some sort of expression to grab it, or load the values from a database, really, however you want to get it.  All you need to do is make sure that when the package execution hits the control flow, you have your XSD value.

Next, select the step in the Control Flow that contains your XML source to be validated and right-click, select Properties.  In theProperties explorer, find the Expressions box and click the ... button next to it.  This will launch the following dialogue box:

Here, if you select the [XML Source].[XMLSchemaDefinition] property, you can assign any path to the XSD, or a reference to your variable or parameter containing the path.

Not immediately obvious, but it’s very simple to do and worth remembering for any other Data Flow settings that you’d like to parameterise.