Search code examples
sqlxmlssissql-agent-job

Using a variable to process all *.xml files using SSIS

I am new to SSIS package, so any help would be really appreciated. The SSIS code was built using the VS2005 and its setup as a SQL agent Job in the 2005 SQL Server. Now as we are migrating to 2016 SQL server we see that the same SQL agent Job fails. Hence we decided to rebuild the package using VS2012, to see if that would solve the issue.

Processes done on the Control Flow Tab: enter image description here

Data Flow task: enter image description here

Variables used: enter image description here

we have used a ForEachLoop: enter image description here

Errors Show in the Data Flow Task: enter image description here

enter image description here

We are still not able to make out what's causing the issue, we also refereed the full step by step process given in the below link.

Click here!

If any more information is required I can provide that too!!

XML Source Editor Error: enter image description here

XML Source Advanced Editor: enter image description here

XML Source Components Tab: enter image description here

XML Source Connection manager tab: enter image description here

Solution
    1. I'd rather use VS 2015 for packages targeting SSIS 2016.
    2. Your error comes from DataFlow task - XML Source. At Validation step, which occurs on Package Save, XML Source tries to open XML file using sXMLfilename variable value; the value of sXMLfilename at this moment in VS is the variable default value. Therefore, validation in Visual Studio fails and you see the error messages.
      To pass validation, XML source should be able to open both XML and XSD file. Check for these files at Developer's station, migrate files if necessary from your old server. For XML file you can use any valid XML input file, XSD schema should match it. Specify valid path to XML file as the default value for sXMLfilename variable.