September 2, 2011 at 11:55 am
Hi all,
I created a package using oracle database as source. SSIS reads the columns in oracle in a Unicode format. I get the below error at runtime:
There was an error with output column "PRIOR_CST" (1989) on output "OLE DB Source Output" (1878). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". End Error Error: 2011-08-28 03:10:53.20 Code: 0xC020902A Source: stImport MMT MMT [1868] Description: The "output column "PRIOR_CST" (1989)" failed because truncation occurred, and the truncation row disposition on "output column "PRIOR_COST" (1989)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
To fix it, I use the advanced editor to open the source and in the tab "Input and output properties", I increases the length for the output column "PRIOR_CST". This fixed the error. I scheduled the package. Now I frequently get the same error for different columns from different tables (based on the input data I guess). Is there any way I can fix this so I dont get this error at allin future. I have 20 diff tables with hundreds of columns at the source whcih I import.
Thanks and Regards,
Praveena
September 2, 2011 at 1:58 pm
You can choose to ignore all truncation errors (in the error output), but you would still have to do this for all source columns and IMO it is a very risky thing to do.
Working with 2 different systems here with data types that aren't 100% compatible all the time, I would advise you to check the data types in your source and adjust the lengths accordingly in SSIS. You will unfortunately have to do this for all source fields.
An approach that I prefer, is to stage the data where each field in my staging table is a varchar. Doing it that way, I ensure that the process doesn't fail during initial transfer and I have all the functionallity within the SQL language at my disposal to validate and deal with dirty data (or incompatible types for that matter) accordingly.
September 6, 2011 at 10:28 am
Thank you Martin! I will follow your suggestion.
September 8, 2011 at 3:00 pm
All,
I have a column in oracle db sit_id which of NUMBER datatype of lenght 22. SSIS reads it as unicodestring of length 38 in the source. I get the truncation error I previously described at this column. If I changethe source output column lenght to 50, the package works fine. But if I change it to float or numeric it gives me the below error.
"Accessor is not a parameter accessor"
Please suggest which numeric datatype would make the package work.
Thanks and Regards,
Praveena.
September 8, 2011 at 3:11 pm
venus.pvr (9/8/2011)
All,I have a column in oracle db sit_id which of NUMBER datatype of lenght 22. SSIS reads it as unicodestring of length 38 in the source. I get the truncation error I previously described at this column. If I changethe source output column lenght to 50, the package works fine. But if I change it to float or numeric it gives me the below error.
"Accessor is not a parameter accessor"
Please suggest which numeric datatype would make the package work.
Thanks and Regards,
Praveena.
SSIS has selected the (dare I say) most compatible and equivalent datatype. You cannot change it because float and numeric is not compatible.
Leave the datatype as it is (with the modified length of course), and rather add a data conversion task to convert the source data to more appropriate types.
September 8, 2011 at 7:29 pm
Martin, thank you for the response.
I wish I could do something to prevent something rather than waiting for an error to occur and then fixing it. It becomes tough to identify which columns might need the increase in the length. source type number(22) doesn't fit in unicode string(38). Seems like increasing the length for all the numeric source columns might fix. I will give it a try.
September 9, 2011 at 7:04 am
venus.pvr (9/8/2011)
Martin, thank you for the response.I wish I could do something to prevent something rather than waiting for an error to occur and then fixing it. It becomes tough to identify which columns might need the increase in the length. source type number(22) doesn't fit in unicode string(38). Seems like increasing the length for all the numeric source columns might fix. I will give it a try.
Why don't you change the type to String instead of unicode string. The problem with unicode is that it takes 2 times the amount of space...so a 22 byte number from your source will take 44 bytes in unicode. That's a lot of extra space that you don't want to be using.
It is definitely possible to account for all these scenario's, without having to wait for an error. But in order to do that you will have to do some ground work upfront.
As I said before, I would suggest that you make all the data types strings and that you also stage the data. Ensure that the length property of all your source columns account for the total length it may be.
September 9, 2011 at 7:19 am
Martin,
Even string wouldn't work(cannot convert between unicode and nonunicode string error). Staging the data as is and then converting using data conversion would work. You are correct, I should have accounted for these things earlier.
Thank you very much for your responses.
Praveena.
September 9, 2011 at 7:32 am
venus.pvr (9/9/2011)
Martin,Even string wouldn't work(cannot convert between unicode and nonunicode string error). Staging the data as is and then converting using data conversion would work. You are correct, I should have accounted for these things earlier.
Thank you very much for your responses.
Praveena.
Sure...no problem 🙂
Another thing I usually do (and sorry for only mentioning it now...still waking up), is to write a query against Oracle source tables instead of connecting to the table directly. There are a lot of benefits to that approach, like eliminating rows you don't need and also converting/casting data types. What I usually do is to convert everything to varchar(2) in the Oracle query...that eliminates a lot of the data type issues.
August 6, 2012 at 7:35 am
With my recent experience of querying Oracle for Numeric fields, I've noted that SSIS defaults a field's output type with an OLEDB query as Unicode string of length 38 (the max precision for Oracle), if it can't determine what the precision+scale is in the query (for example, if querying a view, which has the number-to-query in a subselect); it is able to set the output type to NUMERIC with matching precision and scale if its quering directly against a table though.
I do receive truncation errors when the OLEDB output for the field is Unicode(38), if the underlying Oracle data contains 38 digits with a decimal point - it seems that SSIS's default of 38 characters doesn't accommodate a decimal-point if one is present? Increasing the Unicode field length to 39 works in my case...
August 6, 2012 at 9:21 am
Just a couple of amendments to my last post:
1. Tables can also have datatypes defined as NUMBER without precision and scale specified (defaults used - which then results in SSIS accommodating field as Unicode)
2. It can be difficult to know the required length of a Unicode string will accommodate a number field returned from Oracle - 38 is the maximum number of significat digits - but the amount of digits may be much larger than this (eg 0's following decimal point are not counted as significant).
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply