Can I use XSD in SSIS XML Source to generate metadata

Question: 

Is it possible to use XSD file to generate metadata in ZappySys XML Source just like how Native SSIS XML source allows to use XSD file? 

Answer:

No. Because ZappySys uses  different approach to guess metadata. Basically there are two ways you can guess metadata for any XML File, and we chose Method#1 below based on majority of use cases we find.

Method#1 : Scan sample XML values in existing file and detect columns, guess datatypes.  

Method#2 : Use XSD file to detect Metadata for XML Structure.

Now we will explain you few problems with 2nd method. 

• Not every XML File / API Response provides XSD to describe the structure (i.e. metdata).
• XSD Standard sometimes doesnt describe exact length of string datatypes making it hard to define exact length and causing issues like this.
• If your source adds new columns then you have to reply on new XSD and import its again into your XML Source to refresh metadata. Getting this new version of XSD each time can be time consuming. 

Above are few reasons why we chose simple method to detect metadata from existing XML File itself.

Advantages of ZappySys XML Source approach.

1. Totally flexible metadata scan, add/remove columns, change types as needed in future opposed to XSD approach. See this article how to change metadata
2. De-Normalization engine allows to flatten the complex hierarchy  

How to generate / scan for metadata in XML Source for very large files

By default ZappySys metadata detection engine scan only 300 rows for performance reason. If thats not accurate for your use case then use below approach to scan larger range in big files.

1. Double click XML Source to configure (i.e URL, Filter, Connection etc)
2. Click on Columns Tab
3. Change Scan Row count to other than default (i.e. more than 300) and Click on Refresh Column .
4. Select Guess mode (i.e. Guess 4x) , Check Lock, Check Reset and Click OK like below. At runtime if you ever get error about Data Type Issue you can always adjust this later on too. Make sure Lock column is set to avoid setting reset for manually changed columns. For more information on metadata changes check this article

Here is another article : Loading very large XML files in SSIS

https://zappysys.com/blog/read-large-xml-json-file-ssis-fast-process-million-rows/

SSIS Metadata Options – JSON / XML File Parsing