Table of contentPrerequisites
MySQL ODBC Connector
- Physical database – Username/password with sufficient rights to meet end goals intended with use of this connector-definition
- The correct ODBC driver must be installed
- A working and technically accessible MySQL database(server) compliant with current supported versions. Please consult latest supported platforms matrix for more information.
- User/Login information that can be used to access the MySQL database (with sufficient rights for the tasks that you will be using it for).
- MS SQL Server Management Studio – Username/password with sufficient rights to meet endgoals intended with use of this connector-definition.
- Access to the Microsoft ODBC Administrator panel
Creating a DSNBefore creating a DSN make sure the correct MySQL ODBC driver has been installed. Go to: Control panel > System and Security category > Administrative tools > Data Sources (ODBC) See Figure 1.
Figure 1Select the System DSN tab > add > select the appropriate driver > Finish See Figure 2.
- In the Data Source Name field enter a logical name for the data source
- In the TCP/IP Server field enter the IP or hostname of the server running MySQL
- If an other port then the default 3306 is used, enter it in the Port field
- In the User field enter the username of the user that has sufficient rights to connect to the MySQL engine
- In the password field, the password belonging to the user must be entered
- In the database field, the required database can be selected based on the credentials provided
- Click the details button to see more options
- Under the Connection tab check the Interactive Client option
- In the field Character Set enter utf8
Figure 3Click the Test button, if the connection is successful click OK to confirm and save the DSN.
Creating a linked server to MySQLThis chapter will describe the creation of a Linked server to MySQL.
- Open the MS SQL Server Management Studio
- Select > Server Objects > Linked Servers
- Select > New Linked Server
- In the Linked server field enter a logical name for the Linked server
- Check the Other data source option under Server type
- In the provider field select Microsoft OLE DB Provider for ODBC Drivers
- In the Product name, enter the Data source name of the System DSN configured in the previous chapter
- In the Data source field, enter the Data source name of the System DSN configured in the previous chapter
- Leave the rest blank
- To the left select the Security page
- And check the option Be made Without using a security context
- Click the OK button to confirm the settings and create the linked server
Creating the connectorThis chapter will assume that a DSN is configured as described in the Creating a DSN chapter. Select > Tools > Settings > Add > MySQL ODBC See Figure 6.
Figure 7Inputs explained
- Datasource name
- The logical name for the connector within DM-ETL+
- Existing DSN
- Check this option to use a existing DSN
- Type DSN
- Here the DSN type can be selected
- The following options are available
- System DSN
- User DSN
- Here the available DSN are shown for the selected DSN type
- DSN custom settings
- Check this option to use custom DSN settings
- The server name or ip address of the server
- ODBC driver
- Here the installed drivers are shown, Select the appropriate MySQL ODBC driver
- Here the port that is configured in the MySQL installation must be entered
- Here the username that has sufficient rights must be entered
- Here the password belonging to the username must be entered
- Default schema
- Here a default schema can be defined
- In the Datasource name field enter a logical name for the connector
- Check the option existing DSN
- In the Type DSN field select System DSN
- In the DSN field select the DSN created earlier
- In the Default schema field enter the default schema
Figure 8When all information is entered click the Test Connection button. If the connection is successful click OK to confirm and save the connector.
Importing data to DM-ETL+In DM-ETL+ select Diagram to add a diagram to the solution. Select the connector as data source. We created this in the previous chapter. See Figure 9.
Figure 9Then double click the diagram title bar to enter the diagram main window. To the left click the appropriate Owner that contains the required database. The available databases and views of the selected Owner will now be shown. See Figure 10.
Figure 10To the left select the required Table to add it the the diagram task.
Figure 11When all required data is selected and imported, press the Save button to save the diagram and press the Solution button to go back to the solution. The data is now successfully imported to DM-ETL+.