Populating the target table
Moderators: chulett, rschirm, roy
Populating the target table
[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]
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]
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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
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
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.
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 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.
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.
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
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
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
-
- Participant
- Posts: 132
- Joined: Wed Mar 01, 2006 11:12 pm
- Location: Pune
Re: Populating the target table
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'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
SMB