Migrating MySQL Data into MS SQL through SSIS

Database migration is a crucial part of any change management approach for enterprise applications. When you change the database, many things change accordingly impacting your business and technical infrastructure. The main reasons behind the database migration are performance, adapting a more reliable & secure platform, modernizing the DBMS and cost-cutting.

There are both benefits and challenges in any database migration process. The major challenges are source data complexity, data corruption, infrastructure compatibility and security. Therefore, the data migration strategy must be efficient, reversible, fail-safe, auditable and secure.

Here we take a practical example of how a database migration from MySQL to MS SQL is performed using SSIS (SQL Server Integration Services).

What is SSIS?

SSIS is the abbreviation stands for SQL Server Integration Services. It is a primary element of the Microsoft SQL Server and it’s usually used for abroad range of data migration tasks. Generally, SSIS comprises a data warehousing tool which we use for data extraction, transformation, and loading.

Data Extraction

Data extraction comprises the process of recovery of data from various sources.

Data Transformation

Data transformation is about reconstructing data from one format (source system) to another format (destination format).

Data Loading

Data loading is the process of loading of data from a source file. It comprises the process of copying data from the source file and pastes the same in the data storage.

Features of SSIS

The first version of Microsoft SQL Server was released in 2005. SSIS consists of data transformation packages that included various wizards. Especially, the import/export SSIS wizards allows quick data transfer from source to the destination without any format transformation.

As a developer, when you are asked to maintain the SSIS package, you need to utilize a visual development tool (SQL Server Business Intelligence Development Studio (BIDS) based on Microsoft Visual Studio. It has a simple drag and drop user interface to edit the package. It also has a scripting environment which allows writing code. The various elements of the SSIS package define the workflow of the same. When you execute the package, it displays real-time color coding for easy code monitoring.

The primary functional elements of SSIS are:

· Connections

The connection in SSIS denotes logical representation required to connect to an external data source. The tasks can refer to the connections by their names.

· Event handlers

Various events occur during a package execution. Event handlers are declared to capture these events and define actions that need to be taken to respond to these events further.

· Parameters (SQL Server 2012 Integration Services)

You can assign values to the properties with parameters within the package.

· Precedence constraints

Precedence constraints link the tasks in a workflow. It ensures that a special task must be implemented before a certain task.

· Tasks

The atomic work units which perform any action are called Tasks. There are various ranges of tasks as per their functions. 

The built-in transformations of SSIS are:

Below are the steps for the data migration process from MySQL to MSSQL through SSIS:

Prerequisites:

1. Install SSIS and SQL Server Data Tool
2. SQL Server Management Studio and MySQL
   
   

Step to create ODBC User Data Source: 

• Open the ODBC data source.
• Click on add and select MySQL ODBC driver option as shown and click on Finish. 
  
  
  
  Step 2:
• Enter the related details for source database like TCP (IP) Server Name, Login ID and Password.
• It will auto bind the database from Server to the drop-down list.
• You can select the required database from the drop-down list.
• Click on the Test button to check whether it is establishing the connection successfully or not.
  
  

Follow the below-mentioned steps to create SSIS package:

Step 1:

• Open the SQL Server Data Tool and create a new project by clicking on the Business Intelligence tab.
• Select the Integration Services Project from the list.
• Click on OK.
  
  

Step 2:

• Click on the Control Flow tab.
• Drag the Data Flow Task to the Control Flow.
• You can rename it as per requirement. 

• Add ODBC source and OLE DB source control on the Data Flow tab
  
  

Step 4:

• Upon establishing a connection for the source table of the MySQL database, select a user data source name which is created in ODBC.
• Click on OK.
  
  

Step 5:

• Click on the OLE DB Source control following window get open then click on new.

Step 5:

• Follow the same process as mentioned above to create a connection for the destination table for the MsSQL database. 
  
  

Step 6:

• Add Merge Join for inserting or deleting values into the destination table.
• The Merge Join Transformation Editor pop-up window opens.
• It gets a database record from both the source and destination tables. 
  
  

Step 7:

• Add a Conditional Split tool to check record as per its primary key value so that it helps to insert or delete records.
• You can check this in the Conditional Split Transformation Editor.
  
  

Step 8:

• Add the OLEDB Destination tool and select the respected output from a conditional split. 
  
  

Step 9:

• Add OLEDB Command Tool as shown for update and delete & select respected output from the list.
  
  

Step 10:

• To Insert record, select the destination connection and select the table from dropdown.
  
  

Step 11:

• For OLE DB command choose destination string. 
  
  

Step 12:

• On Component properties tab you can write SQL command for update and delete. 

Step to run SSIS package:

• Either we run it manually as shown below by clicking on the Start button or we can create SQL Job and scheduler for it to run automatically. 
  
  

Conclusion

SQL Server Integration Service (SSIS) is a component of the Microsoft SQL Server database software that can be used to conduct a wide range of data integration tasks. SSIS is a fast & flexible data warehousing tool which is used for data extraction, loading, and transformation. It makes data moving easy from one database to another database. SSIS also can extract data from a wide variety of sources like SQL Server databases, Excel files, Oracle, and DB2 databases.

Besides, SSIS comprises graphical tools & wizards for performing workflow functions like sending email messages, FTP operations, data sources, and destinations.