Skip to main content

Informatica importer

Coming soon

The Informatica importer is about to be released as open source (Apache License 2.0). Until then, we run it for you: contact us.

The Informatica importer converts PowerCenter mappings and Informatica Cloud (IICS) tasks into Apache Hop pipelines, translates Informatica expressions, and writes a migration report that classifies every transformation and checks whether each generated pipeline can run as converted. It will be open source (Apache License 2.0), and runs from the Hop GUI, from hop-import, or as a standalone command line.

PartWhat it doesWhere it runs
Hop pluginAdds File → Import from Informatica… to Hop GUI and -t informatica to hop-importInside Apache Hop 2.19.0 or later
Command lineThe same import as a single jar, for scripts and CIAny machine with Java 21
Migration reportinformatica-migration-report.md and conversion-metadata.json in the output folderWritten by every import

What it reads​

FormatWhat it reads
PowerCenterRepository exports (POWERMART → REPOSITORY → FOLDER), with every mapping in a folder, and single-mapping XML files. Files ending in .xml are read, and so are files without an extension that start as XML
IICSSynchronization (dssTask.json) and replication (drsTask.json) tasks, with their connection files from SYS

PowerCenter workflows become Hop workflows: a Session runs the pipeline of its mapping, an Email task becomes a Mail action and a Command task a Shell action. Worklets, parameter files and schedules are not converted.

How it works​

  1. The importer walks the input folder for PowerCenter XML files and IICS task folders.
  2. For each mapping, it reads the transformations, their ports and the connectors between ports, and resolves the source and target definitions the mapping's instances name in the folder.
  3. Each transformation becomes one or more Hop transforms; field-level connectors become transform-level hops. Each target becomes a Table output, or a Text file output for a flat file, writing only the ports connected to it.
  4. A Source Qualifier without a SQL override gets the query PowerCenter would build: its connected ports, the source tables, the User Defined Join and the Source Filter.
  5. Expressions are translated: into a Formula where Hop's Formula computes exactly what PowerCenter does, into a User Defined Java Class otherwise. Filter conditions, Router groups and Aggregator outputs go through the same translation.
  6. Each generated pipeline is checked as a whole: a placeholder on the data path, or a transform with no hops, makes the pipeline non-functional as converted in the report.
  7. For IICS tasks, Hop connections are created from the task's connection files, without credentials. PowerCenter mappings refer to connections by name (for example INFA_SOURCE_DB and INFA_TARGET_DB), which you create in Hop.

What you get​

In the output folder:

  • pipelines/<mapping>.hpl: one pipeline per mapping or task;
  • metadata/: the local run configurations, and the Hop connections of IICS tasks;
  • project-config.json: so the folder opens as a Hop project;
  • informatica-migration-report.md: see The migration report;
  • conversion-metadata.json: the same figures, as JSON, for your own tooling.

Design choices worth knowing​

  • Exact, or Java. Hop's Formula follows spreadsheet rules (a blank number is 0, text compares without case), so an expression only becomes a Formula where those rules give PowerCenter's answer. Everything else becomes Java that calls a small runtime with PowerCenter's semantics: NULL handling, IIF, DECODE, date formats, ERROR() skipping a row, ABORT() stopping the run. Each statement has the original expression above it as a comment, so you review it side by side with the mapping.
  • Filters translate exactly, or not at all. A Filter becomes Filter rows when its condition maps onto Hop's condition exactly, and a Java filter otherwise. A condition the importer cannot parse stays a placeholder with the condition in its description, rather than a filter that keeps the wrong rows.
  • Mapplets are expanded. Each Mapplet instance becomes a copy of the Mapplet's transformations in the pipeline, with fields of its own, so a Mapplet used twice gives two independent results. Reusable transformations are copied in the same way.
  • Unconnected Lookups are calls. :LKP.lkp_NAME(…) in an expression becomes a query the Java class runs on the Lookup's connection.
  • Parameters become variables. Mapping parameters ($$NAME) in SQL become ${NAME}, and $PMTargetFileDir or $Param_… in file names become Hop variables of the same name.
  • Credentials are never carried across. Generated connections have empty user names and passwords; you add them in Hop, through variables.
  • Update Strategy is done by the target. Hop has no transform that decides insert, update or delete per row; the Update Strategy becomes a pass-through, and the target is generated for the operation. A per-row strategy expression is flagged.
  • Two checks, not one. A transformation's status says what it became in the generated pipeline; the structural check says whether the pipeline as a whole holds together. Neither says it runs: see the limits below.

What it does not do​

  • PowerExchange targets are written as fixed-width text files and flagged for manual work; PowerCenter wrote them through PowerExchange. PowerExchange (VSAM) sources are read as fixed-width files, with COBOL numbers decoded.
  • Workflows: link conditions other than a task's success or failure, and task types such as Decision or Timer, are reported for manual work. Worklets, parameter files and schedules are not converted.
  • Transformation types without a converter (see the transformation mapping) become placeholders.
  • It does not validate the result. Do that with a parallel run: a mistranslated expression gives a pipeline that runs and computes the wrong answer.
  • SQL overrides run as written. Oracle syntax in an override ((+) outer joins, ^=) stays Oracle syntax, which is right as long as the database is still Oracle.
Current limits

Canvas positions are not carried across: transforms are laid out left to right by data flow.

What we tested it on​

The test corpus is the PowerCenter estate the US Department of Health and Human Services published at github.com/HHS/Informatica under The Unlicense (public domain): 108 mappings and 11 workflows in 11 repository exports. It is one organisation's federal HR and payroll estate, so it is broad but not a random sample. It is not your project; the migration report of your own export gives your figures.

ResultWhat was measured
Converted automatically100% of mappings (108 of 108) convert with nothing missing on the data pathThe report's structural check of each pipeline. 28 (26%) need no review at all and 80 (74%) carry review flags (see below); 0 cannot run as converted. The 11 workflows convert to Hop workflows
Runs end to end96% of the mappings that could be set up run to the end (98 of 102)PowerCenterCorpusExecutionTest rebuilds each export's tables in H2 in Oracle mode, writes five rows per source and runs the pipelines unchanged; 94% of the mappings (102 of 108) could be set up that way. The 4 that stop do so on the test database: Oracle SQL that H2 does not accept, or test strings put into numeric columns
Same result as the originalNot measuredThat needs PowerCenter to run the original mappings, and the tests run without a PowerCenter installation

Read the runs figure first, not the review count. "Need review" is by design: every Lookup, Filter, Joiner and Aggregator, and every Expression translated to Java, is flagged for a person to check however it converted, so on this estate only the simplest mappings are functional without review. Running to the end shows a pipeline is complete; it does not show it computes what PowerCenter computes. That is what a parallel run is for.

Where to go next​