How to Migrate Databases to SQL Server with SSIS

Microsoft SQL Server Integration Services (SSIS) is a part of SQL Server installation. Ir has been designed to assist with such data migration tasks as data extraction, transformation and loading. Using those capabilities SSIS can do easy and safe data migration for SQL Server, Oracle, PostgreSQL and MySQL.

Basic features of SQL Server Integration Services:

• Warehousing tool allows to extract, transform and load the data
• Special graphical tool simplifies building and modifying SSIS packages
• Import/Export Wizard helps to create data migration tasks
• SSIS can simplify overall maintenance of SQL Server
• SSIS provides environment for development and real-time monitoring of the package execution

Installing SQL Server Integration Services

• Launch setup.exe of the SQL Server installation pack
• Choose "New SQL stand-alone installation or add features to an existing installation" option in the SQL Server Setup Wizard
• It is recommended to enable automatic checking updates by selecting "Use Microsoft Update to check for updates"
• Check the rules for running SQL Server on the "Install Rules" page
• Select "Add features to an existing instance of SQL Server" on the "Installation Type" pane if SQL Server has been installed. Otherwise, select "Perform a new installation of SQL Server".
• Select "Integration Services" option under "Shared Features" on the "Feature Selection" page. Choose "Client Tools SDK" to install assemblies if it is required.
• On the "Server Configuration" page choose an account used to run the SSIS service.

Migrating Database to SQL Server via SSIS

Here we explore how to migrate MySQL to SQL Server using SQL Server Integration Services, however the same technique is applied to any other source DBMS:

• Create a new migration task, choose MySQL as data source and SQL Server ADO.NET as destination
• Right-click to create "New Connection" in the "Connection Manager" window. Choose MySQL and configure all necessary connection details in the appeared "MySQL Connection Manager"
• Back to "Connection Manager", double-click on the MySQL source to launch the source editor and configure the data extraction query, such as: SELECT col1, col2, col3 FROM tbl1
• Open the ADD.NET Destination to set up the SQL Server destination. Add new connection and enter the necessary connection details for the server and database.
• From the "Data access mode" menu choose "table or view" and select table or view to populate.
• Execute the migration task and after it is completed the SQL Server will host the MySQL data

Have any questions? Contact us