SSIS Oracle Error Solution: ORA-01406: fetched column value was truncated

By | February 22, 2017

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
Vishal Monpara is a full stack Solution Developer/Architect with 12 years of experience primarily using Microsoft stack. He is currently working in Retail industry and moving 1’s and 0’s from geographically dispersed hard disks to geographically dispersed user’s mind leveraging geographically dispersed team members.

Leave a Reply

Your email address will not be published. Required fields are marked *