Issue Transferring NULL Values to Redshift

Discussion of issues, suggestions and bugs of Devart SSIS Data Flow Components, our product line for building SSIS-based ETL solutions, performing data access to popular cloud applications and databases.
Post Reply
randrew44
Posts: 2
Joined: Wed 28 Oct 2020 13:57

Issue Transferring NULL Values to Redshift

Post by randrew44 » Wed 28 Oct 2020 14:09

I came across an issue when migrating data from SQL Server to Amazon Redshift. If a row in a table has multiple varchar columns which are NULL, only the first column containing the NULL is set to NULL in Redshift, the others are set to blank.

I'm using an OLE DB Source (Native OLE DB\SQL Server Native Client 11.0) as my source, and a Devart Redshift Destination (using Bulk Insert) as my destination.

If I change my destination to an ODBC Destination using the Amazon Redshift driver (version 1.04.01.1001), all columns which are NULL in SQL Server are set to NULL in Redshift, but the performance is really bad.

I've created a support request, but am still awaiting a response, so I thought I'd ask here.

Thanks in advance for any help,

Bob Andrew

randrew44
Posts: 2
Joined: Wed 28 Oct 2020 13:57

Re: Issue Transferring NULL Values to Redshift

Post by randrew44 » Wed 28 Oct 2020 14:56

Wanted to pass along something else. When the operation is changed from 'Bulk Insert' to 'Insert' on the Devart Redshift Destination, it works properly.

Now wondering if this is another issue with AWS. Already have one which sometimes throws the:

"Description: An exception has occurred during data processing, the message returned from the provider is: Error accessing AWS. Error: We encountered an internal error. Please try again."

error. Not able to determine the cause of this one either.

Thanks,

Bob

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Issue Transferring NULL Values to Redshift

Post by Shalex » Mon 02 Nov 2020 13:38

randrew44 wrote: Wed 28 Oct 2020 14:09 I came across an issue when migrating data from SQL Server to Amazon Redshift. If a row in a table has multiple varchar columns which are NULL, only the first column containing the NULL is set to NULL in Redshift, the others are set to blank.

I'm using an OLE DB Source (Native OLE DB\SQL Server Native Client 11.0) as my source, and a Devart Redshift Destination (using Bulk Insert) as my destination.
1.1. A similar issue had been fixed some time ago. Upgrade to the newest build (1.15.1316) of SSIS Data Flow Components for Amazon Redshift. Does this fix the issue?
1.2. In the debug mode, check if OLE DB Source passes NULL values to the input of Devart Redshift Destination.
randrew44 wrote: Wed 28 Oct 2020 14:56Now wondering if this is another issue with AWS. Already have one which sometimes throws the:

"Description: An exception has occurred during data processing, the message returned from the provider is: Error accessing AWS. Error: We encountered an internal error. Please try again."

error. Not able to determine the cause of this one either.
2.1. Check the stl_load_errors table (select * from stl_load_errors) for the errors that occur during BulkInsert.
2.2. Try using the Insert mode instead of Bulk Insert. In this case, there should be a more detailed log that helps to identify the reason for the issue.

Post Reply