The error in CData SYNC "value too long for type character varying(2000)" is a common issue that occurs with RedShift as destination in Sync that is related with the value for the source that is longer than what the destination is expecting or accepting.
NOTE. Redshift's varchar column data type is based on the number of bytes and not characters.
With that being said, the "BytesPerCharacter" property within the "Sync" feature should multiply the column width in Redshift in a corresponding manner. Therefore, when this property is properly configured, you should not encounter any issues.
It is important to note that the "BytesPerCharacter" property exclusively impacts data replication to destinations such as Redshift, DB2, and Oracle.
To configure this property, please navigate to "Additional Options," then select "Advanced" and proceed to "Job Settings." In this section, set the property "BytesPerCharacter" to a value of 2 or higher, as the default value is set to 1. For more comprehensive information on this configuration, please refer to https://cdn.cdata.com/help/ASJ/sync/Advanced-Job-Options.html