2

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

Community
  • 1
  • 1
DataWrangler
  • 1,804
  • 17
  • 32

1 Answers1

0
  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.
Ferdipux
  • 5,116
  • 1
  • 19
  • 33
  • The XML files and the XSD are both placed in the location as set in the SSIS package, also ReadOnly is not checked in. Still the issue persists, anything else that might be causing the issue ? – DataWrangler Feb 06 '17 at 12:14
  • @Joby, try to open XML Source, and hit Ok, or in Advanced Editor - hit Refresh button. If error persists - please post screenshots of XML Source Editor. – Ferdipux Feb 06 '17 at 12:44
  • I have added the screenshots of both from the normal editor and Advanced Editor, can you please take a look. – DataWrangler Feb 06 '17 at 14:52
  • @Joby, thanks for the screenshot. Unfortunately, it is not the right one and gives no insight. Please, post - open XML Source editor and post Connections Manager window (1st one), and open XML Src Advanced editor and show Component Properties tab. – Ferdipux Feb 06 '17 at 16:30
  • Hey @Ferdipux, have added the new screenshots, hope this time I have added it right :) – DataWrangler Feb 07 '17 at 11:51
  • @Joby, this time is fine. On the "Variables" screen your variable `sXMLFileName` has default value *filename*. Your XML Source at Validation event (which fires when you save your package in VS) tries to load file specified by this variable. Please mind - it happens not at runtime, but when VS is saving package; at this moment the variable is initialized with its default value. Way to fix - adjust `sXMLFileName` default value to point to valid XML file. – Ferdipux Feb 07 '17 at 12:10
  • can we leave the `sXMLFileName` blank, would that fetch all the XML files. As we the files that are posted in the path don't follow a naming convention. Our expectation is to process all files that have an extension *.XML – DataWrangler Feb 07 '17 at 12:40
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/135072/discussion-between-ferdipux-and-joby). – Ferdipux Feb 07 '17 at 12:55
  • Thanks the SSIS package is working fine now, also Job also executes the package successfully. Just adding the change that was done as advised by Ferdipux Set the **Delay Validation** property to `True`. – DataWrangler Feb 10 '17 at 09:38