How to Convert JSON into XML using SSIS

[hardik]

Introduction

JSON (JavaScript Object Notation) is a widely used data interchange format. While JSON is more popular in many scenarios, there might be cases where you need to convert JSON data to XML. SSIS, with the help of suitable components, can facilitate this conversion.

Prerequisites

Ensure you have the following installed:

1. SQL Server Data Tools (SSDT): Install SQL Server Data Tools, the development tool for SSIS.
2. ZappySys SSIS PowerPack: Download and install the ZappySys SSIS PowerPack.

A simple example to convert JSON into XML

This example is simple, we only need to get books’ information like the category, author, title, and price.
Below are the steps to convert JSON to XML using SSIS PowerPack.

1. Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
   
2. Double-click on the DataFlow task to see the DataFlow designer surface.
3. From the SSIS toolbox drag and drop JSON Source on the dataflow designer surface.
   
4. Double-click on the JSON Source component and configure it as mentioned in the below screenshot.
   Click on the Example1 link to get the sample JSON string, then click on the Select Filter button and select the Array Filter $.store.books[*], then uncheck the include parent(slow) checkbox as we are not going to load the parent columns as well in the XML, click on PREVIEW to preview the source sample data, which we’re going to convert it into an XML.
   
   
5. Now, Drag and Drop the XML File Destination component from the SSIS toolbox on the dataflow designer surface and connect it with the JSON Source component.
   
   
6. Double-click on the XML File Destination component and configure it, First Create a new connection using the File Connection Manager, create a new file, and select the folder location, as mentioned in the below screenshot.
   
   
7. Do the right click on the Mappings, and click on the Add Element(s) (Below this node) to add the elements for XML.
   
   
8. Now, Click on the Add Multiple (Bound) radio option, select all the required elements you want to add, and then click on the OK button.
   
   
9. We’re done with the Mappings, you can see the sample preview in the Sample Data in the Live Preview section.
   
   
10. Run the package and you will be able to see the exported XML data.
    
    

Conclusion

In this tutorial, we covered the basic steps to convert JSON to XML using SSIS with the help of ZappySys components JSON Source & XML File Destination. While JSON is widely used, there are scenarios where XML might still be required. Ensure you adjust configurations based on your specific JSON structure and XML requirements.