ISSUE:

I'm working from a complex xml schema.  The issue I'm having is that the xsd passes SSIS XML Source validation, I'm able to create each table from its connections, but on import it executes and succeeds but w/o importing any data.

FILE DETAILS:

The xsd is not generated from the xml file.  It is provided by a client to guarantee that it covers all possible relationships. I understand that I can create an xsd in the SSIS XML Source but exporting a full data set each time there is a schema change and then creating a new xsd file is not ideal, nor does it guarantee that all relationships will be correctly generated.

The Schema version does match the XML version (which is a bit suspect since I can still run xml files on the old schema w/o error)

QUESTIONS:

1. I have been unable to find an answer as to why this happens.  Can anyone shed light on why the data is ignored?  I've read that it's not an uncommon occurrence but have not season an answer on why.  Getting an answer to this is probably the most important.

2. My Options?:

a. I am not proficient in C# so i cannot script the import, plus there are over a 100 tables currently being generated. So I am ruling this out

b. This is my most likely approach: I will create an xsd from a large data set and then manually adjust any inconsistencies I find.

If the changes are going to happen to the schema then it is very likely that the package will break.

Furthermore, if the schema does not match the XML (version I am not sure is important) it is a problem, but since you do not get any data then look into what prevents the from being picked e.g. use the DataViewer.

Without a concrete example it is hard to help further.

In my experience, if something is too complex it will not work well, I suggest you persuade the other party to emit XML that is tailored for data interchange and you can easily incorporate it into the ETL process otherwise you are bound to delivering a flimsy solution.

Thank you Arthur

I previously tried testing w/ DataViewer.  The issue is that no rows are retrieved or inserted so there is no data to view.

When I was referring to the versions I was really meaning each new release.  These are important if there are element changes.  Regardless, it does not seem to have an impact.

And I understand the problems w/o having concrete examples.  

I found this article noting the issue and providing documents that should create the same issue. I did not test his documents since I know what the issue looks like.  

But it's back to question #1 in that I have not been able to find any articles stating why.  I would greatly appreciate any insite.

Here is the excerpt:

"In the below example, I am using sample "purchase order" and "customers and orders" XML files which can be downloaded from the MSDN site: http://msdn.microsoft.com/en-us/library/bb387034.aspx andhttp://msdn.microsoft.com/en-us/library/bb387025.aspx respectively.  I also downloaded related XSD files at: http://msdn.microsoft.com/en-us/library/dd489284(v=vs.110).aspx and http://msdn.microsoft.com/en-us/library/bb675181.aspx. Both of these XML files are moderately complex with several layers (or nested) values. Unfortunately, as an XML file's hierarchy structure and complexity increases, often times the supplied XSD file do not parse and load the data correctly. Often the load will occur without error, however no data is transferred. Thus, the Generate XSD button will need to be used to generate a new XSD file to be used for importing. The Generate XSD process works well most often, but you may need to adjust it for special cases within your XML file."

Here is the url: http://www.mssqltips.com/sqlservertip/3141/importing-xml-documents-using-sql-server-integration-services/

I've marked this as the answer because one sentence sums this up here.  "If something (XML Schema) is too complex it will not work well." 

To answer the previous questions

1. Why doesn't it import data?

-After MANY extensive searches on this I was unable to find any resolution.  So I recommend just accepting it doesn't work.

2b.  Creating my own schema

-I did this tedious task and it's not scalable.  Creating each element and manually updating a schema is time consuming and should be a last ditch effort.

In summary, get someone that knows how to script the xml import!

• Marked as answer by Katherine XiongMicrosoft contingent staff, Moderator 5 hours 30 minutes ago