How to export multiple SQL Server Tables into XML files

[daniel]

Introduction

This article provides step-by-step guidance on how to export more than one table into an XML file using our SSIS Export XML File Task.

Prerequisites

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

Steps

1. Add the SSIS Export XML File Task: Drag and drop the SSIS Export XML 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 dbo.Books b
   INNER JOIN [Employee table] e
   ON b.author = e.name;
   

4. Configure the Target: Go to the Target tab, select a target file, and run the package. You will see the result in the XML 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 XML Elements and Attributes: Create your XML file with elements and attributes. For more details, refer to this example link.
   

   

9. Run the Package: Execute the package to generate your new XML 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 XML 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 XML File Task
• SSIS PowerPack
• SQL JOIN