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. Also please see these two Informatica KB articles:
Use ODBC Connections instead of native Informatica SQL Server Connections
When the DIH was installed it created Informatica connections called DX_ODS and DX_REPO. The DX_ETL workflow uses these connections. Change the connections to use ODBC versions.

On the Informatica Server, create ODBC Data Sources for DX_ODS and DX_REPO. Create a corresponding Microsoft SQL Server DSN for each.

Change the PowerCenter Connections DX_ODS and DX_REPO to use their corresponding DSN.

Edit the Windows Registry
Use RegEdit to edit HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\datasource. Add the string value pair WorkArounds (or WorkArounds2) with a value of 2.

Edit the m_SET_CURRENT_CUTOFF mapping.
Change these properties in the SQL1 SQL Transformation (as noted in the following images):
  • On the SQL Settings tab change the Database Type from Microsoft SQL Server to ODBC
  • Change SQL Ports
    • LAST_CUTOFF_END – From Date (or DateTime) to varchar
    • CUTOFF_END – From Date (or DateTime) to TimeStame

If using versioning check in the code. Refresh and validate the DX_ETL workflow.
Run the workflow.

Thank you for your support
 or  PayPal

IMPORTANT,  YOU are responsible for backing up your systems before trying or implementing any suggestions from this blog. I do not guarantee 100% accuracy of any code examples. I do not presume to know your system environment(s) or Security requirements; all code examples from this blog should be thoroughly tested before any attempted use on a production system.

0 Response to "Data Integraton Hub DX_ETL DateTime Precision Issue"

Post a Comment