Load delimited file but from a certain row on, input is garbled

Problem

You load a delimited file but from a certain row on, loaded input is garbled i. e. there seems to be an offset. Possibly this also leads to an crash of the session.

Cause

You probably have at least one text field that contains a line break as its content. The content is within quotes but none the less it does not work. By default (at least up to 9.6) PowerCenter interpretes a line break as the break for a new record no matter what the quoting is.

Solution

There is a custom property called

MatchQuotesPastEndOfLine

If set to

yes

it makes PowerCenter respect quoting of text fields over linebreaks.
You can activate this property either on session level

or as Integration Service property for all sessions at once. For the time being I do not have permissions to the admin console so I cannot provide a screen shot but we did that at my last employer's project.

Sources

• Importing Flat Files with Newline Characters in Informatica
• How to ignore new line characters fom the string while loading to csv file?? 

Workflow fails to start with "Error in expanding variable parameter"

Symptom

When trying to start a workflow in the workflow manager, you get in the integration service pane at the bottom:

Error in expanding variable parameter Please check the Integration Service log for more information.

Cause

You have a parameter file and tried to do sort of a self assignment like

$PMSourceFileDir=$PMSourceFileDir/some/path

Cure

Programming language usually support such things but PowerCenter parameter do not. Create a work around.

A mapping did not load any data containing an SQL override in a source qualifier using a mapping variable as filter

First, I know the setup is cheap. This project has the segregation of environments for DEV, INT and TEST made by differently named workflows in the same folder of the same repository... and we use mapping variables to store the highest ID having been loaded and to detect the records still to be loaded. Not obvious to me, the value of the mapping variable stored the repository is not saved per mapping but at most per folder maybe even per repository. I did not test. Being aware of that, it explains the problem described in title. The "failing" mapping did not load anything because it is the INT mapping using the stored value of the variable stored by DEV/TEST. So, if the source is the same (and it is) you have loaded everything already in DEV/TEST and the detection of records to load fails miserably.

Things learned

• Do not be too cheap
• Do not use mapping variables to detect whether rows have been loaded already or not. (This is not necessarily restartable anyway.)

Rank Transformation is spezial filter (I want the top BLUPP of the groups BLÖRTZ of the data)

A colleague of mine asked me about the working of the rank and I had to admit I did not know much about it. I just did not use it and might get using it more often instead of some more or less complicated sort, aggregate and filter conjunction. However, we tried it out a bit. Here our findings

• It is not just a ranking thing but it almost always filters!!! Number of Ranks property controls this. From each defined group, it returns as many rows as it is defined here. Again, rows not ranks as the name suggests. I.e. if you set it to 1 (well, this is the default) and in one group there are several records with rank 1, you will still get only one of those, probably just the first one happening to get identified as rank 1. To, hopefully, switch off filtering, set the number of ranks to its maximum value (for version 9.1: 2147483647)
• You control the grouping by the ticks in the Group by column of the ports tab. You can group over several ports.
• Obviously, you need to have a sort criterion. This is controlled by the R column of the ports tab. Surprisingly, you can rank over one port only. That is, if you need to sort over several ports, you'll have to compound/concatenate them beforehand into a sorting/ranking port.
• The Top/Bottom property tells the sorting order, i. e. Top means descending, Bottom means ascending.

Automagic Port Insertion into Expression (if I only knew earlier)

I just discovered that PowerCenter inserts a missing port into an expression by itself.


I had this expression where I had to calculate a big number of out ports. So I drag-and-dropped (dnded ;-) ) them to the expression and made them out ports only. After that I dnded the source ports to the expression but I did not get them all as it turned out.



After that I copied the calculations from my specification document into the expression field of the out port.



And the missing port was there as in port only.



It's magic, it's cool, I'm excited. I will see if I can remember and make use of this features.

ORA-01007 in PowerCenter though not in TOAD or the like

I recently got ORA-01007 after adding and forwarding ports of sources. I took the offending SQL from the session log and ran it in TOAD where it returned without errors.


Cause: I missed that the source qualifier used SQL override, so I did not adapt it.


Solution: I added the new ports to the attribute list of the SQL override and the error was gone.


N.b.: http://datawarehouse.ittoolbox.com/groups/technical-functional/informatica-l/ora01007-variable-not-in-select-list-1451528 says even that this error occurs when you do not strictly follow the order of the ports in the SQL override attribute list.

Worklet task instance [XYZ]: Could not acquire the execute lock for Worklet [xyz] [].

I just got this error. The Workflow Monitor showed the worklet in question as being succeeded.

Solution

Stop the worklet explicitly by the use of the context menu.
 It does not seem to be necessary to remove the lock in the Administration Console as suggested by https://kb.informatica.com/solution/5/Pages/18244.aspx

Run-time validation error: Joiner Transformation has an invalid join condition: not all of the ports could be found.

Symptom

The session fails in its initialisation. You have a very short log and somewhere therein is an entry saying:

TM_6279 The session instance [xyz] encountered the following run-time validation error: [Joiner Transformation  has an invalid join condition: not all of the ports could be found.
Joiner Transformation  has an invalid join condition: not all of the ports could be found.

(Possible) Cause

I was changing the name of the ports to the joiner being used in the join condition by the propagation feature.


The port name changed. The name of the port used in the join condition changed. The mapping was valid. Everything looked fine - in the mapping. However, this change was not propagated to the session.

Solution

It seems one cannot solve this issue on session level. I tried to refresh the mapping, exported and imported the enclosing worklet to NO avail.
All I had to do, was to change the name of the affected port in into something else confirm the change (I closed the joiner dialog, maybe pressing apply also works, I did not try) and change it back to the desired name.

I am not sure whether I did a mapping refresh on session level, however it might be worth just for good measure.