Working With Assignment Task In Informatica Jobs

Hi!

I have a strange bug(?) in workflow. It used to work fine in version 9.6.1, but afler upgrade up to 10.1.1 we have a problem.

In workflow we have an assignment of the type:

$$WF_VAR_INCNO = REPLACESTR(1, $$WF_VAR_INCNO, $PMWorkflowRunId, '') in Assignment task.

It works not properly. Instead of replacing part of the variable $$WF_VAR_INCNO that equals $PMWorkflowRunId to '' it replaces all zeros in this variable to ''.

Severity Timestamp Node Thread Message Code Message

INFO 12.01.2018 14:04:03 node01_OS-2299 7852 LM_44206 Workflow WF_TW_FOCUS_WFLOW_CONVERSION started with run id [4773371], run instance name [], run type [Concurrent Run Disabled].

...

Severity Timestamp Node Thread Message Code Message

INFO 12.01.2018 14:04:33 node01_OS-2299 DIRECTOR TM_6962 The variable [$$WF_VAR_INCNO] will be assigned from [$$VAR_INCNO]

INFO 12.01.2018 14:04:33 node01_OS-2299 DIRECTOR VAR_27048 Persisting mapping variable values to the repository.

INFO 12.01.2018 14:04:33 node01_OS-2299 DIRECTOR VAR_27049 Mapping variable name: [$$VAR_INCNO], persisted value: [4773371503043], run instance name [].

...

Severity Timestamp Node Thread Message Code Message

INFO 12.01.2018 14:04:36 node01_OS-2299 7872 LM_36568 Assignment task instance [FOCUS:WF_TW_FOCUS_WFLOW_CONVERSION[version 1].s_02_Assignment_SET_INCNO[version 1]]: Executing assignment [$$WF_VAR_INCNO = REPLACESTR(1, $$WF_VAR_INCNO, $PMWorkflowRunId, '')]

INFO 12.01.2018 14:04:36 node01_OS-2299 7872 LM_36379 Workflow [WF_TW_FOCUS_WFLOW_CONVERSION].  Variable [$$WF_VAR_INCNO], Value [47733715343].

 

So it looks like in assignment we have $PMWorkflowRunId = 0. Why?

(Read the previous trick, Rolling Sums & Averages Grouped By Key)

Informatica provides both a Web Service Consumer transformation and an HTTP transformation that can access a remote webpage and return the information provided (these days, usually in the form of JSON files). When the web service requires input parameters, Informatica passes them through with standard GET method (e.g. “).

Unfortunately, RESTful web services do not use standard GET parameters. Instead, a RESTful web service has a URI, i.e. a regular URL followed by a URN that identifies a particular resource, and which acts as an input parameter of sorts (e.g. “”). Informatica currently has no way of assembling the URI on the fly; however, this limitation can be overcome. This will explain how.

The Challenge

In a recent Smartbridge project, this issue arose when our client wanted to obtain information about their business from an external independent source that offered the information through a RESTful web service. The URN had two parts: a start date and an end date (plus a few other identifiers not relevant to this post).

Due to business requirements and the nature of the data being served, it was decided that we wanted the ETL to fully refresh the last week’s worth of data daily, and perform a check for new records (but not perform updates) for the entirety of the last month. This being JSON files, we would have to feed the output from the web service into a Java transformation for processing, and therefore the Java memory stack was also a concern (but that is a blog post for another day). Bottom line, we wanted to make sure we could slice and dice the dates depending on the circumstances.

The PowerCenter Approach

Ultimately, the best approach is to use the HTTP GET transformation with a $$VariableURL already crafted for the variable values needed. You will be required to provide at least one parameter, but if you feed it a NULL value, the resulting URL will be valid. Remember that PowerCenter does not update mapping variables’ values until session end, so the $$VariableURL needs to be set to the needed value before the session in invoked – therefore, it must be a Workflow variable, or a parameter file variable. We decided on a Workflow variable, but the approach will work with a mapping parameter file generated by a previous session of the workflow.

The Set-Up

1. Create a $$VariableURL_wf workflow variable. You can give it an initial value if you want, but it will not be used. We placed the base URL there for safekeeping.

3. In the workflow, precede each session that will use the web service with an Assignment Task that generates the correct URL for that session and assigns it to $$VariableURL_wf. As mentioned above, in our case the variability of the URI was exclusively time-dependent. If your case relies instead on values read from a data source, I strongly recommend using a parameter file instead.

The resulting variable will be something like:
https://www.RESTful_WebService.com/api/rest/1.1/etl/2015-12-05T00:00:00/2015-12-12T00:00:00

4. Assign the $$VariableURL_wf to $$VariableURL_m in the session, so that it is available in the mapping

5. In the mapping(s), have the HTTP transformation use the $$VariableURL_m

The Solution

By controlling the variable at the workflow level, this approach does lose much of the flexibility you would get from a regular HTTP GET transformation (where you can alter the variable at mapping runtime), but will nevertheless get the job done. It is possible that Informatica will soon patch the HTTP transformation to account for RESTful web services, but if so, it is likely at this point it will only happen in version 10.

On the other hand, giving each variable value its own mapping creates an enforced parallelism to the HTTP calls that can (and in our case definitely did) increase the throughput. It was so successful, in fact, we parallelized several other, more traditional, HTTP GET calls, and saw a significant increase in our overall workflow speed.

One thought on “Working With Assignment Task In Informatica Jobs

Leave a Reply

Your email address will not be published. Required fields are marked *