Varchar Field Truncation Issue : Sequential File

bbobpop1 · Post by **bbobpop1** » Thu Mar 12, 2009 4:33 pm

Hi

I am using a comma seperated sequential file as a source. One the columns called "A" in this file has a datatype VARCHAR(30).

The target is database and Column "A" is populating Column "B" of Database.(Size of B: Varchar(30)).

If the length of the incoming field is more than 30, I want to catch that record in to rejected file and continue the processing.

I have tried the option in the sequential File like "Reject Mode"= Output. It is rejected the records for Integer (/ Date Format MisMatch) DataType but not for the varchar datatype.It is reading the Column by truncating the Values.

I changed the datatype to "Char", but then I am not able to read the file.I was searching for the simillar issue in the forum but was not able to find the appropriate.

Can you please guide me on the same?

Thanks
Bob

dh_Madhu · Post by **dh_Madhu** » Thu Mar 12, 2009 8:58 pm

Did you view the data through the seq file....also check the column ordinalities between the source and the target..

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Mar 13, 2009 3:40 am

Remove the max length from A and add the check in a transformer in the flow.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Mar 13, 2009 3:40 am

Remove the max length from A and add the check in a transformer in the flow.

bbobpop1 · Post by **bbobpop1** » Fri Mar 13, 2009 7:56 am

Thanks

I have mentioned the column size VARCHAR(30) in sequential File. Also I can view data (with Truncated data) in sequential file.

Both Source and Target has same data type.

Also Can you please let me know which function I can use to check the len of decimal, varchar etc...

Please let me know if you need more information.

Thanks
Bob

chulett · Post by **chulett** » Fri Mar 13, 2009 8:01 am

As noted, you'll need to increase the field size you are using in the Sequential File stage and then do a length check on the string, logically rejecting it if it is longer than 30 characters.

bbobpop1 · Post by **bbobpop1** » Fri Mar 13, 2009 11:03 am

I am using the logic which has mentioned above. Now I am facing one m more challenge. Can you please let me know how to avoid truncation of decimal fields / check lenght of decimal fields (Precision and scale) to fit the exact target definition?

ray.wurlod · Post by **ray.wurlod** » Fri Mar 13, 2009 1:28 pm

As noted elsewhere (did you Search?) the string requires two extra character positions - one for the sign and one for the decimal placeholder character (unless scale = 0).

Pagadrai · Post by **Pagadrai** » Fri Apr 03, 2009 1:54 am

Hi,
Is the issue resolved?
I just went through the following writeup in manual.
See if it helps your case.

" APT_IMPORT_REJECT_STRING_FIELD_OVERRUNS
When set, DataStage will reject any string or ustring fields read that
go over their fixed size. By default these records are truncated. "

Let me know once you try this.