Informatica – PowerCenter Express – Datatype – XML

Background

As I dig more into Informatica and MySQL, found out that we were not properly copying XML column from SQL Server into MySQL.

 

Table Structure

Here is our table structure.

MSSQL

Admin-CourseManagerUserLog-MSSQL

MySQL

Admin-CourseManagerUserLog-MYSQL

 

Informatica

Original Table Structure

Physical Data Object – Admin.CourseManagerUserLog

PhysicalDataObject

Explanation:

Later on, was cognizant that our XML Column, details, precision or size is 0

 

Mappings

Mappings

Explanation:

Later on, noticed that for the details column, the arrow is not full.

 

Mappings - Later

 

Run Mappings

Run the mapping.

And, things ran well.  Was not until later that I found that the details column on the destination, MySQL, was empty.

 

Create View on the Source

Here is a workaround.

In the view, add new columns to track the unsupported XML Column.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if object_id('[Admin].[CourseManagerUserLog_XMLColumnConverted]') is null
begin

	exec('create view [Admin].[CourseManagerUserLog_XMLColumnConverted] as select 1/0 as [shell] ')

end
go

alter view [Admin].[CourseManagerUserLog_XMLColumnConverted]
as

SELECT
		    [id]
		  , [username]
		  , [resourceID]
		  , [status]
		  , [message]
		  , [details]
		  , [detailsAsVarcharMax]
				= cast(	[details] as varchar(max))	
		  , [detailsAsVarchar]
				= cast(	[details] as varchar(8000))	
		  , [userID]
		  , [refID]
		  , [record_created]
		  , [appointmentID]

from   [Admin].[CourseManagerUserLog]
GO

GRANT SELECT ON [Admin].[CourseManagerUserLog_XMLColumnConverted] TO [public]

go

 

Informatica

Resource

Resource

Explanation

  1. details, XML Column, comes in xml.  But, size is 0
  2. detailsAsVarchar, varchar column, size is 8000 ( max size in MS SQL Server )
  3. detailsAsVarcharMax [varchar(max) ], size is 0, as well

 

Mappings

After AutoLink

After AutoLink ….

Mappings-After-Autolink

Those columns with matching names are Auto Linked.

 

After Column – detailsAsVarchar is linked to details

And, then we manually linked the columns whose names are not matched.

Here in:

  1. detailsAsVarchar (MSSQL) right-arrow details (MySQL)

Mappings - Later - Details - 0448PM

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

 

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

Informatica – PowerCenter Express – Data Sync – Introduction

Forward

In an online article, Robert Sheldon, reviews a couple of ETL tools.

The article is titled “Moving beyond SSIS: 5 third-party SQL Server tools for ETL“. And, it is available here.

He compares a hand fold of ETL Tools; here is his list:

  1. Informatica – PowerCenter
  2. SAS – Enterprise Data Integration Server
  3. Business Objects – Data Integration
  4. Pentaho – Data Integration
  5. Talend – Open Studio

 

PowerCenter Express

In this post, we will go over Informatica PowerCenter Express.

There are two versions of PowerCenter Express

  1. PowerCenter Express – Personal Edition ( Free )
  2. PowerCenter Express Professional Edition ( Cost )

 

Product Comparison

Informatica PowerCenter Express – Data Sheet ( Here

ProductComparison

Pricing

Here is current pricing

  1. Single User License – $8000

PowerCenterExpressProfessionalEdition

Download

Journey to https://marketplace.informatica.com/solutions/pcexpress and click on the download link.

Login

You will be asked to login with an existing account or create a new user.

marketplace

As I did not have an existing account, entered my hotmail email address.  But, a day later still no email in my hotmail.com.

Thank goodness for my Google email account has that one promptly received a link that I clicked on and continued with my download.

Media

DownloadCenter

Details

We chose to download:

  1. PowerCenter Express Server/Client Windows 64-bit version
  2. PowerCenter Express Client Windows 32-bit version

 

The Client install comes bundled with the Server version, but we chose to download and have it for keepsake.

License key

We will need a license key during the install, and one was sent to the email address we registered.

Installation

Server Client Folder

Here is the Server Client Folder Structure

ServerClientFolder

Please initiate install by double-clicking on the install.bat file

License Agreement

Accept the License Agreement by checking the “I accept the terms of the license agreement.” button.

LicenseAgreement

Welcome

In our case, as this was a fresh install, we chose the “Install Informatica PowerCenter Express

Welcome

 

License And Installation Directory

Specify the folder where you saved the License Key file.  And, the folder you want to target the install.

Specify License Key File and Install Directory

LicenseAndInstallationDirectory

License Key File is not valid

If you try to use the License Key file that comes with the install binary, you will get the error pasted below.

License.txt

License-txt-installbinary

The license key file is not valid

TheLicenseKeyFileIsNotValid

Actual License Key

LicenseKeyActual

Specify License Key File and Install Directory ( Corrected )

LicenseAndInstallationDirectory-Actual

Pre-Installation Summary

PreInstallationSummary

Installing …

Installing-20160315-0907AM

Configuring Installation

ConfiguringInstallation

Domain and Repository Configuring

This is a very important step.

Please specify and confirm the Domain password.  Note the Repository name.

DomainAndRepositoryConfiguring-After

Post Install Summary

  1. Informatica Administrator Home Page : http://localhost:7009
  2. Domain name : localhost
  3. Host name : localhost
  4. Port number : 7006

 

Post-InstallSummary

Preparatory Work

Let us itemize some of the prep work we need to have in place:

  1. Identify Source & Target Object
    • Source :- DB Server, Database name, Tables and Views
    • Target :- DB Server, Database name, Tables and Views
  2. Identify Authentication Mechanism and Credentials
    • SQL Server principally support two authentication modes – Windows Authentication and SQL Server Authentication
    • We will utilize SQL Server Authentication
    • Identify SQL User along with password
  3. Review and grant permission to Database Objects
    • Source – Grant read permission to source objects
    • Target – Grant write permission to targeted objects
  4. Backup Database Objects
    • As we be modifying the targeted DB, please make sure you backup all of the targeted tables

Administrator

Data Sources

Configure Data Sources

Let us configure the data sources

To do so we will launch a web browser and enter the URL – http://localhost:7009/administrator

Connections – Shell

The only connections listed is ProfilingWarehouseConnection

Connections-Empty-Cropped

 

Connections – Add New Connection

Via the panel, let us create a new connection

Initiate

To do so, let us do this:

  1. Access Domain \ Connections
  2. From the Domain Navigator bar, click on Actions \ New \ Connection

Connections-Empty-New-Cropped

 

New Connection

Connection Type

The New Connection window appears …

Connnections-NewConnection-Sql Server

From the list of supported databases, we chose Sql Server

New Connection – Step 1 of 3

We entered the following info:

  1. Name – The network name of the Primary Database Host
  2. ID – The ID defaults to same, we can change to a new name for whatever reason that may be desired
  3. Description – Left empty
  4. Username – Enter username
  5. Password – Entered password

 

DBPROD-Page1

 

New Connection – Step 2 of 3
Before

DBPROD-Page2-Post-v2

 

After

DBPROD-Page2-PostChanges

 

  1. Metadata Access Properties
    • Connection String
      • Originally :- jdbc:informatica:sqlserver://<hostname>:1433;SelectMethod=cursor;databaseName=<dbname>
      • Revised :- jdbc:informatica:sqlserver://DBPROD:1433;SelectMethod=cursor;databaseName=HRDB
  2. Data Access Properties
    • Connection String
      • Originally : servername@dbname
      • Revised : DBPROD@HRDB 

 

There is an important difference between the Metadata Access Properties and Data Access Properties

  1. Usage Scenario
    • The metadata access properties is used during design mode; in essence during metadata discovery
    • While the Data Access properties is used during data viewing and processing

 

New Connection – Step 3 of 3

The third page allows for more advanced customized.

DBPROD-Page3-v2

We will leave as is for now.

 

Connections – Additional Connection

Please repeat for the destination data source.

 

Information Developer

Usage

Outline

  1. Create a new project
  2. Add Data Object
    • Add Source table
    • Add Target table
  3. Add Mapping
    1. Add Data Object – Source as read
    2. Add Data Object – Destination as write
    3. Link Destination and Source Data Objects
  4. Review data in Source
  5. Map data

 

Repository

Connect to Repository

Upon initial launch of Information Developer, one needs to connect to a repository.

To do so click on the menu item – File \ Connect to Repository …

ConnectToRepository-BeforeRepositorySelected

 

Choose Service

On the Connect to Repository window, click on the Browse… button

ChooseService-0548PM

 

The Domain_localhost\ModelRepository service is shown.

Please select it.

Choose to Repository – Select a Repository – Post Repository Selected

Once a repository is selected, it is reflected once we return to the “Select a Repository” window.

ConnectToRepository-SelectARepository-0551PM

Click the Next button selected.

Connect to Repository – Open Project

OpenProject-20160315

Choose any project that you will like opened.

Click the “Finish” button.

 

Project

New Project

Let us create a new project.

To do so, click on the menu items New \ Project.

 

Create Project

NewProject-After-Cropped

 

  1. Name
    • Give the Project a new name.
    • In our case, we chose syncData

 

Data Object

New Data Object

Using the menu, please click File \ New Data Object…

Select a Wizard

From the list of Wizards, choose “Relational Data Object”

SelectAWizard

 

Select a Connection

Before

RelationalDataObject-SelectAConnection-Before

After

RelationalDataObject-SelectAConnection-After

Changes

  1. Connection
    • We chose the Connection we defined in the Administrator window
    • In our case DBPROD
  2. Resource
    • { Click the Browse button to select a resource }
  3. Name
    1. Originally :- Test_TestTriggers
    2. Changed :-  Test_TestTriggers_Source

 

 

Select a Resource

Before

SelectAResource-Before

After

SelectAResource-TestTrigger

 

Changes

  1. Uncheck “Show Default Schema Only
    • If you do not do so, the only objects that will be shown are those that match the Schema indicated in the Administrator web screen
    • As we did not specify a Schema, we were not offered any objects to choose from

 

Overview

The overview screen is shown.

This screen shows the table’s structure.

Overview

 

Mapping

New Mapping

Using the menu, please click File \ New Mapping…

Mapping – Before

Mapping_Before

Mapping – After

Mapping_After

 

Mapping – Empty Canvas

Here is an empty Canvas that we will drag and drop into.

Mapping_EmptyCanvas

 

Mapping – Add Destination

Drag the source, DBBETA in our case, into the Canvas.

DragDrop-AddSource

 

In the Add To Mapping window, choose the Read button.

This is the right option because we are specifying the Source.

 

Mapping – Add Destination

In the Add To Mapping window, choose the Write button.

This is the right option because we are specifying the Destination.

 

 Mapping_AddDestination

 

Mapping – Auto Link

Our sample is easy, and we will go the Auto Link path.

Before

AutoLink-Before

 

After

Select both items to autolink the two.

AutoLink-After

 

Data Viewer

Access Data Viewer

Choose the Source object and the menu changes to allow you to access the Data Viewer.

RunDataViewer

Data Viewer – Output

Here is our source data….

RunDataViewer-Output

Run Mapping

Access Run Mapping

Click on an empty spot in the Mapping Canvas.

And, click on the menu items ( Run \ Run Mapping ).

We only have a few records and so completed very quickly.

 

Review Job Run History

Administrator Console

Access the Administrator Console, http://localhost:7009/administrator/#monitoring/Domain_localhost:$$:DIS:$$:jobs, and review job run history.

 

Review-Administrator-Jobs

 

Data Review

Backup Destination

Of course, before running anything against a database you took a DB level backup, or in our case a table level backup


   use [HRDB]

   SELECT
		   [tID]
		  ,[vID]
		  ,[triggerID]
		  ,[record_created]
		  ,[record_last_updated]

		--into [DBBackup].[MarketingBackup].[Test_TestTriggers.201603150438PM]
		into [DBBackup].[MarketingBackup].[Test_TestTriggers.201603150440PM]

  FROM [Marketing].[Test_TestTriggers]

Compare Backup against Current

SQL

	SELECT
		   [sourceData] = 'Before'
		  ,[tID]
		  ,[vID]
		  ,[triggerID]
		  ,[record_created]
		  ,[record_last_updated]


	from [DBBackup].[MarketingBackup].[Test_TestTriggers.201603150440PM]

	SELECT
		   [sourceData] = 'After'
		  ,[tID]
		  ,[vID]
		  ,[triggerID]
		  ,[record_created]
		  ,[record_last_updated]

  FROM [Marketing].[Test_TestTriggers]

Output

compareData

Explanation
  1. We had 4 records before running the job
  2. Now we have 9 records
    • 5 records representing new entries in the source since 2016-02-25

References

Administrator

  1. MS SQL Server Connection Properties
    https://network.informatica.com/onlinehelp/analyst/961HF2/en/index.htm#page/analyst-tool-guide/GUID-3849D181-CE1C-4EFC-8B40-076762C57817.1.16.html

 

Developer

  1. Informatica PowerCenter Express- Getting Started
    http://oracleedq.com/informatica-powercenter-express-getting-started/

 

Webinar

  1. Introducing PowerCenter Express: 10 Minutes to Enterprise-Class Data Integration
    Andrew Taylor, Informatica and Lalitha Sundaramurthy, Informatica
    https://www.brighttalk.com/webcast/10477/99285/introducing-powercenter-express-10-minutes-to-enterprise-class-data-integration

 

Evaluation

  1. A First Look at Informatica’s New PowerCenter Express
    http://smartbridge.com/a-first-look-at-informaticas-new-powercenter-express/

 

ETL Tools

  1. Top 15 Free Extract, Transform, and Load ETL Software
    http://www.predictiveanalyticstoday.com/top-free-extract-transform-load-etl-software/

Informatica – PowerCenter Express – Error – SEVERE: com.informatica.sdk.dtm.ExecutionException: [LDTM_0072] RR_4036 Error connecting to database

Background

Trying to play around with Informatica Power Center Express on a new box and ran into the error pasted below.

What were we trying to do?

Basically all we were trying to do is review data on a Data Source.

To do so, one will create a project add a data source and access Run \ Ran Data Viewer via the menu.

 

Diagnostic

Server Log

We know that that Job is ran on the server.  And, so we connected to the Administrator Portal via http://localhost:7009/administrator/#monitoring

And, from the left panel, via the Navigator accessed Domain_locahost/DIS/Jobs.

Monitoring \ Domain \ DIS \ Jobs

Domain-DIS-Jobs

Here is what our Jobs log look like:

Monitoring

Monitoring

Unfortunately, it is not possible to copy the actual text of the error message.

 

Server Logs

As it was not possible to capture the text, went googling to determine where the logs are actually kept.

<INFA_HOME> \tomcat\bin\disLogs\ms

Found Help here:

  1. Is anyone using PowerCenter Express on Windows 8?
    https://network.informatica.com/thread/18946

 

Rakshith Jain provided excellent support

IsAnyoneUsingPowerCenterExpressOnWindows8

as he directed us to INFA_HOME\tomcat\bin\dislogs\ms

Folder Structure

FolderStructure

Error

Opening the most current file from INFA_HOME\tomcat\bin\dislogs\ms revealed the error pasted below.

Image

RR_4036-ErrorConnectingToDatabase

Text


2016-03-15 12:20:15 &lt;DTMLoggerThread_6&gt; INFO: DIRECTOR,	TM_6020,	Session [Test_TestTriggers] completed at [Tue Mar 15 12:20:15 2016].
2016-03-15 12:20:15 &lt;DTM-pool-3-thread-5&gt; SEVERE: RR_4036 Error connecting to database [
Error: Failed to create and initialize SQL OLE DB instance. Reason [1008]: [An attempt was made to reference a token that does not exist.
]
Database driver error...
Function Name : Connect
Database Error: Failed to connect to database using user [dbscript] and connection string [].].
2016-03-15 12:20:15 &lt;DTM-pool-3-thread-5&gt; SEVERE: com.informatica.sdk.dtm.ExecutionException: [LDTM_0072] RR_4036 Error connecting to database [
Error: Failed to create and initialize SQL OLE DB instance. Reason [1008]: [An attempt was made to reference a token that does not exist.
]
Database driver error...
Function Name : Connect
Database Error: Failed to connect to database using user [dbscript] and connection string [].].
2016-03-15 12:20:15 &lt;DTM-pool-3-thread-5&gt; INFO: Total LDTM Execution Time: 3,883ms

 

Internet

With a copyable error message went googling and found matches:

  1. ERROR: “Error: Failed to create and initialize SQL OLE DB instance. Reason [1008]: [An attempt was made to reference a token that does not exist” while running a preview on data or running a profile job using a Microsoft SQL Server database connection
    https://kb.informatica.com/solution/23/Pages/_0/160624.aspx?docid=160624&type=external
  2. “ERROR: Failed to create and initialize SQL OLE DB instance” when using Microsoft SQL Server with PowerCenter 9.6
    https://kb.informatica.com/solution/23/Pages/5/159016.aspx?

 

Cause And Solution

Image

CauseAndSolution

Textual

Cause

This issue occurs due to a change in how PowerCenter connects to SQL Server.
Previous versions up to 9.5.1 used the OLEDB mechanism while 9.6 requires the SQL Server Native Client (SNAC).

Solution
SNAC is not part of the default installation of the SQL Server client software and needs to be installed manually.
It is part of the Microsoft SQL Server 2012 Feature Pack, which can be downloaded from the Microsoft website at
http://www.microsoft.com/en-in/download/details.aspx?id=29065

Download

Please follow the link and search for Microsoft® SQL Server® 2012 Native Client.

Download-Details

As we are on a 64-bit platform chose that package.

 

Install Microsoft SQL Server 2012 Native Client ( SNAC )

What SNAC packages are currently installed?

Accessed Programs and Features to determine which Microsoft SQL Server Native Clients are currently installed.

ProgramAndFeatures-Before

We see that the currently installed packages are:

  1. Microsoft SQL Server 2008 Native Client

Installed Microsoft SQL Server 2012 Native Client

Installed the package.

 

What SNAC packages are now installed?

Once the package installed successfully, we now have Microsoft SQL Server 2012 Native Client.

ProgramAndFeatures-After

 

Data Viewer

Let us revisit and see if we can use Data Viewer again.
Same menu items – Run \ Run Data Viewer.

RunDataViewer

 

Output:

DataViewer

 

Applicable

Here is our current version

Informatica Administrator

Version 9.6.1 Hotfix2

Administrator

 

Information Developer For PowerCenter Express

Version 9.6.1 Hotfix2

InformationDeveloperForPowerCenterExpress