How to fix source is empty error in XML source while using Foreach loop container in SSIS 2012?
I have an issue with a very simple task in SSIS 2012.
I have a for-each container that runs in FOR-EACH-FILE Enumerator mode. I want to read a target folder with XML files. The path to the folder is correctly configured. The files field is set to *.xml
The variable mapping is defined with the follwing Variable: User::FileVar , Index 0.
Now I add a simple data flow task inside the container. The dataflow task only has a XML-Data Source task, that's it. For the XML Data source task, the XSD location is set. When I click choose columns, I can see the columns from the XSD schema.
BUT: When I save the XML task , I always get the error message: The Property XMLDataVariable is empty. I tried both data Access modes, XML file from variable and XML data from variable. The error message remains, I cannot run the package.
I don't use any expressions, neither at the foreach loop container nor at the data flow task.
I dont know what's wrong here, I did the steps exactly as shown in some tutorials for older versions of SSIS.
Do you have any ideas?
Answer
The issue is that the XML Source is trying to validate the existence of the given file during the design time. However, you will know the file name only during runtime when the Foreach loop container executes and loops through every XML file available in a given folder.
I recreated an SSIS 2012 package using my answer to one of other SO questions. SSIS reading multiple xml files from folder
I was able to reproduce the error The property "XMLDataVariable" on the XML Source was empty
On the XML source, I set the property ValidateExternalMetadata to False. Setting this to false will force the package not to verify the existence of the xml file path during design time.
I was successfully able to execute the package.
Hope that helps.