Populating the target table

rajeevm · Post by **rajeevm** » Thu Apr 19, 2007 8:46 am

[b]Hi All,

I am trying to populate the table which is our staging table as we extract the data from the sequential file. when i run this job I am getting lot of warnings saying '
DATA HAS BEEN TRUNCATED' what could be the reason its getting like this. I am unable to understand this i have checked the columns if there is any mismatch and also length and data types but everything is fine.

Please try to give you valuable suggestions in order to resolve it.

Thanks[/b]

DSguru2B · Post by **DSguru2B** » Thu Apr 19, 2007 8:52 am

Well aparantly, you missed a column or two. Since your source is a flat file, its hard to say what the maximum length can be. You will have to put in a LEN() function to get the length of each and every column. Inspect to get the max of all the lengths that you got and then compare that with your target table. I know, its a pain.

rajeevm · Post by **rajeevm** » Thu Apr 19, 2007 9:00 am

hi DSguru,

Thanks for quick response its kinda tricky i didn't get your point how do i use the len function. as I didn't miss any columns in the source everything was fine. I have to clarify that i am running the historical data to the stage table when i ran for two years it was fine but when i try to run for the third year i am getting these warnings.

Thanks

DSguru2B · Post by **DSguru2B** » Thu Apr 19, 2007 9:13 am

Well, then you need to look at 3rd year's data and see which column's data is going beyond its definition.
Do a spot check on the data, if you see any data going beyond its definition thats the culprit. If the number of columns are large, then source your file, put in a Len() function to get the length of the field in the transformer , pass it through the aggregator, get max of all the columns. Group on a dummy key that you will create in the transformer. Load it to another flat file. Now open the file and check the size of each field. It should be less or equal to the target columns. The column that does'nt satisfy this rule is the culprit. Fix the data or increase that particular column(s)' size in the target table.

kris007 · Post by **kris007** » Thu Apr 19, 2007 9:17 am

Check the lengths of both your source and target fields and see if there is any huge difference for particular fields. Especially, fields with descriptions, addresses and so on. What the warning implies is that data from a particular source field(s) cannot fit into its corresponding target field as the length of the target field is less than that of source.

rajeevm · Post by **rajeevm** » Thu Apr 19, 2007 9:27 am

I have checked the source and target lengths as well as the data types are same is there any other way to do this

Thanks for you help

DeepakCorning · Post by **DeepakCorning** » Thu Apr 19, 2007 9:34 am

The simplest way to do this will be to replace ur Target "table" with yet another flat file (smae strucutre as ur target table as of now) and check the data which gets populated in the file.

Its a hectic process but u will need to go on doing this and may be narrowing down the data processed (yearly may be monthly) will help you in debugging this.

rajeevm · Post by **rajeevm** » Thu Apr 19, 2007 11:39 am

Hi Deepak,

I tried with another flat file but it just populated fine , but when i try to populate the table why i am getting the warnings, I do not have the data which can be processed by monthly i just have to dump the rows which represent the year 2004-2005 from the source i.e sequential file. our target table is nothing but the stage table i need to further extract from there to move onto the target.

Any input would be appreciated.

Thanks

DSguru2B · Post by **DSguru2B** » Thu Apr 19, 2007 11:44 am

Buddy, try with Len() function to get the exact lengths. Only then you can check with the target metadata. Otherwise it will be very tedious. You might get lucky and spot the culprit column(s) or it might take you forever. Thats why try with the Len() function.

DeepakCorning · Post by **DeepakCorning** » Thu Apr 19, 2007 12:16 pm

If you can not point it the way i told u , try what DS Guru is saying. Its a veru effective method. You will get exact length , but again it will not be easy.

You can also try somethign liek Maximum Len so that u know whats the max len of a column in the file.

rafik2k · Post by **rafik2k** » Thu Apr 19, 2007 12:31 pm

Is any row getting rejected due to truncation?
What database are you using?
There might be data issue like junk character.

Check some sample source data

baglasumit21 · Post by **baglasumit21** » Sun Apr 22, 2007 11:54 pm

rajeevm wrote:Hi All,

I am trying to populate the table which is our staging table as we extract the data from the sequential file. when i run this job I am getting lot of warnings saying '
DATA HAS BEEN TRUNCATED' what could be the reason its getting like this. I am unable to understand this i have checked the columns if there is any mismatch and also length and data types but everything is fine.

Please try to give you valuable suggestions in order to resolve it.

Thanks

Check the length of the file columns. Also it may be due to the file format you are selecting. Check whether you have used 'Fixed Width File' or 'Delimited file'

DSXchange

Populating the target table

Populating the target table

Re: Populating the target table