Background
A dated follow-up to our post on using Informatica – PowerCenter Express.
In that post we copied data between two MS SQL Server Instances.
In this post we will copy data from SQL Server to MySQL.
Outline
Here is a brief outline of the steps we will take:
- Select a Driver to use to communicate with MySQL
- If Driver is not yet downloaded, please do so
- Deploy Driver
- Configure Informatica Client & Server with newly installed driver
- Launch Informatica Desktop
- Design Project
- Create new project
- Physical Data Object
- Create Physical Data Object to Source ( MS SQL Server )
- Create Physical Data Object to Source ( MySQL )
- Create Mapping
- Link Data Sources to ( Source and Destination )
- Validate Mapping
- Run Mapping
Lab
Select Driver to Connect to MySQL
We have to decide whether we want to connect to MySQL using an ODBC or JDBC Driver.
We chose to go with the Vendor’s own JDBC Driver.
Download Driver
Availability
The MySQL Java/JDBC Driver is available here.
OS Platform
As we are installing on MS Windows, and not Unix, we chose to go with Zip file packages, as opposed to Tar, if we were on Unix.
Deploy Driver
Copy Jar file to the following locations
- <Informatica Home>\PCExpress\externaljdbcjars
- Used by Server
- <Informatica Home>\PCExpress\client\externaljdbcjars
- Used by Client
In our case, we copied the extracted mysql-connector-java-5.1.39-bin.jar to:
- D:\Informatica\PCExpress\client\externaljdbcjars
- D:\Informatica\PCExpress\externaljdbcjars
Configure Informatica Client & Server with MySQL Driver
Configure Informatica Server
Launch
From the MS Windows desktop menu, access Start \ Informatica PowerCenter Express \ Launch Informatica Administrator.
Login
The default browser opens in a new tab. Our URL is http://localhost:7009/administrator/
Domain Navigator
The URL for local Domains appear. That URL is http://dbdev:7009/administrator/#admin/Domain_Domain_localhost
New Connection – Choose to Create New Connection
Access the “Domain” \ “Connections” tab.
And, through the “Actions” link drop down, select New \ Connection
New Connection – Select a connection type
Once New \ Connection is selected from the dropdown menu, the “New Connection” panel is displayed.
New Connection – Step 1 of 2
Here is our user credentials and MySQL JDBC Driver Class Name and MySQL Hostname and Port number.
- Username :- xferClient
- Password :- Enter password
- JDBC Driver Class Name :- com.mysql.jdbc.Driver
- Connection String :- jdbc:mysql://DBDEV:3306
New Connection – Test Connection
New Connection – Step 2 of 2
Configure Informatica Developer
Launch
Let us configure Informatica Developer with our newly downloaded JDBC Driver.
If the Developer has not be launched, please do so now.
Access Preferences
Access the Preferences menu by navigating the menu – Window\Preferences.
Informatica \ Connections
Using the tree menu on the left panel, access Informatica \ Connections
Add new JDBC Connection
In the Connections window, click the Add button to add a new JDBC Connection
- Username :- xferClient
- Password :- Enter password
- JDBC Driver Class Name :- com.mysql.jdbc.Driver
- Connection String :- jdbc:mysql://DBDEV:3306
Design Project
Launch Informatica Developer if not already running.
Create New Project
Connect To Repository
Using the Object Explorer, select and connect to the ModelRepository.
Create New Project
Create a new project by using the menu items – File \ New \ Project
Name Project
In the “New Project” window, enter the name you will like to give to the Project.
And, review and change the “Repository Service” if there is one.
Data Object – Source
Create Data Object
Right click on the Project and select New \ Data Object
Select a wizard
From the “Select a wizard” window, please select “Relational Data Object”
Relational Data Object – Select a connection
The “Relational Data Object” window appears and the user is prompted “Select a connection”
Relational Data Object – Choose Connection
The “Choose Connection” window appear ….
Relational Data Object – Specify RDO Name
Specify the name of the Relational Data Object.
Relational Data Object – Select a Resource – Initial
By the “Resource” item, select the Browse button.
Here is the initial contents of the “Select a Resource” window.
Relational Data Object – Select a Resource – Avail all Schemas
Uncheck the “Show Default Schema Only” checkbox.
Unchecking the “Show Default Schema Only” button, reveals all database objects.
Relational Data Object – Select a Resource – Object Selected
Our object, Admin.ErrorLog, is selected below.
Relational Data Object – New Relational Data Object – Form Filled Out
Here is what things look like once our choices are all filled out.
Relational Data Object – Overview
Here is the Overview screen.
It shows the columns and corresponding datatype of those columns.
Data Object – Destination
Choose To Create New Physical Data Object
Use the menu item to initiate creation of a new Physical Data Object.
Relational Data Object – Select a connection
We are back at the initial contents of the “New Relational Data Object” screen.
We are being prompted to choose a connection
Relational Data Object – Choose Connection
Aforementioned, we will like to use JDBC to connect to our target data source.
And, so we will choose the MySQL Data Source that we created earlier.
The name of that Data Source is DBDEVPlatform32JDBC.
Relational Data Object – Select a resource
With our data source connected, we will go on to select a Resource, the specific Data Object that will be processing on the Target.
Please click the Browse button to review the list of objects available on the target.
Relational Data Object – Select a resource – Initial
Upon the “Select a Resource” window appearing, the resources listed will like be limited
Relational Data Object – Select a resource – “Show Default Schema Only” Unchecked
Once we uncheck “Show Default Schema Only“, we are able to see a fuller list of schemas and objects.
Relational Data Object – Select a resource – Resource Chosen
We choose the dblab.errorlog table that we created earlier.
Relational Data Object – Completed
Please review the chosen connections, resource, and change the name of the RDO to afford specificality.
Mapping
Initiate New Mapping
Please initiate New Mapping by clicking on the menu items ( New \ Mapping )
Specify New Mapping’s Name
Specify a name that allows us to identify which objects are being mapped
Before
After
Mapping Canvas
Empty Canvas
Here is our new empty canvas.
Add Source
Drag the source data object ( ErrorLog#Source ) from underneath Physical Data Objects\<Database>\<Object> to the canvas.
As this is our source, we will detail that our access will be “Read“.
Add Destination
Drag the destination data object ( ErrorLog#Destination ) from underneath Physical Data Objects\<Database>\<Object> to the canvas.
As we are now working on the destination, we will specify that our access type is for “Write“.
Mapping – Relational Data Objects
Here are the RDO Objects once the are dropped on the canvas.
Mapping – Relational Data Object – Initiate Menu
Select the Source Object and right click on it’s menu bar to get an insight of options available for that object.
Please select “Auto Link”.
Mapping – Auto Link
Mapping – AutoLink – Initial
In the screenshot above, we have only selected the Source Object and so the “OK” and “Apply” buttons are disabled.
Mapping – AutoLink – Select Source & Destination
Please select the Source and Destination objects
And, click OK or Apply to effect the changes.
Mapping – Validation
Mapping – Validation – Initiation
Please right click on the empty canvas and select the Validate option from the drop-down menu.
Mapping – Validation – Completion
If no problems are found, we are told so.
Mapping – Validation – Completion
Mapping – Run Mapping
Right click on the canvas, and chose to “Run Mapping”
Mapping Running
Errors
There are some errors to be on the lookout for:
Maximum Number of rows for a 24-hour period
It is possible that you might experience the error pasted below:
Mapping run completed with errors.
[MPSVCCMN_10082] The Mapping Service Module [MappingService] could not run the mapping due to the following error: [[DS_10192]
The Integration Service processed the maximum number of rows for a 24-hour period. Wait until the row limit resets at [2016-06-28 00:00:00.000].].
As we are running the Express, Informatica Developer for PowerCenter Express, edition and it is free and licensed for Development purpose, it is not suitable for large datasets.
[…] our last post we spoke about copying data from MSSQL to MySQL via using MySQL JDBC […]