2.2.2 Migrating from MySQL to MongoDB using SSIS - Gs1TestTeam/MongoDB_Task GitHub Wiki

2.2.2 Migrating from MySQL to MongoDB using SSIS.

Migrating from MySQL to MongoDB via SSIS will be described below. In this case we will be migrating two nested sample tables, one will have some information about people uniquely identified by field "ID", and another table about the hobbies of these people, connected by field "resident" as foreign key to the first table.

1. To be able to make a connection to MySQL to fetch data from SSIS, and MySQL ODBC driver needs to be set up if it is not already. Below is an image of a set up MySQL ODBC driver in System DSN tab of ODBC Data Source Administrator window. To set up MySQL ODBC driver follow this tutorial https://kb.iu.edu/d/amsw

2. Create a new Integration Services Project in SSDT. Instructions can be found Here

3. Click on the Data Flow Task. To connect to MySQL database drag and drop an ADO NET Source from SSIS toolbox.

Right click on ADO NET source and click "Edit". In the ADO.Net Source Editor select "New...", and in the "Configure ADO.Net Connection Manager" select "New...", to add a new connection.

In the "Connection Manager", select the provider as ".Net Providers\MySQL Data Provider" and fill out the form with server name, database name, port number, user id and password for the mySQL server.

Click "test connection" to test if it works, then click "ok" in the "Configure ADO.Net Connection Manager".

Back in the "ADO.Net Source Editor" you will now see the connection you just created listed as the ADO.NET connection manager. Select "data access mode" as "SQL command", and write the SQL command to extract ordered data using an ORDER BY clause.

4.The data being migrated into MongoDB in this example will be put into a parent-child document structure. Create a second connection for the embedded child table (1 connection per table).

5. For both ADO NET Sources, right click each ADO NET Source and select "Show Advanced Editor...". In advanced editor window select "Input and Output Properties" tab, click on "ADO NET Source Output" and set IsSorted to True in the "common properties".

Expand "ADO NET Source Output" and "Output Columns". Click on the column sorted by in SQL statement (in this case "ID") and set SortKeyPosition to indicate the order it was sorted in, in this case "1".

Do the same in the ADO Source for child table, but since child table was sorted by field "resident"(the foreign key), click on the field "resident" and set SortKeyPosition to "1". In cases where the data was sorted in descending order this field would be set as a negative value, and if data was sorted by multiple columns, SortKeyPosition for each column should be set to indicate sorting order, such as 1 for the first, -2 for second descending, 3 for last ascending. Unsorted columns should be set to 0.

6. Drag and drop a JSON Transform component to the Data Flow Task. Connect the two ADO NET Sources to the JSON Transform component by clicking and dragging the blue arrow to the JSON component.

7. Double click the JSON Transform component to open the "JSON Generator Transform" Window. The data from ADO Sources are listed under "Datasets". Click on a dataset name to open "Add/Edit Dataset" window. Here you can change the dataset name (in this case it will be renamed as "Parent", and the nested dataset will be called "Child"), and check the "Root dataset" check box to set it as top level element. You can also see where the data for this dataset will be coming from in the "Select input for this dataset" drop down. You can also see the name of the dataset input from each ADO NET Source by clicking on the arrow connecting the ADO NET component to the JSON Transform generator and seeing its "DestinationName" in "Common Properties".

8. Click on "Mappings" then click on "insert new element under selected node" icon to open the "Add/Edit Attribute" window. Select "Add Multiple (Bound)" to add multiple records, and check off the boxes of the columns you want to add as parent document.

9. To add a nested table, first create an element to store it in. Click on "Mappings" then click on "insert multi row element under selected node", and in the "Add/Edit Element(Array of rows)" window select "Document Array" as "Element type", write the element's name in "Element Name" box, select "Child" dataset as "Dataset for this element". Next create a join. Select the columns from each dataset that the join should be on. Then click on "+" to add them. To finish click "OK".

10. To add nested columns click on the newly created array element then click on the "insert new element under selected node" icon. In the "Add/Edit Attribute" windows and select "Add Multiple(Bound)" and check off the boxes of the columns that should be in the nested document. click "OK".

The completed nested JSON mapping should look like this:

11. Add a ZS MongoDB Destination component, and connect the output of the JSON Transform to it. Refer to the Data Migration section for configuring a MongoDB connection. The final product should look like this:

12. Click "Start" to run the migration.