DSXchange

Hi,
i have requirement that i need to split 950 line items for each Header record according to business logic where source file contains millions of records.

eg:- suppose a transaction may contains 1000 line items and the logic should go as follows:-

H Z-101 K012
L 2345.56 4567.89 Y 2004 013 C0060
L 8909.89 3456.78 N 2004 012 C5549
.. ..... ..... .. ....... ... .......
.. ..... .... ... ....... ... .......


950th line Item
L 8999.99 6789.89 Y 2003 011 c6789
H z-101 k102
951 st to 1000 line items
L .... .... .. .... ... ....
.. ... .... .. ... ... ....

if the transaction exceeds 950 lines, then remaining line items should come after creating a header(see the above example).

Can anybody help me how to implement this logic in datastage mapping.
Source is flate file
Target is SQL Server Database.

Thanks

Hi,
Since you say that you need to put a header in case you have over 950 concecutive non-header lines you can try doing it using basic job that reads the flat file, or in a derivation use Stage Variables to count lines and in case you go over 950 lines with no header concatenate one yourself using the CRLF line termination (since your using windows, if unix was the case you would use only the LF line termination).
i.e. line derivation will be something like this:
If SVLineCount > 950 Then SVHeaderLine : Char(13) : Char(10) : DSLink.Line Else DSLink.Line

CR = Char(13) ; LF = Char(10)
this is the overall idea (not the small details).

IHTH,

Search the Forum to determine how to use stage variables to detect whether an input value has changed.
Add to this technique's an expression that checks for 950 and resets a "counter" stage variable. An occurrence of a header line in the input stream will also reset the counter.

I found that sometimes it's easier to write a short python script to pre process the file.