SSIS:Unicode data is odd byte size for a column Should be even byte size

2
votes

I am running an SSIS package that pulls data from one SQL server to other SQL Server. The source and destination table has same schema. One of the column is of nvarchar(max) datatype. When I'm trying to insert the data - it is giving me following error for the nvarchar(max) type column-

Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Unicode data is odd byte size for column 13. Should be even byte size.".

Can someone help me to resolve this issue?

2
sql-serverssisetl
It is a known bug using Nvarchar Max gives this error, try using a fixed length instead of Navarchar Max or use integer type – Ven
Use a derived column expression and change it to fixed length instead of changing data types in source and destination. – Ven
@BHouse Thanks for the update. I've created a derived column and it worked! – Rameshwar Pawale

2 Answers

0
votes

NVarchar stores 16bit =2byte characters.

The size of NVarchar columns is specified in bytes.

So, if you need to store 13 character data in an NVarchar column you need to set it's size to 26.

This allows you to store the common characters from around the world.

0
votes

Try adding a Derived Column with the following expression and map it to the destination column

(DT_WSTR,4000)[column name]

Else, check the following article it contains useful information: