I was modifying an SSIS package in which I was fetching data from Oracle source using Attunity. The query was complex and using multiple CTEs to fetch data. As soon as I added new query in Oracle Source and remapped the columns in the destination object, everything looked good at design time but when I ran the package, it encountered the following error.
OCI error encountered. ORA-01406: fetched column value was truncated
This query was straight forward select statement and it was not using any “temp table” equivalent where it would truncate the data. Finally I found an answer.
I assume that when we modify the query, SSIS Attunity sniffs the metadata (may be using first X number of rows) and stores it in SSIS package. In my resultset, I was getting decimal values so SSIS stored the data type for that column as DT_NUMERIC(16, 4). When I ran the query in Sql Developer, I saw that certain rows for that column retrieved result as float number due to complex calculations which caused the value to be truncated resulting in this error. I rounded the column to 4 decimal value in the query and Visual Studio was happy to run my SSIS package.
Possible Solutions
- Refresh metadata
- Round decimal values
Leave a Reply