If you use default settings to read data then it may result into OutOfMemory Exception so we will outline few techniques which will enable high performance Streaming Mode rather than In-memory load of entire file.<\/p>\n
Prerequisites<\/span><\/h2>\nBefore we parse very large XML or JSON files using SSIS , you will need to make sure following prerequisites are met.<\/p>\n
\n- SSIS designer installed.\u00a0Sometimes it is referred as BIDS or SSDT (download it from Microsoft site<\/a>).<\/li>\n
- Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\n
- You have at least two sample files… One file must be small dataset (less than 10 MB if possible). We will\u00a0 use small dataset file during design mode to get metadata and see data preview on the Component UI.<\/li>\n
- Second XML file is the big file with full dataset you like to parse at runtime.<\/li>\n
- Make sure\u00a0SSIS PowerPack<\/em><\/a>\u00a0is installed.\u00a0Click here to download<\/a>.<\/li>\n<\/ol>\n
<\/p>\n
Step-By-Step : Reading large XML file (SSIS XML Source)<\/h2>\n
Now let’s look at how to read large XML file (e.g. 3 Million rows or more) using ZappySys XML Source<\/a>\u00a0in SSIS. Below steps are identical for\u00a0 ZappySys JSON Source<\/a>\u00a0 too (Except Step#7).<\/p>\n\n- Open SSIS Designer and drag Data Flow from SSIS Toolbox<\/li>\n
- Double click Data Flow Task to switch to Data flow designer<\/li>\n
- Now drag ZS XML Source on the surface from SSIS Toolbox.<\/li>\n
- Double click ZS XML Source and specify small dataset file<\/span>\u00a0 path you like to parse. (e.g.\u00a0 c:\\data\\customer_small.xml )<\/li>\n
- Click on Select Filter button to find Node which will be treated as Array. Once you close the Filter Browse Dialog. Append –FAST<\/span>\u00a0 Your Expression may look like below.
\n$.Root.Row–FAST<\/span><\/li>\n- Now uncheck\u00a0\u00a0<\/strong> Include Parent Columns option<\/span><\/li>\n
- Check\u00a0<\/strong>Enable Performance Option<\/span>\u00a0 (For JSON Source Skip this step)<\/strong><\/strong><\/strong>NOTE: Try to enable Performance mode setting after you Select Filter (by using smaller dataset file). Once filter is set you can check enable performance mode and make sure following two settings are correctly set (Option#2 needs new version – SSIS v4.1+ \/ ODBC v1.4+).
\n(1) On Array Handling Tab<\/strong> – Set Array node name (must be only one entry<\/strong>). For example your Filter is $.DATA.ORDER[*] then you can enter ORDER<\/span>
\n(2) On Advanced Filter Options Tab<\/strong> – Enter all unwanted Tag names you like to Skip. For example if you have other arrays like PRODUCT or CUSTOMER then enter PRODUCT,CUSTOMER<\/span>
\n<\/div><\/div>
\n<\/strong><\/strong><\/li>\n- Your setting may look like below
\n
\n<\/strong><\/strong><\/p>\n![\"Configure](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/01\/ssis-read-very-large-json-xml-file-parse-stream-mode.png\")
<\/a>Configure XML source or JSON Source for Very Large Data File (Streaming mode for High Performance)<\/p><\/div><\/li>\n
- Click Preview to verify data (Adjust Filter if needed to extract correct Hierarchy)\n
![\"Preview](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/01\/ssis-preview-sample-xml-json-data.png\")
<\/a>Preview XML or JSON File data using SSIS XML Source or JSON Source<\/p><\/div><\/li>\n
- Click on Columns Tab<\/li>\n
- Change Scan Row count to 3000 or more and Click on Refresh Column<\/span>\u00a0.<\/li>\n
- Select 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<\/a>\n
![\"SSIS](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/01\/ssis-metadata-scan-options-xml-json-parsing.png\")
<\/a>SSIS Metadata Options – JSON \/ XML File Parsing<\/p><\/div><\/li>\n
- Click OK to save settings.<\/li>\n
- Now Right click on XML Component > Click Properties. Change DirectPath<\/span>\u00a0 property to original file path (large file) (e.g. c:\\data\\customers_large.xml<\/span>\u00a0 ). Save Package.<\/li>\n
- Now you can run your SSIS Package from Designer or Command line. As you see in the below screenshot that there is virtually no memory pressure when you enable stream mode. Thanks to ZappySys unique XML \/ JSON Parsing Engine. When streaming mode is enabled\u00a0file is not loaded into memory for parsing rather than that it only reads record by record to process very large JSON or XML file.In our below example we used Windows 7 Desktop, 16GB RAM, 4 Core i7 64 bit CPU<\/strong>. It took around 3 Minutes to Parse 3 Million Records (1.2 GB big XML file)<\/span>\u00a0. If you Parse JSON file then it can be even faster due to compact size.\n
![\"Reading](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/01\/ssis-read-very-large-xml-json-file-example.png\")
<\/a>Reading Very Large XML or JSON File using SSIS (Stream Mode for High Performance)<\/p><\/div><\/li>\n<\/ol>\n
Step-By-Step : Reading very large JSON file (SSIS JSON Source)<\/h2>\n
<\/p>\n
Step-By-Step : Reading large XML file (SSIS XML Source)<\/h2>\n
Now let’s look at how to read large XML file (e.g. 3 Million rows or more) using ZappySys XML Source<\/a>\u00a0in SSIS. Below steps are identical for\u00a0 ZappySys JSON Source<\/a>\u00a0 too (Except Step#7).<\/p>\n Configure XML source or JSON Source for Very Large Data File (Streaming mode for High Performance)<\/p><\/div><\/li>\n Preview XML or JSON File data using SSIS XML Source or JSON Source<\/p><\/div><\/li>\n SSIS Metadata Options – JSON \/ XML File Parsing<\/p><\/div><\/li>\n Reading Very Large XML or JSON File using SSIS (Stream Mode for High Performance)<\/p><\/div><\/li>\n<\/ol>\n\n
\n$.Root.Row–FAST<\/span><\/li>\n
\n(1) On Array Handling Tab<\/strong> – Set Array node name (must be only one entry<\/strong>). For example your Filter is $.DATA.ORDER[*] then you can enter ORDER<\/span>
\n(2) On Advanced Filter Options Tab<\/strong> – Enter all unwanted Tag names you like to Skip. For example if you have other arrays like PRODUCT or CUSTOMER then enter PRODUCT,CUSTOMER<\/span>
\n<\/div><\/div>
\n<\/strong><\/strong><\/li>\n
\n
\n<\/strong><\/strong><\/p>\n<\/a><\/a><\/a><\/a>Step-By-Step : Reading very large JSON file (SSIS JSON Source)<\/h2>\n
![\"Parse](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/01\/xml-parse-array-extract.png\")
<\/a>![\"Parse](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/01\/xml-parse-exclude-nodes-by-name.png\")
<\/a>