How to export multiple SQL Server Tables into JSON files

[daniel]

Introduction

This article provides step-by-step guidance on exporting more than one table into a JSON file using our SSIS Export JSON File Task.

Prerequisites

• SSIS PowerPack: Download and install the ZappySys SSIS PowerPack from here.

Steps

1. Add the SSIS Export JSON File Task: Drag and drop the SSIS Export JSON File Task onto your SSIS package.

2. Create a New Connection: Open the task and create a new connection. This can be an OLEDB, ADO.NET, or ODBC connection.
   

   

3. Write the SQL Query: Use an SQL query to call both tables and use an INNER JOIN with a conditional. For more details on SQL JOINs, refer to this link.
   

   

   Example SQL:

   SELECT b.author, b.price, e.hiredate, e.age
   FROM [Employee table] e 
   INNER JOIN dbo.Books b
   ON b.author = e.name;
   

4. Configure the Target: Go to the Target tab, select a target file, and run the package. The result will be in the JSON file.
   

5. Use Custom Layout: A Custom Layout is another option. You will get a new tab with that name.
   

   

6. Create Datasets: You can create different datasets for each table.
   

   

7. Use SQL Query: Alternatively, you can use an SQL query like in the previous example.
   

8. Configure JSON Elements and Attributes: Create your JSON file with elements and attributes. For more details, refer to this example link.
   

   

9. Run the Package: Execute the package to generate your new JSON file.
   

Video Tutorial

Watch a detailed video tutorial on this process:

Conclusion

This article provides a comprehensive guide, complete with screenshots for each step, on configuring the SSIS Export JSON File Task with multiple tables from an SQL server. For any issues or further assistance, feel free to contact our support team via chat on our website

References

• SSIS Export JSON File Task
• SSIS PowerPack
• SQL JOIN