SSIS - Error not fired for Execute SQL Task

7 years 3 months ago #13870 by alexm

We are using the Redshift OLEDB provider version 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.

Create a test table in Redshift:
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;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:
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.

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

7 years 1 month ago #13883 by Moderator
Hi! Thank you for bug report! We have fixed this bug in build 3418. The new build will be released shortly. To obtain a build before official release please contact our Support.

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

6 years 11 months ago #13894 by alexm
Thank you, confirmed that it works as expected now.

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

Time to create page: 0.162 seconds
Powered by Kunena Forum