SSIS How do I pass XSD path from variable

Question

I am running SQL/BIDS 2005 (current PROD environment).

I'm trying to do some basic POC and the task is fairly trivial:

• Get the XML file from the hard drive
• Do some mapping
• Update DB with the results

Steps I’ve taken:

• Create a new package
• Add Data Flow Task
• Add XML source task in the Data Flow

It is all working perfectly fine when I provide the XML and XSD location in the XML source properties.

I can see all the XML fields fine so I can proceed with the mapping etc.

The above is obviously not acceptable and what I need to do is pass both XML and XSD values from variables (and later on form the config file).

So I went to Data Flow’s Expressions and pointed the XML and XSD to the variables (I evaluated the results and it was perfect).

…except it doesn’t work.
When I try running the open the XML source task it throws the following:

The component has detected potential metadata corruption during validation. Then it gives the stack with the Object Reference nopt set to an instance... starting with the Dts.Pipeline.ManagedComponentHostValidate(IDTSManagedComponentWrapper90 wrapper)

As I mentioned I validate the xml doc and schema and its well formatted. When passing those directly in the xml source it works. It only doesn't work when I am trying to pass those values from the Data Flow level (as Expressions).

Answer 1

You may find SSIS 2005 reacting poorly to added columns in a dataflow. They didn't seem to get picked up very well in the "arrows" between subsequent steps of the dataflow (this was improved in later versions of SSIS).

You can look at the properties, specifically the "metadata" tab, on these arrows to see whether they actually include all of your columns. I am afraid I often had to delete all of my dataflow steps and build them up again methodically, making sure I was using the complete set of columns from scratch.

Yes, you can use a variable most places. What you have to look for is the "expression" property. It isn't always obvious. That's where you tell it to use the variable. When you do this in BIDS, it will use whatever the variable is set to in your development environment, and it will set the text property to reflect that variable setting. This is only temporary. Later, at runtime, it will in fact use the variable you give it at runtime. But during development your variable must actually point to, e.g., a valid file with the correct structure. Otherwise you won't be able build your dataflow. That is just the way SSIS works. That has not been changed in later versions.

P.S., I just added more detail to a related question here.

If you "edit" the connection manager it will show you the edit window. That's not the one you want. Look at the "properties" window, which is where you will find the "Expressions" entry, in front of an empty box.

Click on the empty box, and it will show you a button with three dots on it. Click on that button. This pulls up the "Property Expression Editor". There is a dropdown with properties like "ConnectionString" on the left. On the right, there is ANOTHER button with three dots. Click on that button to pull up the "Expression Builder". If you have declared any variables, you will find them listed in the upper left hand corner, e.g., as User::VariableName, and you can then drag them into the Expression box, where they will appear as @[User::VariableName]. Not obvious, but doable.