SSIS: Excel destination error message 'cannot convert between unicode and non-unicode'

GOAL: move data from SQL Server to Excel with SQL Server Integration Services.

PROBLEM: the package doesn't work and you receive the error "cannot convert between unicode and non-unicode".

image.png

SOLUTION

  1. Add a Data Conversion task between Source and Destination in your Data Flow Task.
    image.png
  2. Inside the Data Conversion task convert all the string field to "Unicode string [DT_WSTR]":
    image.png
  3. In the Mappings page of the Excel destination task, remap the fields taking the new converted columns instead of the original ones from the Available Input Columns.
    image.png