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

Background

In our last post we spoke about copying data from MSSQL to MySQL via using MySQL JDBC Driver.

In this post, we will cover using an ODBC Driver.

 

Lab

ODBC Drivers

Availability

MySQL’s ODBC Driver is available here.

As of June 28th, 2016, the current MySQL ODBC Driver is 5.3.6.

Our targeted machine’s bitness is 64 bit, and we will reach for 64-bit MSI Installer.

DownloadConnectorODBC-GeneralRelease

Install

Please install the downloaded installer.

Wizard

Welcome

License Agreement

LicenseAgreement

 

Setup Type – Custom

SetupType

Setup Wizard

Drive C:

ChangeCurrentDestinationFolder-DriveC

 

Drive D:

ChangeCurrentDestinationFolder-DriveD

Custom Setup

CustomSetup

Wizard Completed

WizardCompleted

 

Administrate

Please install the downloaded installer.

Configure New Data Source

Let us configure a new Data Source

Review List of Installed Drivers

We want to confirm that the MySQL ODBC N.M Drivers from Oracle are installed.

To do so, we will access the Drivers Tab.

Drivers

 

User DSN

Current List

Here are the currently configured User Data Sources

UserDSN-Before

Add New Data Source

Add a new Data Source by clicking the Add button

Create New Data Source

The “Create New Data Source” window appears and the list of drivers is displayed…

CreateNewDataSource - SelectADriver

Please select “MySQL ODBC 5.3 ANSI Driver“.

 

Create New Data Source

Please specify a name for the Data Source, the DB Server’s Hostname and MySQL Port Number.

As well, as the user credentials.

MySQLConnector-ODBC

Validate Connectivity

Click the “Test” button to validate connectivity.

MySQLConnector-ODBC-TestConnection

List – Upon DSN Creation

Once the DSN is created, it should show up in the list of “User Data Sources”.

UserDSN-After

 

Informatica

Register DSN

Let us register the created DSN with Informatica

Preferences

Access Preferences via the menu item Window\Preferences

Connections – Available Connections

On the left panel, Access Connections option
And, on the right panel, access Databases, ODBC
Click on the Add button

Preferences-Connections

Database Connection

  1. Name :- DBDevMySQLNative_x64_User
  2. ID :- DBDevMySQLNative_x64_User
  3. Type :- ODBC

 

DatabaseConnection

Connection Details

  1. User Name
  2. Password
  3. Connection String  ( Name of the created DSN )

ConnectionDetails

 

Relational Data Object

Let us create a new Relational Data Object and bind it to our created Data Source

Select a Wizard

Here we choose to create a “Relational Data Object”.

SelectAWizard - RelationalDataObject

Choose Connections – Available Connections

And, we choose our ODBC Data Source.

ChooseConnection

Relational Data Object – Completed

Upon choosing the Data Source Connection, Resource, and specifying an indicative name we end up with the scree shown below.

RelationalDataSource-Completed

 

Mapping

Create a new mapping

CreateAMapping

 

Add Mapping – Source

AddSource

 

Add Mapping – Destination

AddDestination

 

AutoLink

Initiate AutoLink

AutoLink-Begin

AutoLink – Source Selected

AutoLink-Middle

AutoLink – Destination Selected

AutoLink-Apply

Click OK button to apply AutoLink

Validate Mapping

Validate Mapping

Mapping-Validation

 

Errors

Errors – ODBC Driver Manager/Configuration

If the steps above are not taken there are a couple of bobby traps one might run into.

32-Bit ODBC Drive or System ODBC DSN

If one tries to use a 32 bit ODBC DSN or one tries to use a DSN created as a System DSN and not a User DSN, you might get the error noted below.

Textual

Error returned during ODBC access.

  1. ErrorCode :- -1
  2. Reason :- [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and application.

 

Image

TestConnection-ConnectionFailed-x64-ArchitectureMismatch

 

Connection Failed – Invalid JDBC connection string Missing dbURL

While creating a new Database Connection within Informatica Preferences we received the error pasted below.

Note that the error is that we had not yet entered the name of the ODBC Data Source in the “Connection String” entry box.

Textual

Invalid JDBC connection string Missing dbURL.

Image

InvalidJDBCConnectionStringMissingdbURL

 

Implication

It is interesting to note that though we are creating an ODBC Connection, the error message reads “Invalid JDBC connection string…“.

The implication is thus that the same codeline is used for JDBC/ODBC; ODBC likely relies on the JDBC/ODBC Bridge.

 

Errors – Designer

Schema – MySQL – Nullable Primary Key

Somehow our primary key, id, came back as nullable

PrimaryKeyContainsNullableColumn

Error

Textual

Primary Key [PRIMARY] contains nullable column [id].

Image

PrimaryKeyContainsNullableColumn-cropped

Correction

Toggle the Nullable checkbox besides the Primary Key column, id.

DataStructure-After

 

3 thoughts on “Informatica – PowerCenter Express – Data Sync – MSSQL to MySQL ( Using MySQL ODBC 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