Unable to write to a sequential file

Mike · Post by **Mike** » Thu Mar 01, 2012 10:17 am

Rather than having us keep guessing...

Why don't you tell us the exact data types of your 5? output columns along with the exact derivation expressions? Include the exact data types of any source columns used in the derivation expressions as well.

Mike

hiral.chauhan · Post by **hiral.chauhan** » Thu Mar 01, 2012 11:11 am

Hi Mike,

I am sorry about that, I just did not want to bombard everything which may not be relevant. Below is all the information of the job:

Code: Select all

                                         SQL Server Table
                                                ||
                                                ||
Sequential File ==> Transformer ==> Join ==> Lookup ==> Transformer ==> Sequential File

My source is a multi header/detail sequential file , I am reading everything as a Varchar(255) followed by splitting the detail records in the transformer, join them based on a key column and lookup on a sql server table , perform the following two transformations and send output to the sequential file. All the columns across all stages are Char. As per business requirement ,the output file must match the sort order of the file created by the existing legacy process (informatica), so I have explicitly made every stage to run sequentially. (Please forgive my ignorance if this is a very inefficient way to use Datastage, I was able to get the same sort order only by this way)

Right(MyLinkName.GFA_Column11,15)
Right(MyLinkName.GFA_Column12,15)

Here are my target columns with their datatypes:

GFA_Column1 - Char(7) - Nullable = NO
GFA_Column2 - Char(8) - Nullable = NO
GFA_Column3 - Char(20) - Nullable = NO
GFA_Column4 - Char(1) - Nullable = NO
GFA_Column5 - Char(7) - Nullable = NO
GFA_Column6 - Char(8) - Nullable = NO
GFA_Column7 - Char(3) - Nullable = NO
GFA_Column8 - Char(3) - Nullable = NO
GFA_Column9 - Char(1) - Nullable = NO
GFA_Column10 - Char(9) - Nullable = NO
GFA_Column11 - Char(15) - Nullable = NO
GFA_Column12 - Char(15) - Nullable = NO
GFA_Column13 - Char(1) - Nullable = NO
GFA_Column14 - Char(3) - Nullable = NO
GFA_Column15 - Char(8) - Nullable = NO
GFA_Column16 - Char(2) - Nullable = NO

Required output :

Code: Select all

00019152012021603000000378         F004012620120216025153S00014690000000000011453200000000077966017672012021600

Current Output: APT_STRING_PADCHAR = 0x20

Code: Select all

00019152012021603000000378         F                                                                                                                                                               0040126                                                                                                                                                         20120216025153S00014690000000000011453200000000077966017672012021600

I am treating all the fields as Varchar/char even though some of them are dates, some of them are amounts, counts etc.

I really appreciate your time and help in this.. sorry If I have not been clear/informative.

Thank you

hiral.chauhan · Post by **hiral.chauhan** » Thu Mar 01, 2012 11:13 am

in the above job design, SQL Server table is being looked up from the look up stage.. I think it got messed up in the diagram while posting..

chulett · Post by **chulett** » Thu Mar 01, 2012 11:40 am

Use the 'Preview' button to see what your post will look like before submitting it. The forum software removes all 'excess' whitespace unless you use

Code: Select all

 tags to preserve them. See? More better.

Mike · Post by **Mike** » Thu Mar 01, 2012 11:45 am

Hiral,

Now that Craig has been kind enough to show you what needs to be done... please put code tags around your expected output and actual output so we can see the exact spacing.

Mike

hiral.chauhan · Post by **hiral.chauhan** » Thu Mar 01, 2012 12:04 pm

sorry about that!!

I have edited my post now.
Thanks,
Hiral

Mike · Post by **Mike** » Thu Mar 01, 2012 12:58 pm

Your target metadata perfectly describes your required output... which is a 111-byte record.

The only way I can see you getting your actual output is if there is bad formatting information in the metadata either at the record level or the field level.

Your actual output looks fine for the first 4 fields. Does it really contain those few hundred extra spaces?

Go into the columns tab in the target sequential file and right-click column5 and choose "Edit Row". Are there any extra properties set for that column?

Mike

hiral.chauhan · Post by **hiral.chauhan** » Thu Mar 01, 2012 1:55 pm

That was EXACTLY the problem!!

When I did "Edit Row" for Column 4 and Column 5, the field width was set to 160. And so was for the last column! I changed it to the required length i.e. Char(1) and Char(7) respectively and I got the required output!!

Hands down genius !!!!!! :D

I am wondering when I will become like you all

Thank you Mike Thank you Craig Thank you Ray and thank you everyone!!!

I appreciate your time, effort and help!!