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
Issue Transferring NULL Values to Redshift
Re: Issue Transferring NULL Values to Redshift
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
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
Re: Issue Transferring NULL Values to Redshift
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?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.2. In the debug mode, check if OLE DB Source passes NULL values to the input of Devart Redshift Destination.
2.1. Check the stl_load_errors table (select * from stl_load_errors) for the errors that occur during BulkInsert.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.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.