I will using SQL Server 2019 and Visual Studio 2019 with SQL Server Integrations Services Projects installed to demonstrate SSIS Basics – Load Data from a MySQL Database into SQL Server.

To load data from a remote MySQL Database source into a Microsoft SQL Server Database and SSIS, first you need to install the MySQL Connector ADO.NET on your SQL Server.


Table Of Contents
  1. Install the MySQL Connector ADO.NET
  2. Connect to your MySQL Server
  3. Create a Data Flow Task
  4. Using the native SQL Server Destination or the OLE DB Destination ?
  5. Troubleshooting
  6. ETL , DTS and SSIS Introduction
  7. Bulk Insert Task
  8. Usage Considerations
  9. Links



Install the MySQL Connector ADO.NET

https://dev.mysql.com/downloads/



Connect to your MySQL Server

At your new SSIS Project right-click at Connection Mangers Panel to add a new ADO.NET Connection


Select the MySQL Data Provider


In the Connection section enter the Server Hostname or IP and the Database.


And under Security enter the user and password for the account which should connect to your database.


Also you can test if the connection could be established.

Keep in mind to allow the user and host to access the MySQL database.
#check the permissions
USE mysql;
SELECT user,host FROM user;

# this will grant full access on all databases to the user from the specific remote IP
GRANT ALL PRIVILEGES ON *.* TO ‘user’@’192.168.195.201’;



Create a Data Flow Task

Drag a Data Flow Task to the control flow and double-click on it to change to the Data Flow view.


Drag an ADO NET Source and SQL Server Destination to the Data Flow view and set the link (ADO NET Source Output and SQL Server Destination Input) between them.


Select the previously created Connection Manger for our MySQL Server. For the Data access mode you can choose between a table, view or SQL command to define the data you want to transfer from MySQL to SQL Server. In my case to demonstrate I will simply enter a SQL command to select the department table from the employees sample database.

Employees Sample Database
https://dev.mysql.com/doc/employee/en/


The employees sample database and departments table in MySQL.


OLE DB Source Editor (Columns Page)
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/ole-db-source?view=sql-server-ver15#ole-db-source-editor-columns-page

External Column
View external (source) columns in the order in which you will see them when configuring components that consume data from this source. You can change this order by first clearing the selected columns in the table, and then selecting external columns from the list in a different order.

Output Column
Provide a unique name for each output column. The default is the name of the selected external (source) column; however, you can choose any unique, descriptive name. The name provided will be displayed within the SSIS Designer.



Now double-click on the SQL Server Destination icon to configure the destination database on our SQL Server.

First we need to add a connection manager for the connection to the SQL Server.


Select the Microsoft OLE DB Provider for SQL Server.


Select your SQL Server and a database where you want to import the data.


Create or select a table or view to import the records into.


SQL Destination Editor (Mappings Page)
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/sql-server-destination?view=sql-server-ver15#sql-destination-editor-mappings-page

Use the Mappings page of the SQL Destination Editor dialog box to map input columns to destination columns.


Options
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/sql-server-destination?view=sql-server-ver15#options-2

Keep identity
Specify whether the task should insert values into identity columns. The default value of this property is False.

Keep nulls
Specify whether the task should keep null values. The default value of this property is False.

Table lock
Specify whether the table is locked when the data is loaded. The default value of this property is True.

Check constraints
Specify whether the task should check constraints. The default value of this property is True.

Fire triggers
Specify whether the bulk insert should fire triggers on tables. The default value of this property is False.

First Row
Specify the first row to insert. The default value of this property is -1, indicating that no value has been assigned.

Clear the text box in the SQL Destination Editor to indicate that you do not want to assign a value for this property. Use -1 in the Properties window, the Advanced Editor, and the object model.




Unfortunately we cannot execute the Data Flow Task, if it is not directly on the SQL Server.

If you double-click on the SQL Server Destination you will get the message

The selected data source is on a remote computer. The bulk insert operation can be executed only on the local computer

So click on OK and you still be able to edit but later on execution you will run into an error if you not develop directly on the SQL Server.


As mentioned if you run execute and you develop not directly on the SQL Server, you will run into an error. So you first must deploy the Project to your SQL Server and SSIS catalog.

After deploying the Project to your SSIS catalog, you can execute the Data Flow Task to transfer the departments table from the employees database and MySQL Server to your database in SQL Server.


Finally the data is imported into our SQL Server database and table.



Using the native SQL Server Destination or the OLE DB Destination ?

One big disadvantage with SQL Server Destination, is as mentioned above, that you cannot execute successfully the Data Flow Task if you are running them not directly on your SQL Server.

SQL Server Destination
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/sql-server-destination

The SQL Server destination connects to a local SQL Server database and bulk loads data into SQL Server tables and views. You cannot use the SQL Server destination in packages that access a SQL Server database on a remote server. Instead, the packages should use the OLE DB destination. For more information, see OLE DB Destination.


So if you develop your SSIS Project for example on your notebook, you cannot execute the SSIS Project from it if the SQL Server is on a different machine and you will run into the following error.

The selected data source is on a remote computer. The bulk insert operation can be executed only on the local computer


In contrast you can use instead the OLE DB Destination which not comes with this limitations and also works. Both provider




Conclusion

So if you have to bulk load data into a remote SQL Server database, you should use the OLE DB destination, otherwise to load to a local SQL Server use the SQL Server destination.

The SQL Server destination offers the same high-speed insertion of data into SQL Server that the Bulk Insert task provides; however, by using the SQL Server destination, a package can apply transformations to column data before the data is loaded into SQL Server.

For loading data into SQL Server, you should consider using the SQL Server destination instead of the OLE DB destination.




Troubleshooting

When executing the SSIS Package and in the destination database and table are no imported records, you can check the overview report, so click on yes.


Click on View Messages



Here you can see that the host 192.168.195.201 which is my SQL Server is not allowed to connect to the MySQL Server.


To solve this issue we must grant the user and ip access to the MySQL database.

Keep in mind to allow the user and host to access the MySQL database.

#check the permissions
USE mysql;
SELECT user,host FROM user;

# this will grant full access on all databases to the user from the specific remote IP
GRANT ALL PRIVILEGES ON *.* TO ‘user’@’192.168.195.201’;


Now the execution succeeded and the records from the MySQL database was imported.



ETL , DTS and SSIS Introduction

https://www.jitendrazaa.com/blog/sql/sqlserverintegrationservices/etl-dts-and-ssis-introduction/

ETL is the process in database usage, specially in Dataware house that evolves getting data from different sources (Extract) , performing manipulation operations as per business need (Transformation) and saving on destination database (loading).

DTS (Data Transformation Services)
SQL Server versions 6.5 and earlier, Database administrators (DBAs) used SQL Server Transfer Manager and Bulk Copy Program, included with SQL Server, to transfer data. These tools had significant shortcomings, and many DBAs used third-party tools. When SQL Server 7 was released, “Data Transformation Services” was packaged with it to replace all these tools.
SQL Server 2000 expanded DTS functionality in several ways. Many new types of tasks were made, including the ability to FTP files, move databases or database components, and add messages into Microsoft Message Queue (MSMQ).

SSIS (SQL Server Integration Services)
One disadvantage of DTS was that, for development of package, one must be connected to SQL Server. This is overcome in SSIS.

SSIS is replacement of DTS in SQL Server 2005.  SSIS introduced Business Intelligence (BI) tool, which is development  IDE and installed automatically by SQL Server 2005 in Visual Studio 2005. Thus BI tool gives the advantage of Visual Studio development tools for DTS.

Advantages of SSIS over DTS:

SSIS gives you many new ways to control the flow of your package that could only be done before by writing code. One of the coolest control features is the ability to set up looping within the package. Two tasks, the For Loop Container and Foreach Loop Container, are available for this purpose.

The precedence constraints used to connect one task to the next have been enhanced as well.

There are many tasks and transforms that will reduce the need for scripting. Luckily, when you must write some code, the Script Task uses the Microsoft Visual Studio for Applications environment complete with Intellisense to help you navigate the SSIS object model. Variables, as long as they are in scope, may be accessed by the script.


SQL Server Integration Services (also known as SSIS) is an ETL tool that Microsoft provides to its users in order to extract data from different sources. It then transforms the said data according to the requirements by individual businesses, and loads it into that particular destination (hence ETL).

Read more: Difference Between DTS and SSIS | Difference Between http://www.differencebetween.net/miscellaneous/difference-between-dts-and-ssis/#ixzz6d9cTiAQm



Bulk Insert Task

https://docs.microsoft.com/en-us/sql/integration-services/control-flow/bulk-insert-task

The Bulk Insert task provides an efficient way to copy large amounts of data into a SQL Server table or view. For example, suppose your company stores its million-row product list on a mainframe system, but the company’s e-commerce system uses SQL Server to populate Web pages. You must update the SQL Server product table nightly with the master product list from the mainframe. To update the table, you save the product list in a tab-delimited format and use the Bulk Insert task to copy the data directly into the SQL Server table.

To ensure high-speed data copying, transformations cannot be performed on the data while it is moving from the source file to the table or view.

Usage Considerations

Before you use the Bulk Insert task, consider the following:

  • The Bulk Insert task can transfer data only from a text file into a SQL Server table or view. To use the Bulk Insert task to transfer data from other database management systems (DBMSs), you must export the data from the source to a text file and then import the data from the text file into a SQL Server table or view.
  • The destination must be a table or view in a SQL Server database. If the destination table or view already contains data, the new data is appended to the existing data when the Bulk Insert task runs. If you want to replace the data, run an Execute SQL task that runs a DELETE or TRUNCATE statement before you run the Bulk Insert task. For more information, see Execute SQL Task.
  • You can use a format file in the Bulk Insert task object. If you have a format file that was created by the bcp utility, you can specify its path in the Bulk Insert task. The Bulk Insert task supports both XML and nonXML format files. For more information about format files, see Format Files for Importing or Exporting Data (SQL Server).
  • Only members of the sysadmin fixed server role can run a package that contains a Bulk Insert task.




Links

SQL Server Integration Services
https://docs.microsoft.com/en-us/sql/integration-services/sql-server-integration-services

Data Transformation Services (DTS)
https://en.wikipedia.org/wiki/Data_Transformation_Services

Extract, transform, load (ETL)
https://en.wikipedia.org/wiki/Extract,_transform,_load

Difference Between DTS and SSIS
http://www.differencebetween.net/miscellaneous/difference-between-dts-and-ssis/

OLE DB (Object Linking and Embedding, Database)
https://en.wikipedia.org/wiki/OLE_DB

Open Database Connectivity (ODBC) 
https://en.wikipedia.org/wiki/Open_Database_Connectivity