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

More
3 years 2 months ago #13890 by alexm
alexm created the topic: 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.

Please Log in or Create an account to join the conversation.

More
3 years 1 month ago #13891 by Moderator
Moderator replied the topic: Re: SSIS - nvarchar(max) gets unexpected value in Redshift D
We are working on this issue. We'll update this thread as soon as fix is available. Thank you for reporting this issue!

Please Log in or Create an account to join the conversation.

More
3 years 1 month ago #13893 by Moderator
Moderator replied the topic: Re: SSIS - nvarchar(max) gets unexpected value in Redshift D
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
CREATE TABLE tbl IF NOT EXISTS (colX NVARCHAR(MAX), ...)
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.

Please Log in or Create an account to join the conversation.

More
2 years 11 months ago #13896 by Moderator
Moderator replied the topic: Re: SSIS - nvarchar(max) gets unexpected value in Redshift D
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.

Please Log in or Create an account to join the conversation.

Time to create page: 0.139 seconds
Powered by Kunena Forum