-
Notifications
You must be signed in to change notification settings - Fork 3
2.2.1 Migrating from MS SQL to MongoDB 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.
- 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.
- Add a "New Connection" for MongoDB.
- 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.
-
Create an Integration Services package using Visual Studio Follow How to create a SSIS Package for Migration.
-
Do the following under the "Data Flow" tab in the Designer Pane:
-
Add Parent and Child table Data Source - OLE DB Source.
-
Right Click to view "Advance Editor" and edit the "Input/output Properties" and make the IsSorted field True for both Parent and Child.
-
Add a Merger Join Component. You can use anytype of joins to get the results.
-
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.
-
Finally, Add MongoDB Destination.
-
GS1 Canada® is a registered trademark of GS1 Canada. Copyright © GS1 Canada 2018.