Introduction
In this article, we will demonstrate how to extract two or more objects from a JSON and use them within the same data flow using the JSON Parser Transform. Working with JSON data in SSIS can be complex, especially when dealing with multiple nested objects. The ZappySys JSON Parser Transform simplifies this process by allowing you to extract and manipulate multiple JSON objects within a single data flow task, providing a streamlined and efficient approach to handling JSON data in your ETL processes.
Prerequisites
- SSIS PowerPack: Download and install the ZappySys SSIS PowerPack from the Customer Download Area or get the trial version.
Steps
-
Add a Data Flow task to your SSIS package.
-
Inside the Data Flow, add a Source component that contains a JSON in a column. In our case, we will use our JSON Source with Example 1 from our component and check the option Output as RAW document.
-
Preview the data and copy the JSON example from the column you will use. This will help in the following steps.
-
Connect a Multicast component from Microsoft to the JSON Source.
-
Drag and drop the required JSON Parser Transform components. We will use two for our objects: Employees and Books.
-
In the first JSON Parser Transform, we will extract Employees. Select the column with the JSON, copy and paste the example into the component, use the Select filter, choose the object, and preview the data to verify. Click OK to save the configuration.
-
In the next JSON Parser Transform, repeat the process for the Books object. Preview the data and click OK to save the configuration.
-
Connect destination components to all the JSON Parser Transform components.
-
Run the package and check the results.
Video Tutorial
Conclusion
Following these steps, you can effectively use multiple objects from the same source component within a data flow, leveraging the ZappySys tool for efficient data retrieval and integration. For assistance or inquiries, contact our support team via chat on our website.
References
For more information, refer to the JSON Parser Transform and JSON Source documentation.