How to copy data from MongoDB into SQL Server in SSIS

[daniel]

Introduction

This article will show you an example of exporting data from MongoDB using our MongoDB source to an SQL server.

Prerequisites

• SSIS PowerPack: Download and install the ZappySys SSIS PowerPack from this site.
• MongoDB database

Steps

1. Add a Data Flow Task: Drag and drop a Data Flow task into your SSIS package.

2. Add a MongoDB Source: Inside the Data Flow, add a MongoDB source component.

3. Create a MongoDB Connection:

   • Set up the host, username, password, and database.
   • Check the other tabs for additional configuration options.
   • Test the connection to ensure it is working.
     
     

4. Select the Table:

   • Choose the table in access mode.
   • Use the preview button to check the data.
     
     

5. Use Query Mode (Optional):

   • For more complex database calls, use query mode.
   • Example query:
     
     

     SELECT _id, CustomerID, CompanyName, RegistrationDate, Tags, stars 
     FROM OrdersList 
     WHERE 
     (Tags IN ('ab','bb') AND stars >= 4) 
     OR 
     (Tags NOT IN ('db','cc') AND RegistrationDate > 'DATE({{User::varDate}})')
     

6. Add an OLE DB Destination Component:

   • Drag and drop the component.
   • Connect it to the MongoDB source.
   • Select a database connection to a table, map the columns, and run the package to see the result.
     
     

7. Use Upsert Destination (Optional):

   • Another destination component option is our Upsert destination.
   • For more information, refer to our article here.
     
     

Video Tutorial

Watch a detailed video tutorial on this process:

Conclusion

This tutorial ensures a seamless process for exporting data from your MongoDB table into your SQL Server using SSIS. If any issues arise, feel free to contact our support via chat on our website.

References

• Upsert destination
• MongoDB source