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

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