Command as source backslash issue

I tried reading source via command and came across an issue. The command tested in bash shell worked fine. In Command Task it was working fine as well. However, when put into the Source Qualifier as Command property on a session, it kept crushing.

Looking at the logs I've discovered, that the command is altered. Original command:

ll ./* | awk -F\ '{if ( NF==1 ) title=$1} else if ( NF>2 ) print title ","$NF"," strftime("%Y"), $6, $7, $8}'

Fetched from log:

ll ./* | awk -F/ '{if ( NF==1 ) title=$1} else if ( NF>2 ) print title ","$NF"," strftime("%Y"), $6, $7, $8}'

Never found any solution. Following the advice found at Informatica KB I've created a script to invoke the command indirectly.


KB entry says: This is a known issue and a CR 108604 has been submitted to be addressed in the future release of PowerCenter. Last Modified Date:8/2/2008 8:05 PMID:1947, - seems it's over 10 years old... Not sure if it's going to be fixed anytime soon. It refers PowerCenter 8.1 and still exists in 10.2

As found in this KB article, there is a custom property available:

SkipUnixToNTCmdPathConversion = yes

Setting this to "yes" stops the automatic backslash to forwardslash conversion.

Removing whitespaces from XML source

There are a few ways to remove whitespaces from XML source.

• obviously, you can use LTRIM / RTRIM in an expression
• you can apply normalize-space ( string ) XPath Query Function

However, if it's necessary to apply this across all the ports, it's possible to use a custom property on a session:

• XMLStripWhitespace=Yes;

And the other way around:

if for some reason the whitespaces are gone but it actually is needed to preserve them, the above mentioned settings need to be reviewed. If there is no trace of a whitespace removal function anywhere, it's possible that it's been set on Integration Service as a default. In such case, for a given session, the whitespaces may be preserved by setting the custom property to the opposite value, overriding the default:

• XMLStripWhitespace=No;

Removing hash from output header? Easy!

When creating Flat Files as output from a mapping, it's very easy to add a header to the file - all you need to do is just check the property on the target definition.

However, when you look at the file created, you'll notice that the header starts with a hash:

There is no property to enable or disable this... or is there?

RemoveOutputHeaderHash=Yes



More:

These properties are undocumented after searching a lot I found details for few of them and sharing here the same:

Custom Properties Name: ServerPort
Custom Properties Value: any available server port number
Details: This custom property configures Informatica
PowerCenter Services to listen on that port.


Custom Properties Name: overrideMpltVarWithMapVar
Custom Properties Value: Yes
Details: This enables Informatica to evaluate
parameters within mapplets.


Custom Properties Name: DisableDB2BulkMode
Custom Properties Value: Yes
Details:This is needed when your DW is on a
DB2/390 or a DB2 UDB database.

Custom Properties Name: WriteNullXMLFile
Custom Properties Value: No
Details: This property skips creating an XML file when the XML Generator transformation or Target doesn’t receive data


Custom Properties Name: SuppressNilContentMethod
Custom Properties Value: ByTree
Details: This property will suppress the parent tags as well as the child tags when all the child elements are null. 


Custom Properties Name: XMLWarnDupRows 
Custom Properties Value: No
Details: When set as No, IS doesn't write duplicate row warnings and duplicate rows for XML targets to the session log.

Custom Properties Name: XMLSendChildFirst
Custom Properties Value: Yes
Details: This is used to reduce the cache file size created by XML target and increase the performance of reading large XML files.


Custom Properties Name: FullCBLOSupport
Custom Properties Value: Yes 
Details: This is used to enable complete constraint based loading.

Repository Custom Properties

Custom properties

overrideMpltVarWithMapVar=Yes
Makes mapping variables and parameters visible inside mapplets automatically.

UseLogFile=Yes
Makes the txt log files available

LogParameterOverrideValue=Yes
Makes variable assignment during session initialization visible in the log files (KB 303702)

SkipUnixToNTCmdPathConversion=yes
Stops backslash to forwardslsh conversion for Source commands (KB)

JVMMinMemory/JVMMaxMemory=XXXM
Memory assignment to session java virtual machine. Helps solving the Java Heep Size (OutOfMemoryError)

ObjectImport - [object] is popotentially unsafe and cannot be imported

If pmrep objectimport stops with the following error:

The [object]: [objectName] is potentially unsafe and cannot be imported.

Try running import with [-s] switch, e.g.:

pmrep objectimport -s -i xmlFileName -c controlFileName

FR_3085 ERROR: character is a null character, which is not allowed in a text input file

As mentioned on https://kb.informatica.com/solution/6/Pages/20698.aspx:

Solution

To avoid this error set one of the following PowerCenter Integration Service custom properties (for detailed steps refer to article 18015: HOW TO: Set the PowerCenter service Custom Properties): FileRdrTruncateStringNull FileRdrTreatNullCharAs These parameters can be used to deal with invalid null characters in the source flat file as described below. NOTE: These parameters require PowerCenter 8.1.1 SP3 (or later). VSAM These parameters should only be used with ASCII (non-EBCDIC) flat file sources. If the file is a mainframe VSAM file confirm the following in addition to the above: The file is not converted to text before reading in PowerCenter. The source definition type is VSAM. If it is flat file this error may occur. Use a Normalizer in the mapping. Change the source code page to EBCDIC .

More Information

FileRdrTruncateStringNull and FileRdrTreatNullCharAs The FileRdrTruncateStringNull and FileRdrTreatNullCharAs PowerCenter Integration Service custom properties can be used to handle Null (binary) values in flat file sources. If no flags are used then a row error is generated and row having null character is skipped from processing. These flags cannot be used simultaneously. FileRdrTruncateStringNull If FileRdrTruncateStringNull is set to Yes, then column will be truncated at the first NULL character. FileRdrTreatNullCharAs If FileRdrTreatNullCharAs is set to a character, then the NULLs will be replaced with the character specified. To set FileRdrTreatNullCharAs as a non-alphanumeric ('special') character (such as a space) use the octal representation character (such as \040 for space), and NULL will be replaced with the specified character. Example The octal representation for a space character is /040. Set this parameter as follows so that nulls are replaced with spaces in the flat file: FileRdrTreatNullCharAs=\040 NOTES FileRdrTreatNullCharAs may cause a session to fail in PowerCenter 8.5.1 and 8.6.  PowerCenter 8.6 HotFix 3 or later is recommended.  For more information refer to article 33016. If FileRdrTreatNullCharAs an error message will be written to the session log. For more information refer to article 31587 HEX OO Values If the file contains NULL characters with HEX value of 00 you can remove these characters using a Hex Editor. Use the replace option to replace null(00) with space or even nothing. Use the following link to download Hex Editor XVI32 http://www.chmaas.handshake.de/delphi/freeware/xvi32/xvi32.htm

CurrentlyProcessedFileName not showing up

CurrentlyProcessedFileName not showing up

So, you've selected the 'Add Currently Processed Flat File Name Port" property, applied the changes and closed the window - and the port did not show up? Me too.

I opened up the Source Definition in mapping again and verified that the property is checked. Unchecked it and - surprise: I got a message saying that CurrentlyProcessedFileName is not present to be deleted!

Checked it again, clicked "Apply' and switched to "Ports" tab WITHOUT CLOSING the Edit Transformations window and voile! It's there! I clicked "OK" to close the window and... It's gone again!

If you can repeat the above and have some observations - please share by leaving a comment.

Solution... well - a workaround rather

I was not able to find the cause and solve it. However editing the Source Definition in Source Analyzer and checking this property (instead of overriding in Mapping Designer) made the CurrentlyProcessedFileName visible and available for processing.