Data Integration Hub Errors

This page will be a running list of tips and workarounds for Data Integration Hub 10.

Data Integraton Hub DX_ETL DateTime Precision Issue

This issue was found in Informatica Data Integration Hub 10 on Windows Server 2012 using SQL Server 2012 as the Repository.

The PowerCenter Workflow DX_ETL can abort in mapping m_SET_CURRENT_CUTOFF giving the errors:

  • Timestamp parameters with a scale, must have a scale less than ten and a precision equal to 20 plus the scale. You specified a precision of 999 and scale of 9.
  • Fractional second precision exceeds the scale specified in the parameter binding.
Here are some steps you can take to correct this problem. Some or all of these steps may be required to correct the issue.

Formatting Telephone Numbers

I created this simple PowerCenter mapplet to format phone numbers. Informatica Developer (IDQ) may have a different or easier way to do it but I wanted something I could use in PowerCenter without the hassle of maintaining a separate object in IDQ and having to import it into PowerCenter. You can alter the mapplet to format other data like postal codes, SSNs, etc.

Download the mapplet and give it a try.

Mapplet Overview

My approach is a simple one, take the phone number, remove all special characters, then reformat the number with dashes as 999-999-9999. This approach allows me to ignore how the phone number comes into the mapplet, for example partially formatted , not formatted or formatted correctly but not how I need it.

The mapplet's input is the telephone number without a country identifier. In addition to the reformatted phone number, the mapplet also outputs:

  • a Pattern (example xxx-xxx-xxx) based on the input number (this could be used for a quick/dirty data profiling)
  • the phone number parsed into its parts, area code, central office
  • the length of the input phone number

Read further to see detail on the mapplet works.

Diary of an Informatica 9.6.1 HF1 Install

Here are some notes on an Informatica PowerCenter 9.6.1 64 bit server install.  This also includes the setup of the Informatica Cloud Secure Agent (64)

Most of the install went smooth.  However I want to list some of the problems I encountered.

Use A Mapping Parameter in an SAP ABAP Mapping

There are two areas for logic, the Dynamic Filter and the Static Filter. Use Static Filter and you don't have to create an ABAP block.


VBScript to Convert Excel to CSV

Using Excel files as the source for Informatica mappings can be more trouble than it's worth. You have to setup a connection and name a range in the Excel document which can be difficult to maintain. I much prefer to read text or CSV files, they're simpler to work with and less error prone. I created a VB script for the purpose of converting Excel to CSV.

Querying the Repository

Here are some SQL scripts you can use to query the repository. Most of these I got from an Informatica employee, others I put together. Use them as a base to create your own queries or reports.

One word of caution, use a profile with READ ONLY permission to access the repository and NEVER update the repository using SQL unless advised to do so by Informatica support.

Using pmrep to import an Informatica folder

Here’s the companion script for the “Using pmrep to export an Informatica folder” article. Use this script to import objects into PowerCenter.

One use for scripting an import would be to schedule it during off hours, for example moving development code to production during off hours. You may also need to export some objects, then do a "mass change" or Find/Replace on the XML file, then export them back in (for example, export all sessions, find/replace a connection).