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.

Using lookup input ports in SQL Override for non-cached lookup

If you need to use lookup input ports in the SQL Overrride for non-cached lookup, you need to use ?name? notation, eg.:

SELECT LAST_NAME FROM EMPLOYEES WHERE EMP_CODE = ?in_emp_code?

Not a recognized ODBC scalar function option with OUTER JOIN in SQ

Getting a "Not a recognized ODBC scalar function option" error while trying to perform an OUTER JOIN on Source Qualifier transformation using User Defined Join property? Make sure you're not running the process with auto-generated SQL Query Override after using the {} (curly braces) syntax in User Defined Join :)


Worth reading:
Outer joins in MS SQL
Execution of ODBC Extensions to SQL

MS SQL Server - Exception vs RaiseError

Below is just an observation that needs further investigation.

It appears that while using ODBC to connect to MS SQL Server there's a difference between RAISERROR and an occurence of an exception.

Performing a SELECT 1/0 within a stored procedure called as Post-SQL query results in an error like:

Database driver error...
...
FnName: Execute Direct -- [Informatica][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Divide by zero error encountered.]

Now, this is perfectly fine. An error occured, the session has failed. But what if we need to store some audit information in a log table in case of error in stored procedure? It's possible with TRY...CATCH. Whenever an error occurs, the CATCH block is executed. We can log the information and do a RAISERROR afterwards to fail the session.

But the session never fails. There's no track of RAISERROR in session log, the session completes successfully.

UPDATE:

The issue is caused with returning any dataset. Any print statement or result set seems to cover the error. Even the information about number of affected rows. Hence, the solution is to:

• remove any SELECT / PRINT statements from within the Stored Procedure
• suppressing the information about affected rows by using the SET NOCOUNT ON

The mapping is potentially unsafe and cannot be imported.

So, you got this error while trying to import your mapping to Powercenter:

The mapping m_your_mapping is potentially unsafe and cannot be imported.

What is wrong and how can it be fixed? What's the root cause? Hard to tell - the error message does not explain a lot.

The Cause
Most likely the XML file has been altered outside Powercenter. It's quite common to export and to some edits in XML before importing. Some renaming, replacing some paths or parameters. However some transformations or mappings have this

CRCVALUE="123123123"

property. If the XML file has been altered, it's very much possible the CRC is no longer correct.

The Solution
While importing the XML try to use the "-s" option. It can't be found in the Informatica Command Reference (at least in all versions I've tried) but it seems to work. Instead of the usual command:

pmrep objectimport -i /pathtoyourxmlfile/m_your_mapping.xml -c controlFile.txt - l output.log

try using

pmrep objectimport -s -i /pathtoyourxmlfile/m_your_mapping.xml -c controlFile.txt - l output.log

Remarks
This worked in my case. And I have no idea what the "-s" stands for and what actually happens here...

Hash in output header

When using Output Field Names as Header for a Flat File target, a hash symbol [#] is put as the first one in header.

And the result in the file looks like this:

To get rid of the hash sign, the following custom property has to be set:

MS SQL Server: Stored Procedure error not failing parent Workflow

Stored Procedure error not failing parent Workflow

There's an issue I'm investigating: I have a workflow with a stored procedure executed as a post-load. It fails without failing the parent workflow. I've decided to come up with a simple test that would narrow down the cause.

Stored procedure

Here's the stored procedure I'll use for testing:

CREATE PROCEDURE RaiseErrorTest AS
BEGIN
--PRINT 'started'
--SELECT 1
IF 1=1
BEGIN
RAISERROR ('The servers are busy at this time. Please try again later', 16, 1)
RETURN 1
END
PRINT 'Completed'
SELECT 2
END

All it does is perform the RAISERROR that should fail the whole process. There are also two disabled statements - we'll get to those later.

Workflow

I've created a simple workflow that executes SELECT 1 on source and uses a FALSE filter, so nothing gets written to target. It also includes a Post-load stored procedure call to the above SP:

Test 1: Native connector, disabled statements before raising error

Ok, lets start it and see what happens:

Great, session has failed as expected raising the defied error. The workflow has failed.

Test 2: Native connector, enabled statements before raising error

Now, let's enable the two statements back:

ALTER PROCEDURE RaiseErrorTest AS
BEGIN
PRINT 'started'
SELECT 1
IF 1=1
BEGIN
RAISERROR ('The servers are busy at this time. Please try again later', 16, 1)
RETURN 1
END
PRINT 'Completed'
SELECT 2
END

Test 3: ODBC, disabled statements before raising error

Let's revert the stored procedure to have disabled lines 3 & 4 and try the ODBC connection:

Workflow failed as expected - please note the difference in error message due to using ODBC instead of SQL Server Native Client

Test 4: ODBC, enabled statements before raising error

Now for the final test: using ODBC connection for the stored procedure that runs some statements before raising error:

Session (and worflow) executed successfully! Great, right? Well, not quite... The stored procedure fails but this is not escalated to PowerCenter.

Conclusion

While this is handled correctly by Native SQL Server connector, any first result set returned by the stored procedure fools the ODBC into treating this a successful execution. Whatever happens later on is discarded and you may never know your stored procedure failed. 

Unfortunatelly I have no idea how to overcome this issue.

Repository cleanup

The Idea

Inpired by a post on Stackoverflow I've decided to come up with some solution to find unused objects in PowerCenter Repository.

So, the idea is to find all unused objects in Repository, like for example sources and targets not used by any mapping or mappings not used by any session or sessions that do not exist in any workflows. Doing that manually would require a lot of work and even more time. Doing it using Reository Queries is highly complex - if feasible at all.

Therefore I've created a simple app that uses pmrep tool to find all objects and check their dependencies. It is still time consuming, but once configured requires no manual effort at all.

Download link is available at the bottom.

Setup

All you need to do to run the tool after downloading is edit the config file. The properties are quite simple

#Common parameters

[Common]

infaDir = C:\Informatica\9.0.1

Repository = RepositoryName

#Specify Domain OR Host and Port. By default Domain will be used if specified.

Domain = Domain_Name

Host = your.host

Port = 6005

Folder = InformaticaFolderName

User = UserName

#User security domain. By default Native is used.

UserSecurityDomain = 

#Object list - sample below 

#objectTypeList = mapplet, mapping, session, source, target, worklet

objectTypeList = mapplet, mapping, session, source, target, worklet 

The last one - objectTypeList - can contain any number of object types that should be checked. 

Please keep in mind that looking for all possible object types might be really time consuming!

Running

Once set up, you just need to run the executable. It will prompt for repository password (note: the password is not stored anywhere and must therefore be provided on each run).

During runtime the progress is indicated by listing all steps and the number of objects of each type. Here's a sample:

Finally you'll find a folder named UnusedObjectsReport with the output:

• ListOfUnusedObjects.txt - file containing list of all the unused objects found in the repository
• DeleteUnusedObjects.bat - prepared batch to remove all the unused objects. For safety reasons all the lines in the batch are commended with 'rem ' prefix. You should review and choose which objects to remove. Once executed, all the objects should be removed. Note: this works only with unversioned repositories. 

In addition there will be two files per each object type defined in the list, e.g.:

• mapping.txt - list of all the mappings found in the repository
• mapping_dep - list of all dependencies found for each mapping

But these are just temporary files not intended for any further use. Feel free to inspect them if you like.

The tool requires no installation - simply download, unzip, setup and run. 

It can be downloaded from the following download page.

Numeric identity ODBC error

Using Numeric Identity for MS SQL Server target causes ODBC error when trying to update rows.

While running the session you might get an error as follows:

FATAL ERROR : An unexpected condition occured in file [/export/home/builds/pc8x_root/910HF/build/powrmart/common/odl/msodbc/odbcdriver.cpp] line [495].

This reads: I'm sorry, but loading data via ODBC to Numeric Identity port type for MS SQL Server target port is not supported. Please disconnect the port.

Here is a sample mapping causing the error:

All you need to do is disconnect the port or change it's type.

An ETL Framework for Operational Metadata Logging

Below is a copy of a very nice article found on www.disoln.org.

Quite often there is requirement to track runtime information about your ETL jobs such as record count, error count, job run time etc... In general it is a non-functional requirement, required by the IT team to have such information for reconciliation purposes, calculate performance statistics etc... It is important to have a framework, which can capture all the operational meta data you need with out adding too much time to your development cycle.

Here lets talk about building a framework to capture Operational Metadata by leveraging the capabilities provided by Informatica PowerCenter.

Framework Components

Our Framework for Operational Metadata logging will include three components.

1. A Relational Table :- To store the metadata.
2. Pre/Post Session Command Task :- Command task to collect the metadata.
3. Reusable Session :- Session to log the metadata details into the relational table.

I. Relational Table

A relation table will be used to store the operational metadata with the structure as below. Data in this table will be retained for historical analysis. 

• ETL_JOB_NAME : ETL job name or Session name.
• ETL_RUN_DATE : ETL job execution date.
• SRC_TABLE : Source table used in the ETL job.
• TGT_TABLE : Target table used in the ETL job.
• ETL_START_TIME : ETL job execution start timestamp.
• ETL_END_TIME : ETL job execution end timestamp.
• SRC_RECORD_COUNT : Number of records read from source.
• INS_RECORD_COUNT : Number of records inserted into target.
• UPD_RECORD_COUNT : Number of records updated in target.
• ERR_RECORD_COUNT : Number of records error out in target.
• ETL_STATUS : ETL Job status, SUCCESS or FAILURE.
• ETL_CREATE_TIME : Record create timestamp.
• ETL_UPDATE_TIME : Record update timestamp.

II. Pre/Post Session Command Task

Pre/Post session command task will be used to generate a comma delimited file with session run details. This file will be stored into $PMSourceFileDir\ directory with a name $PMWorkflowName_stat.txt. 

Note :

• $PMSourceFileDir, $PMWorkflowName are the session parameter, which gives the source file directory and name of workflow.
• File name generated will always be <WorkflowName>_stat.txt

The comma delimited file will have the structure as below.

• ETL Start time
• ETL End time
• ETL Job name
• Source table name
• Target table name
• Source record count
• Records inserted count
• Records updated count
• Error record count
• ETL Job status

We will be using the built-in session parameters to collect session run details. 

• $PMSessionName : Name of the Informatica session.
• $PMSourceName@TableName : Name of the source table name.
• $PMTargetName@TableName : Name of the target table name.
• $PMSourceQualifierName@numAffectedRows : Number of records returned from source.
• $PMTargetName@numAffectedRows : Number of record inserted/updated into the target table.
• $PMTargetName@numRejectedRows : Number of records error out in target.

Note : SourceName, TargetName, SourceQualifierName will be replaced by corresponding transformation instance name used in the mapping.

Pre Session Command Task

Pre session command task will be used to create the file with the session start time stamp. 

echo %DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2%,
> $PMSourceFileDir\$PMWorkflowName_stat.txt

Post Session Success Command Task

Post session success command task will be used to append the file, which is created in the pre session command with session run details. This will capture the SUCCESS status along with other session run details.

echo %DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2%,$PMSessionName,
$PMSTG_CUSTOMER_MASTER@TableName,
$PMINS_CUSTOMER_MASTER@TableName,
$PMSQ_STG_CUSTOMER_MASTER@numAffectedRows,
$PMINS_CUSTOMER_MASTER@numAffectedRows,
$PMUPD_CUSTOMER_MASTER@numAffectedRows,
$PMINS_CUSTOMER_MASTER@numRejectedRows,
SUCCESS,
>> $PMSourceFileDir\$PMWorkflowName_stat.txt

Post Session Failure Command Task

Post session failure command task will be used to append the file, which is created in the pre session command with session run details. This will capture the FAILURE status along with other session run details.

echo %DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2%,$PMSessionName,
$PMSTG_CUSTOMER_MASTER@TableName,
$PMINS_CUSTOMER_MASTER@TableName,
$PMSQ_STG_CUSTOMER_MASTER@numAffectedRows,
$PMINS_CUSTOMER_MASTER@numAffectedRows,
$PMUPD_CUSTOMER_MASTER@numAffectedRows,
$PMINS_CUSTOMER_MASTER@numRejectedRows,
FAILURE,
>> $PMSourceFileDir\$PMWorkflowName_stat.txt

Note : 

• Pre/Post session commands need to be changed based on Informatica server operating system.
• Highlighted part of the script need to be change based on the source, target table instance name used in the mapping.

III. Reusable session

Reusable Mapping

A reusable mapping will be created to read data from the comma delimited file generated by the pre/post session command task. Below is the mapping created with an expression transformation to populate additional columns required in the target table 'ETL_PROCESS_STAT'

• ETL_RUN_DATE :- TRUNC(SESSSTARTTIME)
• ETL_CREATE_TIME :- SESSSTARTTIME
• ETL_UPDATE_TIME :- SESSSTARTTIME
• ETL_START_TIME :- TO_DATE(LTRIM(RTRIM(ETL_START_TIME)),'YYYY-MM-DD HH24:MI:SS')
• ETL_END_TIME :- TO_DATE(LTRIM(RTRIM(ETL_END_TIME)),'YYYY-MM-DD HH24:MI:SS')

Reusable Session

A reusable session will be created based on the mapping created in the last step. Session will be configured to read data from the file created by the pre/post session command as shown below.

Note : Make sure the Source File Directory and Source File name are given correctly based on the file generated by pre/post session command

Framework implementation in a workflow

Shown below is a workflow using Operational Metadata logging framework. Pre/Post session command to generate file with session run details will be in the first session, The reusable session will be connected after to read data from the file and load into ETL_PROCESS_STAT table.

XML Analyzer for Informatica PowerCenter

It is my pleasure to present to you the

XML Analyzer for Informatica PowerCenter

This is a tool created to fulfill the following purposes:

• Create Source-to-Target dependency analysis
• Create Technical Solution Documentation
• Perform automated code validation

All in an automated fashion and with (almost) a single click. The tool is now available in few flavours:

• Online tool - available without installation, anywhere you'd need it.
• Server version - you can have your own instance available in intranet. Available to all developers within the company without having to send code over the Internet.
• Offline executable - a simple executable tool that allows to generate html Technical Solution Documentation in batches.

All of the above versions are general in their purpose. They cover sample checks and report some general issues if found. Feel free to contact me if you'd need a tailored solution, covering company- or project-specific validation and reports. Use the features listed to come up with any requirements that would suit your business or technical requirements.

Source-to-Target dependency analysis

Here's a description of how it has all started and what was the original goal fot the tool. Or go directly to this source-to-target analysis sample table where you can see a sample mapping analysis. All Source columns with their corresponding Target columns. You can easily check what target colums are influenced by a given source to perform impact analysis, as well as check where do the values come from for any given target column. You can click on column names to sort the table. Shift+click for multiple selections.

Technical Solution Documentation

The Source-to-Target analysis is performed for all mappings found withing the Workflow XML file. This - along with workflow, worklet (if any) and details for all the sessions creates a technical report covering all implementation details. All available at a glance, with a table of contents, can be used to create documentation for whole project.

Automated Code Validation

While creating the report, different checks are performed to ensure the high development quality. This is very helpful in large scale projects with many developers involved and allows all team members to follow the same practices. All developers can have their code quickly checked to make sure there are no typos in log file names, or any debugging-mode options left on before having their code released to production environment. Here is the list of current check performed by the tool. As said, it can be easily expanded to accomodate any additional quality tests - please contact me if you'd need a dedicated solution.

• Comparing workflow log file with workflow name
• Checking if a session is valid
• Checking if a mapping is valid
• Tracing level
• Overridden session properties
• Hardcoded connections
• Comparing session log file with session name
• Checking if session is not DISABLED
• Expression containing comment (--)
• Filter condition containing comment (--)
• Router group expression containing comment (--)
• Lookup SQL Override containing comment (--)
• Source Qualifier SQL Override containing comment (--)
• Connections with names containing 'tmp', 'temp' or 'test'

Check the main XML Analyzer page for up to date feature list!

PowerCenter Best Practices

Introduction Below you’ll find some simple rules one should consider when building PowerCenter Workflows. This list may be further extended - it definitely doesn’t cover everything. And it probably never will. However it may be further extended to include other Best Practices, examples and explanations. It’s also possible that some of the guidelines below may be reviewed and altered anytime in the future. Feel free to read, ask and comment. Keep in mind that the author holds no responsibility for whatsoever :) General Limit the number of rows as early in the mapping as possible (SQ, aggregators, joiners, filters) - the more you limit at the beginning of the data flow, the less rows will be processed by all downstream transformations. For generating IDs use IDENTITY columns or DB triggers. There is a number of reasons: fewer PowerCenter objects reduce development time and maintenance effort IDs are PowerCenter independent, so any other app used to populate the target table will not cause any issues while migrating the code between environments there is no need to worry about the sequence value If for some reason you need the ID on PowerCenter side, use Sequence Generator rather than Stored Procedure call for performance reasons - Sequence Generator is really fast as it simply gives you numbers in IS memory. Stored Procedure call requires connecting to DB engine, invoking the Stored Procedure, and sending the number back to IS. The communication overhead is huge and may heavily impact performance. This is also a factor of the connection speed and distance. If you’re afraid of losing the generated sequence number (which might happen when exporting/importing xml without the Retain Sequence Generator Value option), store the last ID in DB and use it as a start value adding to the sequence and update it with the last ID generated this way. There should be no unused ports in any transformation (except Source Qualifier) - Source Qualifier should match Source Definition 1-to-1. But for any other transformation there should be no unused ports. Why fetch and process the data for half of the data flow and use extra memory, if you don’t need it? Avoid implicit conversion, as it is slower and might lead to errors - There are number of issues with implicit conversion (e.g. when you link SQ string port to decimal port in the following expression): it’s simply not visible. Anyone checking the mapping will not notice that there is any conversion of datatypes the conversion might behave not as expected (e.g. roundings, trailing/leading spaces or zeros, etc.) there may be unhandled conversion errors Therefore use appropriate conversion functions (e.g. TO_DECIMAL) explicitely. Remove all the 'Error (Transformation error)' messages from default values in Expression transformations - you will avoid getting unwanted error messages in logs. It will not be checked at session initialization. Consider adding Expression transformation right after each SQ and right before each Target - pass-through expressions do not cause additional computation. But I couldn’t count how many times I had to make “little change” before or after the whole data flow in an existing mapping. Having this dummy expression transformation helps a lot. It’s also very useful when relinking all ports after some changes (using Autolink by name). Aggregators: Use as soon as possible - it limits the number of rows processed by any transformation downstream. Use 'Sorted Input' only if possible, but do NOT sort just for aggregation - This one is a bit tricky. Aggregator with a “Sorted input” property works faster and uses little cache. But aggregating data does not require sorting. Therefore, if dealing with unsorted data, Aggregator (without Sorted Input, large cache) will perform better than Sorter (large cache) + Aggregator (with Sorted Input, small cache). If you do add The Sorter, the cache will not disappear - it’ll be created for the Sorter. Adding Sorter is a good idea only if you need to sort the data anyway. Expression: Use local variables for common computation (especially if complex) - If you need to perform some computation for many output ports (e.g. datatype conversion on one of the input ports), you can define a variable port doing it once (e.g. TO_DECIMAL(input_port)) and use the port in expressions for the output ports. This way it will be done once. This is especially important, when the common logic is complex, because: the performance gain is even grater it’s easier to maintain - there is one place you need to check for any errors and implement any changes Avoid local variables if no code reuse opportunity is present - If the output port does some computation and it is not common for other output ports, use the output port expression. Do not add a variable port. Input -> Variable -> Output makes the expression not visible from Mapping Designer (i.e. without opening Expression Transformation window) Filter / Router: Any complex expressions should be done in an Expression transformation before the filter / router - it is very important in case of Router. Calculate once, then compare - instead of calculating many times. Remember, that for every input row, all group expressions are checked (Router can have many output for one input row). It is a good idea to do the same for Filter, as for example you can use variable ports in Expression while it is not possible in Filter. Keep in mind that router executes all expressions against each input row Floats: When to use? Only for Physics, where accuracy is not that important. Keep in mind that floating point numbers are not accurate! E.g. 0,1 is stored as ~0.0999999999999999991, which is very close to 0,1, but not equal. You can end up having 0 kept as -0,00000001 (real-life example). This may cause a lot of trouble when selecting all rows greater or equal to zero. For any financial, quantitative - almost any calculations use decimals. Lookup: Should have only the ports that are being used - The more ports in Lookup, the more data is fetched. It takes more time and memory to build the cache. Limit the number of rows fetched using filter - Use the Lookup Source Filter. This will limit the amount of data to fetch and keep in memory. Parameter files: Use one parameterfile per workflow - There are many ways to use parameters and parameter files. You can define General parameters for all sessions in a workflow. You can have each session use it’s own parameter file. What is the best way? There is no simple answer. You might need different setup for some purposes. However in general it’s good to have one parameter file per workflow. You won’t affect other workflows when changing the parameters. The file will be quite simple (in most cases). Consider using auto generated parameter files using values stored in DB - Workflows can start (or end) with a generic session creating the parameter file. This would allows easy maintaining all parameter files, parameters, do any checks for naming conventions, etc. Sorters: Avoid if possible - do not sort the data unless it is really needed. Use as soon as possible - if the sorting is needed, sort data early in the mapping and try to take advantage of it using Sorted Input for as many transformations downstream as possible. Use as long as possible - Try to arrange the mapping in a way that all transformations sharing the sort type come one after the other. Avoid resorting separately for every transformation. Take advantage on DB sorting if covering index is available - If covering index is available in DB, sort the data using Source Qualifier and use Sorted input for the transformations downstream. Don’t: Sort just for aggregator - Although Aggregator works faster and doesn’t use caching (almost) with “Sorted Input” property checked, it is not worth sorting data just for aggregation. This will just move the need for cache from Aggregator to Sorter and in fact will slow down the overall performance as the aggregation algorithm doesn’t require sorting. Sort for joiner (especially each sorter!) - Do not use two Sorters for Detail and Master groups of Joiner Transformation. In such case both pipes will require caching while for unsorted data just the Master input is cached. Source Qualifier: Connect only the necessary ports from SQ - Source Qualifier determines what really needs to be fetched from source. While it should match Source Definition 1:1 with all ports linked (to avoid future mistakes and misunderstandings), it reads only the data for ports linked to the next transformation. This can be observed while generating SQL statement (Properties->Sql Query->Generate SQL) - only the used ports will be listed in SQL statement. This is especially important for wide sources, as this way the amount of data transferred can be limited. Avoid using SQL override statements - Using the Generate SQL property overrides any and all other settings (i.e. port order, sorting, filters). This is also not visible at the first glance and reduces the code transparency. This should be therefore avoided. If you need to: sort the data - use "Number Of Sorted Ports" property with the proper ports order filter the data - use "Source Filter" property join multiple homogeneous sources - use "User Defined Join" property SQL override should be used only when some complex statements are needed (e.g. use of DB-specific functions or subquery) The above is also applicable (in general) to lookup transformation. All ports between Source Definition and SQ should be connected - As mentioned above, it is good to have SQ matching Source Definition as it reduces the number of future mistakes. All the datatypes should match Source Definition - In general implicit datatype conversion should be avoided as it may lead to errors and unexpected behavior. Update Strategy: Don’t use DD_REJECT - Don't process unneeded data (unless really needed in .bad file, use "Forward Rejected rows"). Otherwise use a Filter transformation to drop unneeded rows of data. Sorting: DB or Sorter transformation? When considering performance, a case-by-case analysis is needed. There is no “best-for-all” approach. Database as well as network performance must be considered. Database and Integration Service load must be considered. Database setup must be considered. Finally, Database and Integration Service hardware configuration must be also considered. There are however some general rules that can be applied: Use DB if covering index is available - Try to take advantage of existing DB indexes. It happens quite often that the source tables are indexed and all that needs to be done is setting the appropriate order of the ports in Source Qualifier and the “Number Of Sorted Ports” property. If not: Check if it's possible to create covering index - Sometimes the DB index is not available, but can be created. The advantage of it is huge enough, that It’s always good to check. Of course there are scenarios, where you can’t afford to use an index (e.g. there are lots of inserts / updates on a table with rare selects). Investigate if it is possible - if not: Use Sorter transformation - If no index on source is available and data needs to be sorted, use a Sorter transformation. Keep in mind the remarks discussed above.