SSIS XML Task with a For Each Loop

Question

I have transformed an XML file

See this question for a complete description

What I want to do know is take this xml task and have it preformed in a For Each Loop. I want all xml files in a specific directory to be transformed and the resulting file to be moved to seperate directory.

It's not working. I'm getting the following error messaes:

Error: 0xC002F304 at XML Task, XML Task: An error occurred with the following error message: "Data at the root level is invalid. Line 1, position 1.".
Error: 0xC002928F at XML Task, XML Task: Property "New Source" has no source Xml text; Xml Text is either invalid, null or empty string.

Can you perform an xml transformation within a for each loop?

Answer 1

You should be able to execute the XML task within a Foreach Loop. The error messages seem to indicate that the XML task has a problem with the source file. You probably need to look at how the file names from the Foreach Loop are applied to the appropriate property in the XML task. Of course, you should ensure that the Foreach Loop is grabbing the correct file list.

Answer 2

The problem is that the XML Task was expecting the variable to contain the XML data that I was trying to transform. In fact the variable contained the file name and path pointing to the XML data.

My coworker showed me the fix.

Inside the XML Task select SourceType = File connection.

Then set the source to point to your file.

Here is the trick. At the bottom you will see Connection Manager and inside will be the file name you pointed your XML Task to. Click on the file name (inside of Connection Manager) and notice that the properties window will display.

Change ConnectionString to any string value (I used "placeholder").

Click on the plus sign next to Expressions and add a connectionstring like this: @[User::FileName]

In this case FileName is a Package variable that contains the path to the xml file.