Informatica – PowerCenter Express – Data Sync – MSSQL to MySQL ( Using MySQL JDBC Driver )

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:

  1. Select a Driver to use to communicate with MySQL
  2. If Driver is not yet downloaded, please do so
  3. Deploy Driver
  4. Configure Informatica Client & Server with newly installed driver
  5. Launch Informatica Desktop
  6. 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.

DownloadConnectorJ-0606PM

 

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.

 

BeginYourDownload

 

Deploy Driver

Copy Jar file to the following locations

  1. <Informatica Home>\PCExpress\externaljdbcjars
    • Used by Server
  2. <Informatica Home>\PCExpress\client\externaljdbcjars
    • Used by Client

 

In our case, we copied the extracted mysql-connector-java-5.1.39-bin.jar to:

  1. D:\Informatica\PCExpress\client\externaljdbcjars
  2. 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/

InformaticaAdministratorUserAuthentication

 

Domain Navigator

The URL for local Domains appear.  That URL is http://dbdev:7009/administrator/#admin/Domain_Domain_localhost

Connections-New-Connection-Cropped

 

New Connection – Choose to Create New Connection

Access the “Domain” \ “Connections” tab.

And, through the “Actions” link drop down, select New \ Connection

Connections-AccessMenuActionNewConnections

 

 

New Connection – Select a connection type

Once New \ Connection is selected from the dropdown menu, the “New Connection” panel is displayed.

 

NewConnection-SelectAConnectionType

 

New Connection – Step 1 of 2

Here is our user credentials and MySQL JDBC Driver Class Name and MySQL Hostname and Port number.

  1. Username :- xferClient
  2. Password :- Enter password
  3. JDBC Driver Class Name :- com.mysql.jdbc.Driver
  4. Connection String :- jdbc:mysql://DBDEV:3306

 

 

NewConnection-Step1of2

 

New Connection – Test Connection

NewConnection-ConnectionTestedSuccessfully

New Connection – Step 2 of 2

NewConnection-Step2of2

 

 

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.

Winddow-Preferences

 

Informatica \ Connections

Using the tree menu on the left panel, access Informatica \ Connections

 

Preferences-Connections-Before

 

Add new JDBC Connection

In the Connections window, click the Add button to add a new JDBC Connection

  1. Username :- xferClient
  2. Password :- Enter password
  3. JDBC Driver Class Name :- com.mysql.jdbc.Driver
  4. Connection String :- jdbc:mysql://DBDEV:3306

ConnectionDetails-ConnectionDetailsCompleted

 

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.

InformaticaDeveloper

Create New Project

Create a new project by using the menu items – File \ New \ Project

NewProject

 

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.

CreateANewProject

 

Data Object – Source

Create Data Object

Right click on the Project and select New \ Data Object

New-DataObject

 

Select a wizard

From the “Select a wizard” window, please select “Relational Data Object”

SelectAWizard-RelationalDataObject

 

Relational Data Object – Select a connection

The “Relational Data Object” window appears and the user is prompted “Select a connection

RelationalDataObect-SelectAConnection

 

 

Relational Data Object – Choose Connection

The “Choose Connection” window appear ….

RelationalDataObect-ChooseConnection

 

Relational Data Object – Specify RDO Name

Specify the name of the Relational Data Object.

RelationalDataObect-SelectAResource-IsNextStep

 

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.

RelationalDataObect-SelectAResource-InitialScreen

 

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.

RelationalDataObect-SelectAResource-Ongoing

 

Relational Data Object – Select a Resource – Object Selected

Our object, Admin.ErrorLog, is selected below.

RelationalDataObect-SelectAResource-Ongoing

 

Relational Data Object – New Relational Data Object – Form Filled Out

Here is what things look like once our choices are all filled out.

RelationalDataObect-CreateARelationalDataObject

 

Relational Data Object – Overview

Here is the Overview screen.

It shows the columns and corresponding datatype of those columns.

RelationalDataObect-PostCreation

 

Data Object – Destination

Choose To Create New Physical Data Object

Use the menu item to initiate creation of a new Physical Data Object.

NewPhysicalDataObject-Menu-20160628-1122AM

 

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

RelationalDataObject- SelectAConnection

 

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.

RelationalDataObject - ChooseConnection

 

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.

RelationalDataObject - SelectAResource

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

SelectAResource-ShowDefaultSchemaOnly

 

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.

SelectAResource-ShowDefaultSchemaOnly ( Unchecked - Upon)

Relational Data Object – Select a resource – Resource Chosen

We choose the dblab.errorlog table that we created earlier.

SelectAResource-ShowDefaultSchemaOnly ( Unchecked )

Relational Data Object – Completed

Please review the chosen connections, resource, and change the name of the RDO to afford specificality.

RelationalDataObject - Completed

Mapping

Initiate New Mapping

Please initiate New Mapping by clicking on the menu items ( New \ Mapping )

1232PM-NewMapping-Menu

 

Specify New Mapping’s Name

Specify a name that allows us to identify which objects are being mapped

Before

1232PM-Mapping-CreateAMapping-Initial

After

1232PM-Mapping-CreateAMapping-After

 

Mapping Canvas

Empty Canvas

Here is our new empty canvas.

Mapping-Canvas-Empty

 

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“.

AddToMapping-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“.

AddToMapping-Read

 

Mapping – Relational Data Objects

Here are the RDO Objects once the are dropped on the canvas.

RelationalDataObjects-BeforeLinking

 

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.

RelationalDataObjects-Menu-Appear

Please select “Auto Link”.

Mapping – Auto Link
Mapping – AutoLink – Initial

AutoLink-20160628-1241PM

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

AutoLink-20160628-1244PM

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.

Mappings-Validate ( Cropped )

Mapping – Validation – Completion

If no problems are found, we are told so.

Mappings-Validate-NoProblemsFound

Mapping – Validation – Completion

 

Mapping – Run Mapping

Right click on the canvas, and chose to “Run Mapping

MappingInitiate-20160628-0105PM

Mapping Running

RunMapping

 

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.

 

References

cData Software

JDBC Data Sources

  1. Create Informatica Mappings From/To a JDBC Data Source for Cassandra
    Link
  2. Create Informatica Mappings From/To a JDBC Data Source for Marketo
    Link

One thought on “Informatica – PowerCenter Express – Data Sync – MSSQL to MySQL ( Using MySQL JDBC Driver )

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s