SSIS Truncate Warning when moving data from a string max length 50 to a string max length 20

Question

Noob SSIS question please. I am working on a simple database conversion script. The original data source have the phone number saved as a string with len = 50 in a column called Phone1. The destination table has the telephone number saved as a string with len = 20 in a column called Telephone. I keep getting this warning:

[110]] Warning: Truncation may occur due to inserting data from data flow column "Phone1" with a length of 50 to database column "Telephone" with a length of 20.

I have tried a few things including adding a Derived Column task to Cast the Phone1 into a DT_WSTR string with length = 20 - (DT_WSTR, 20) (SubString(Phone1, 1, 20)) and adding a DataConversion tasks to convert the field Phone1 from WT_WSTR(50) into WT_WSTR(20) but none of them work. I know I can SubStr phone1 in the SQL String @ the OLEDB Source but I would love to find out how SSIS deals with this scenario

Answer 1

Root cause - This warning message appears over the SSIS data flow task when the datatype length of the source column is more than the datatype length of the destination column.

Resolution - In order to remove this warning message from SSIS solution, make sure datatype length of the source column should be equal to the datatype length of the destination column.

Answer 2

Warning: Truncation may occur due to inserting data from data flow column "Phone1" with a length of 50 to database column "Telephone" with a length of 20.

Solution :

To remove the warning mentioned above, you may follow the below steps:

1)Right click on source components (for ex: flat file) and click on show advance editor.

2)Go to input and output properties.

3)Click on flat file source Output -->Output column --> select (PhoneNo)

4)You may get the properties window for phoneno click on Length property and change the value to 20. Click to Ok.

I wish this may help you.

Answer 3

Your conversion should result in a NEW variable, do not use Phone1. Use the name of the Converted value.

Answer 4

You can change the size of the "output" column (Phone1) in the script component, The default is always 50, manually set it to 20.

Answer 5

this is just a warning.your package will execute anyway. this comes up because the length of source variable and the destination variable is not same. to resolve this warning,right click the destination source,select show advance editor option,select the input and output properties tab,expand the input and output variable and check the length.if they are different then make it same using the data conversion operation.

Answer 6

If all else fails try this.

What I've experienced is that this error shows up occasionally after I've copied and pasted existing Ole DB Destination/Source element either from the same Data Flow Task or a Data Flow Task from another package and then start changing the details within.

Even when the Data Type and Length of the columns are identical, this warning still appears on occasion. This is when I delete the copied element(s) and build them from scratch. This has made the warning disappear.

My guess is that the metadata is fouled up during the copy/paste.

Answer 7

source varchar(50), target varchar(20)

you know the error is looming and are comforable ingnoring it

try the below to see if it helps in your case