SSIS - nvarchar(max) gets unexpected value in Redshift DB

Dear Support,

Hope you are well today! We logged this issue via email earlier and would like to follow up.

We are experiencing issues loading SQL Server nvarchar(MAX) columns into RedShift. We use SSIS and PGNP OLEDB Provider for RedShift in Fastload mode.

Some info on the source data:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

BIDS Visual Studio 2008, Version: 9.0.30729.4462 QFE
PGNP Version: 1.4.0.3420

Data loading details:
We simply use SQL Source + Derived column to add a global variable as a column + OLEDB Destination.
We use "Table or View - Fast Load" option in SSIS for the Destination.

Results:
In SQL Server in nvarchar(max) column the values is: "cooking, traveling"
In the RedShift destination database we get: "c\000o\000o\000k\000i\000n\000g\000,\000 \000t\000r\000a\000v\000e\000l\000l\000i\000n\000g\000"

So data inserted with "\000" after each character.

Can you please kindly review this issue and advise for a workaround or a solution.

Kind Regards,
Alexander.

We are working on this issue. We'll update this thread as soon as fix is available. Thank you for reporting this issue!

We have reproduced the issue, and found that it is caused by the wide-characters passed by the SSIS. The OLEDB provider treats all incoming streams as containing short-chars (by historical reasons). So, if we change default behavior now, it would affect existing projects that are already working. So, we suggest using a "hint statement" in the data flow that would inform the OLEDB provider how to interpret incoming streams for certain table. The "hint" is Note, that the column uses type NVARCHAR (not VARCHAR) for the wide-chars.
This workaround is described in more details in the Developer's Manual available from Download page on our web site. See section 3.10 "Hinting" Statements.

Update: since we received a lot of requests to address this issue, we introduced Extended Properties parameter STREAM=WIDE that changes the default behavior when handling ISequentialStream. Please obtain build 3440 or later, and add STREAM=WIDE.