Unable to write to a sequential file
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 45
- Joined: Fri Nov 07, 2008 12:22 pm
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:
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 :
Current Output: APT_STRING_PADCHAR = 0x20
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
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
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
Last edited by hiral.chauhan on Thu Mar 01, 2012 11:59 am, edited 2 times in total.
Thanks,
Hiral Chauhan
Hiral Chauhan
-
- Premium Member
- Posts: 45
- Joined: Fri Nov 07, 2008 12:22 pm
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 45
- Joined: Fri Nov 07, 2008 12:22 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
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
-
- Premium Member
- Posts: 45
- Joined: Fri Nov 07, 2008 12:22 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!!
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!!
Thanks,
Hiral Chauhan
Hiral Chauhan