How to Fix the SSIS Error 'Cannot Convert Between Unicode and Non-Unicode Strings'

While adding a new step to our data synchronisation, I run into this error message from SQL Server Integration Services (SSIS):

Column "MyColumn" cannot convert between Unicode and non-Unicode string data types.

The problem was that the CONCAT function creates varchar strings while I needed a nvarchar type to synchronise the data:

1
CONCAT('a', 0, 'B') AS 'MyColumn'

To fix the problem, I could add a N in front of all strings to switch from varchar to nvarchar like this:

1
CONCAT(N'a', 0, N'B') AS 'MyColumn'

Since I had more parts than in this example, I decided to convert the final string with a cast to nvarchar:

1
CAST( CONCAT('a', 0, 'B') as nvarchar) AS 'MyColumn'

This solved my problem, and I hope this helps you as well.