March 11, 2008 at 6:34 am
[Source - SP C3 Rebate$ units_org2 [1]] Error: Data conversion failed. The data conversion for column "AmountRebated" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[Source - SP C3 Rebate$ units_org2 [1]] Error: The "output column "AmountRebated" (49)" failed because truncation occurred, and the truncation row disposition on "output column "AmountRebated" (49)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
[Source - SP C3 Rebate$ units_org2 [1]] Error: An error occurred while processing file "\\Syndevsql\rawdata\Schering\SP-MNYCC 0000008\SP C3 Rebate$ units.org2" on data row 257124.
so could u plz tel me what is the exact error here..i am importing a flatfile into the db so i get these error when i look at the row its has correct data so could u plz let me know....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 11, 2008 at 7:21 am
a couple of area i would look into. first, what is the datatype of the table field that you are importing AmountRebated into? Second, what is the raw value from the flat file that you are importing from?
Other questions, how is the overall mapping occurring from the flat file to the table, is parsed via a delimiter or is it a character count for where each field is located? is there an extra hidden character in the flat file, like a tab or return, that is being counted and that is throwing off the actual value trying to be imported?
many questions but the main starting point is to figure out what is the data value that SQL Server thinks is being exported and then take it from there.
March 11, 2008 at 7:53 am
the datatype i gave is nvacrhar(50) ..the flat file for amt rebates is in this way .....
$15.23
$234.33
so i have the raw file in this way ...n the over all mpping is the text qualifier is " and the comma delimited...no extra hidden char as of my knowledge....let me give u in detail....
actually i have a row in this format...
"PCS-CPA",1665,"Preferred Non-PDL Mail US","00085-1132-01","PROVENTIL HFA INHAL 90MCG 6.70",2627,"ADVOCATE HEALTH CARE ",570.00,$154.12
"PCS-CPA",1665,"Preferred Non-PDL Mail US","00085-1132-01","PROVENTIL HFA INHAL 90MCG 6.70",3978,"ADMINISTRATIVE SERVICE CONTR ",288.00,$77.87
PCS-CPA",1665,"Preferred Non-PDL Mail US","00085-1132-01","PROVENTIL HFA INHAL 90MCG 6.70",2184,"AEGON
so in this case the third file is partial row it did not have last two columns at the end and doesnt even have text qualifier for "AEGON" in this way so i modified the row giving the qualifier like "AEGON", ,
so this is the way i jus modified n executed but i still get error says the above error plz help me out.... thkz in advaacne...need any more details let me know...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 11, 2008 at 8:24 am
if you were to remove all records from the file but the first record, does the first record import?
March 11, 2008 at 8:38 am
the above reocrds gets imported but weh it gets to this file it gets the error i could nt know where the problem is
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 11, 2008 at 8:45 am
ok, when you get to the third record, if you subsitute the empty columns to be 0,0 in the flat file instead of the empty placeholders, does this import? (I am trying to eliminate the other columns as having a problem)
After trying that, can you place in a Derived Column task after the Flat File Source task to setup the values to read as NULL from the Flat file before doing the Destination step?
March 11, 2008 at 9:18 am
i tried doing that but still i get the same error...i couldnt do that i dont know why ??? but evrythng seems to be good....n i have other ques for u.....
i have importing a file into a db which has a table with some rows so i am importing some more rows into the same DB so in the middle of execution there was an error around 2000 rows has been imported so how could i make it run from 2001 row after making changes to the row do i need to delete evrythng and start from beginning or is there nay other way such that the 2000 rows would be the same way and starts from 2001 so that there would be no duplication...plz thkz in adavcnae
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 11, 2008 at 9:31 am
the approach i would do for that is to redirect the rows that fail to an error file, which would be the result of the OLE DB Destination Error Output. This would still import the records that are valid and then you can handle the records that are not valid and then reprocess.
As far as what you have imported already, either start over in the table by clearing out the table or create a backup of the file, delete the records from the file that you already imported and the begin from the error reccord.
The problem with playing with the flat file approach is that you run the risk of mistakenly not removing an imported record and therefore creating a duplicate record in your table.
hope this helps.
March 11, 2008 at 9:49 am
another thought on the error record, if you right click on the Flat File source and select Show Advanced Editor, go to the Component Properties tab and look at the property for RetainNulls. This by default will be set to False, change this to be True and then try to import the record again.
The zero length columns will be treated as NULL. The catch though is to ensure that your data table allows nulls for each column that you are importing to.
March 11, 2008 at 9:50 am
hey i m trying to do the same wty...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 11, 2008 at 10:10 am
By the way - did you actually look in the place it told you to? What's wrong with that row?
from your error messages:
Error...on data row 257124
Is that the last row by chance?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 11, 2008 at 10:19 am
no its not the last row ...if its last row i culd have though of partial roa at end ...bit its not tat ....i checked it that was the row which givess error i posted before
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 11, 2008 at 12:11 pm
hey matt n chuck i have an issue how should i redirect it in this case i have a problem with my source which is flat file so i have hunreds of flat files which are partial so i jus want to redirect them and import the rest ...so i jus gave another oledb destintion from flat file source to oledb on error ..after that i jus only got the errorcode ,errorcolumn in the oledb destination i havent got the the other columns in that what should i do to know exactly which are the columns which are partial rows ...plz let me know urgent...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 11, 2008 at 12:18 pm
In order to redirect - you'd need to have a separate file (probably a text file) be the destination of the error. You don't want it to run into another error when redirecting the output.
you'll then need to look at the error files to figure out what to do with them.
You will also need to set the handling to redirect on all columns.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 11, 2008 at 12:27 pm
hey matt thats what i m dng now from source gave to other flat file dest and connected it on failure..but in flat file destiantion in the columns it shows me jus 3 columns they are errorcolumn ,code n such way it doesnt include the exact row which got partial rows so how could i get that n moreover in my case it doesnt even redirect the error code....so how should i do handling....i have turn it to redirect row on error in flat file source and and i could nt see the columns in flat file could plz tell me how to do that ??? plz srry abt that as i m new to ssis
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply