Hello,
We are using the Redshift OLEDB provider version 1.4.0.3392 in our SSIS ETL process.
However, we found that the Execute SQL task does not fire an error when there are multiple statements in the batch and the error occurs not in the first statement.
Example:
Create a test table in Redshift:
Code:
create table test
(
testdate datetime not null,
testvalue int not null
);
Create a simple package with one Execute SQL task which uses Redshift OLEDB provider in fastload mode:
Attachment 474_28fe335fcfbd780b8eea6630cb2be227.jpg not found
Redshift connection string: Data Source=xxxx.redshift.amazonaws.com;User ID=xxxx;Initial Catalog=dev;Provider=SQLOLEDBRSNP.1;Persist Security Info=True;Extended Properties="SSL=require;AWS_BUCKET=dev;xxxx;AWS_SECRET_KEY=xxxx;COMMAND_TIMEOUT=600;";
Put the following code into the task. The code fails when executed in Sql Workbench because null value is supplied for a non-nullable column testvalue in the 2nd insert:
Code:
BEGIN TRANSACTION;
insert into test(testdate, testvalue) values (getdate(), 1);
-- error expected:
insert into test(testdate, testvalue) values (getdate(), null);
COMMIT;
After the package is executed, the Execute SQL task reports success:
<!-- ia0 -->2015-11-24 11_24_47-Data WareHouse (Running) - Microsoft Visual Studio.png<!-- ia0 -->
No row in inserted into the table which indicates that the transaction is actually rolled back.
Can anything be done to make the error fired?
Thank you very much in advance.
