Warning: Truncation may occur due to retrieving data from database column

Question

I am importing a data set from Oracle using SSIS. SSIS is gives me a warning:

Truncation may occur due to retrieving data from database column "Third Party" with a length of 28 to data flow column "Third Party" with a length of 25."

The warning does not make sense. The destination is a SQL Server database where the attribute is [Third Party] as nvarchar(255).

Why is the tool giving me this odd error?

I have tried changing the length of the nvarchar(max). This did not make a difference.

select 
    case 
        when XTNL_HOS_FLG = 1 and VND_MG_APL_FLG = 0 
           then 'Host'          
        when XTNL_HOS_FLG = 0 and VND_MG_APL_FLG = 1 
           then 'Support or Manage' 
        when XTNL_HOS_FLG = 1 and VND_MG_APL_FLG = 1 
           then 'Host and (Support or Manage)'  
        else ''
    end  as "Third Party"
from 
    table1

try casting within sql command it is better then changing type from advanced editor — Yahfoufi, Apr 08 '19 at 09:15

score 9 · Answer 1 · edited Aug 29 '19 at 12:29

9

Look in "Show Advanced Editor" in your Data Flow Task by right-click on Source Target. Then go to "input and output properties" tab and check the length of your columns.

Do the same on Destination Target.

Depending on the way you got your data, the type and length of columns aren't the same as the database.

edited Aug 29 '19 at 12:29

ChrisM

505
6
18

answered Apr 03 '19 at 17:50

Mariana Del Nero

91
3

It is more recommended to specify the column length from the SQL command because the Column property may change if the values provided in the query changed – Yahfoufi Apr 04 '19 at 07:32
I would always recommend if you have a text or CSV file to import the data by finding the max column length and using Unicode to import and then do the conversions in SQL server. This will save a lot of hassle. – Rishi Apr 04 '19 at 14:28

score 1 · Accepted Answer · answered Apr 03 '19 at 20:31

Since you are using an SQL Command as source you can edit you command and force the source column length and to help the OLEDB source to recognize it. You can use a CAST function to do that:

select CAST(
    case 
        when XTNL_HOS_FLG = 1 and VND_MG_APL_FLG = 0 
           then 'Host'          
        when XTNL_HOS_FLG = 0 and VND_MG_APL_FLG = 1 
           then 'Support or Manage' 
        when XTNL_HOS_FLG = 1 and VND_MG_APL_FLG = 1 
           then 'Host and (Support or Manage)'  
        else ''
    end AS NCHAR(255)) as "Third Party"
from 
    table1

@user716255 try to remove the old source component and add a new one with thi sql command. — Hadi, Apr 22 '19 at 16:59

score 1 · Answer 3 · answered Aug 04 '21 at 21:53

1

I did it like this: I used the command left(column,n) to match the output file.

answered Aug 04 '21 at 21:53

Tiago Buhr

11
1

Warning: Truncation may occur due to retrieving data from database column

3 Answers3

Linked