2.2.1 Migrating from MS SQL to MongoDB using SSIS

2.2.1 Migrating from MS-SQl to MongoDB using SSIS.

Data Migration using SSIS

We can use SSIS to migrate data from SQL Server to MongoDB.

Steps to follow for Migration:

Prerequisite: Must have Visual Studio 2017 installed.

• Download and Install SQL Server 2014

• Download and Install CData SSIS Component

• Download and Install SSDT

• Alternatively, we can also download & install ZappySys's SSIS PowerPack and download individual MongoDB Components.

How to create a SSIS Package for Migration

• Open Visual Studio.
• Click on File > New > Project
• Select Business Intelligence > Integration Services from the left Pane.
  

• Create a Data Flow. Add "OLE DB Source" from the left pane (Drag + Drop).
  

• Add "ZS MongoDB Destination" from the left pane (Drag + Drop).
  

• Edit OLE DB Source properties and add source database/table to fetch data.

  • Add a "New" Server.
  • If it is a local server, hosted locally on your workstation use Windows Authentication else use SQL Server Authentication for different Accounts for remote connections.

  

• After we have completed adding the Server, Selecting the database and table from which the data has to be migrated, we can now feed the output to MongoDB destination as follows.

• Editing ZS(Zappy's Sys) MongoDB Destination.
  • Add a "New Connection" for MongoDB.
    
  • Edit the Connction property Accordingly.
    
  • Edit Connection properties. Select newly created MongoDb Connection from drop down.
    
  • Edit Component properties and add the Mongo Table in which we want to Import the data.
    
  • Lastly, match/map the Source and Destination Columns.
    

How to migrate Parent-Child data from RDB to MongoDB using ZappySys compents.

• Contains folowing components:
  • Add Multiple RDBMS Source.
  • Add & Merge Join component for performing JOINS on the RDBMS tables.
  • Add a ZS JSON Generator Transform component for creating a JSON Document from the output of the Merge.
  • Finally, Add a MongoDB Component and Feed the Data to MongoDB Collection.

Layout of the Package.

Step-by-Step Procedure to follow

1. Create an Integration Services package using Visual Studio Follow How to create a SSIS Package for Migration.

2. Do the following under the "Data Flow" tab in the Designer Pane:

   1. Add Parent and Child table Data Source - OLE DB Source.
      

   2. Right Click to view "Advance Editor" and edit the "Input/output Properties" and make the IsSorted field True for both Parent and Child.
      

   3. Add a Merger Join Component. You can use anytype of joins to get the results.
      

   4. Add a JSON Generator Transform component to convert the data into JSON format to later feed it to MongoDB Destination.

      

      • Create Parent and Child elements. Remember to change Output Mode According to your need maerked with Number 1 on the screen shot.

      

   5. Finally, Add MongoDB Destination.