How to read metadata in Informatica Cloud (IICS)?

Yes, we can read the metadata information in Informatica Intelligent Cloud Services.

Unlike PowerCenter, all the information in Informatica Cloud is stored on the server maintained by the Informatica and the user does not have access to the repository database. Hence, it is not possible to use any SQL query to retrieve the information like in Informatica Powercenter.

It may not be possible to query metadata tables but we can make use of the Informatica Cloud capabilities to fetch the required information from metadata.

Though we can retrieve certain metadata information using Informatica Rest API, it is limited and requires knowledge on APIs.

So let’s discuss the ETL way of handling the issue.

Introduction

The advantage of IICS is that it provides functionality to export a set of Mapping Configuration tasks as a single zip file. The metadata here is stored in the form of JSON format.

The idea here is to retrieve the JSON files from the exported zip using a script and read them through a mapping and fetch the desired metadata information in text or CSV format.

It’s going to require a onetime set up to build script and mappings to achieve this and I will guide you through the process.

Once the initial setup is done, it can act as an Accelerator which helps you retrieve the metadata information anytime by just proving the zip file exported from IICS as an input.

There by it helps an Auto Review tool.

So let’s dive in, shall we?

1. Export the data from IICS

First thing first, select all the Mapping Configuration tasks you want to read the metadata from and export them as a single file.

Exporting Mapping task fetches the associated mapping also.

Make sure you select the check box as shown below to include all dependent assets.

Next Click on MyImport/Export Logs from the left pane. Go to Export Tab. Find the name with which you exported the code. Click download.

The entire tasks and its dependencies are downloaded as a single zip file.

In our example the file name will be IICS_Demo_Export.zip

2. Understanding the contents of exported zip file from IICS

Before building our automation project, we need to understand the contents of the exported zip file.

After unzipping the exported zip file, folders and files are available inside as shown below.

2.1. Understanding ContentsofExportPackage CSV file

The CSV file contains the information of all the objects exported from Informatica Cloud.

As you can see it provides the information of all the Mapping Configuration Task, Mappings, Secure Agent group, Project and connection details

2.2. Understanding contents of Explore Folder

Let’s get into the Explore folder.

There you can see the folder with the Project name from which the MCTs are exported.

If the MCTs you exported are from different project folders, you will find multiple folders here with same name. If you have created a project inside a project, similar folder structure will be found here, a folder inside a folder.

If you are exporting contents form the default folder of IICS, the folder will be as shown below.

Inside the Default folder you will find a zipped folder for each Mapping configuration task and its associated mapping that’s exported from IICS.

The Mapping Configuration tasks are represented with ‘MTT’ and mappings with ‘DTEMPLATE’ extension.

2.3. Understanding the contents of MTT folder

Unzipping the MTT zip file extracts two files. The JSON file show below contains the Mapping Configuration task metadata.

2.4. Understanding the contents of DTEMPLATE folder

Unzipping the MTT zip file extracts a folder named bin and three files.

There are two files present under the bin folder as shown below. The @3.bin contains the entire mapping metadata in JSON format.

The JSON files mentioned here are used to read the metadata of MCTs and mappings that are exported from IICS.

3. Understanding how JSON files are processed in Informatica Cloud

In order to read JSON files in IICS, Hierarchy Parser transformation is used.

Let’s understand quickly how Hierarchy Parser transformation works.

In order to parse data through Hierarchy Parser transformation, Hierarchical Schema need to be defined first. Hierarchical schema is an asset that is based on a schema file or sample JSON file (collected from the exported zip) that you can import into Data Integration.

The input to the Hierarchy parser transformation is a flat file containing the list of JSON files along with the directory path.

In order to prepare the input file for Hierarchy Schema, we need a script that performs multiple but simple actions.

4. Building a script

There are few things that needs to be kept in mind before building the script.

• The script should extract all the JSON files from each zip file and place them in a separate folder for the mapping to easily read them.
• All the MCT JSON files are with name mtTask.json and Mapping JSON files are with name @3.bin
• In order to avoid confusion, the script while fetching the JSON files into a separate location should rename them to the MCT/Mapping name i.e. the folder from which the file is extracted.
• The script should also create a separate flat file for MCT and Mapping with list of JSON file names along with directory path. This acts as a source to the Hierarchy Parser transformation as discussed in the above section.

Depending on the platform on which you are working choose a Scripting language (Shell scripting/Power shell/Python) to develop a script.

The script functionalities should be

1. Identifying the zip file exported from IICS and extract its contents into a temp folder.
2. Identify the MTT/DETEMPLATE zip files and making a list of files separately.
3. Loop through the MTT file list
4. unzip each MTT file
5. rename the mtTask.json to <mappingtaskname>.json (which is the zip file name).
6. write the JSON file name along with directory location to a flatfile.
7. Loop through the DTEMPLATE list
8. unzip each DTEMPLATE file
9. rename the @3.bin to <mappingname>.json (which is the zip file name).
10. write the JSON file name along with directory location to an another flatfile.

At the end of the script we have 2 flat files one containing the list of Mapping task JSON files and the other containing the Mapping JSON files along with location.

The contents of the input file of the mapping to read MCT metadata will be as shown below

The contents of the input file of the mapping to read Mapping metadata will be as shown below

Note: You should have IICS flatfile connection to the path specified to read the input file in mapping.

5. Creating Hierarchical Schemas

Create a separate Hierarchical Schemas for Mapping task and Mapping using any one of the JSON files collected.

Just make sure the mapping you use a template must cover all the scenarios you expect.

For example, in all your Mapping configuration tasks you use some Advanced session properties like Error Directory, Error Filename and Error File type.

But in one of the MCT you used an addition property Stop on Errors.

But if you use the JSON of the MCT where Stop on Errors is not used as a template for Hierarchy Schema, the mapping will never read the data of Stop on Errors though the property is used in MCT.

Tip: You can check and add all the additional properties by editing the JSON file you wanted to use a template.

6. Creating Mappings

Create separate mappings to read Mapping Configuration task and Mapping metadata.

The mappings will read the flat file with JSON filenames as input and passes the information to Hierarchy Parser transformation.

The transformation converts the hierarchical JSON data of each file into relational output.

Separate output files will be created for each mapping in csv format one containing the MCT metadata and the other Mapping metadata.

Create a mapping task for each Mapping. You can find the steps to create Mapping here

• Steps to create a mapping to read Mapping Configuration task metadata in IICS
• Steps to create a mapping to read Mapping metadata in IICS

7. List of metadata information that can be read from IICS Mapping configuration task

Let’s see what metadata details can be read form Mapping Configuration task JSON file.

• Mapping Task name
• Description
• RuntimeEnvironment
• Mapping_Name
• Maximum Number of Log Files
• Execution Mode
• lastruntime
• Advance session properties like
• error_log_file_directory
• error_log_file_name
• stop_on_errors
• error_log_type..etc
• enablecrossschemapushdown
• parameter directoryname/filename
• PostProcessingCommand
• PreProcessingCommand
• Schedule_Name
• Source Filter
• Parameter values defined in Mapping

With a proper schema definition, we can read almost every detail from the Mapping Configuration task.

8. List of metadata information that can be read from IICS Mapping

Here are few metadata details which can be read from Mapping JSON:

• Mapping Name
• Mapping_Description
• Transformation Name
• Transformation Description
• Tracing_Level
• Connection Name
• Source Type
• object_name
• Source specific Properties (varies on the type of connection)
• Source_CutomQuery
• SourceFilterCondition
• descending
• flatfilecommand
• queryall
• rowlimit
• selectdistinct..etc
• Lookup specific Properties
• Connected/Unconnected
• Lookup SQL Override
• Lookup Source Filter
• Lookup condition
• Multiplematchpolicy
• returnportname..etc
• Target specific properties (varies on the type of connection)
• truncate
• Pre_SQL
• Post_SQL
• handlespecialchars
• setfieldstonull
• Forward_Rejected_Rows
• fieldmappingmode..etc
• Few Salesforce speicfic Properties
• harddelete
• monitorbulk
• pkchunkingenabled
• pkchunkingsize
• pkchunkingstartrowid
• serialmode
• usebulkapi
• batchsize
• useerrorfile
• usesuccessfile
• createtarget
• SOQLFilterCondition
• Include_Archived_Deleted_Rows
• Enable_Target_Bulk_Load

9. Create a Linear taskflow to execute everything in an order

In short the process flow will be as below

1. Place the exported zip file from IICS in the source path specified in Script.
2. Run the script to extract the JSON files from zip file and prepare 2 flat file inputs.
3. Trigger the Mapping task which reads mapping task JSON files.
4. Trigger the Mapping task which reads mapping JSON files.

The script can be called from Pre Processing Command of the Initial mapping. Create a Linear taskflow and add the mapping Configurations built.

Conclusion & Course Details

If you are with me until here, you can understand it’s not a straight forward way like in Powercenter to read the metadata by simply firing a SQL query. But once you have done the setup it’s as easy as just triggering a taskflow. All you need to do is follow the steps and build a Script and couple of mappings.

It adds a real value by acting as an Auto Review tool. Every new code you build, you can review all the set of mappings using this tool rather than opening and reviewing them manually.

We have a complete 2 hour video course on Udemy explaining how you can build this tool from scratch. Along with video guided course you will also get instructor support in case you need any help in building the tool at each and every step with a life time access.

The enrollment link to the course:
Informatica Cloud Data Integration – Automation Project

Start building your first Automation Accelerator in Informatica Cloud.