DSXchange

I rebuilt the job by separating the references to different look up stages and rewrote the stagevariables and constraints.It worked.

Parallel inserts are allowed on the tables.Suppose that the table you mentioned is loaded by only one parallel job in your project and the job runs on 8 nodes.It is implicit that it is allowing 8 parallel insert statements. In your case the table is loaded by two jobs and assuming your job runs in 2...

Is there any specific reason for defining the soruce metadata as 25,2 instead of 14,2?

What are you passing from ExternalSource stage and what option did you choose in XML input stage for input method URL or XML Content?

Just to make sure nothing wrong in your derivation.Enable KeyChange Column in sort and write your derivation like

Are you ftping in binary or ascii mode?

'|' is the field delimiter not the record delimiter.Remove the record delimiter property and add field delimiter ='|'

That is why I used to mark similar columns as BigInt in datastage when mapping to the ODWH.

What is the nullability of the field?

I assume RPhani is generating sequence using surrogate key (state file) but not generating numbers by formula or loop.

Use a stagevariable and map the generated value in the function as Arnd suggested:
Mod( svSurrKey,1000)

Split the Combined records from first 2 sources into two streams using copy stage and send one stream of the combined data from first 2 sources as reference to Lookup stage and use the third source as stream , perform look up on key and capture the rejects. Now send the other stream from copy stage ...

Check if the link http://www-01.ibm.com/support/docview.w ... wg21587829 helps

OR a lengthy approach...

1-Split the input to two streams.
2-Find max year and month in the output1
3- join the two outputs(Fork join) on projectid,month and year and then find sum.

How is that 10000 passed to the Surr.Key stage and in the job which generates surrogate key (assuming job 2),whats the option selected for Generate Key from Last Highest Value property?

As Ray suggested first step is to talk to the data provider if that is possible
OR
Read as a single column ,pass that to a stage variable, strip out the " from the stage variable and use field function on the stage variable in the derivation for columns.