JavaNewbie JavaNewbie - 9 months ago 35
Java Question

How to deal with long asynchronous stored procedure called from spring server?

I use

spring-boot
,
mybatis
and I execute an asynchronous stored procedure. It is asynchronous because it is a procedure from
SSIS
package (catalog schema) - these procedures are asynchronous by default.

The problem is that before executing the stored procedure, I modify some value in the database. Due to the fact that it is asynchronous, annotation
@Transactional
doesn't help me.

The thing that I am worried about is failure of executing this package (SSIS catalog -
start_execution
). It this case I should revert the previous value in database (one value, but it doesnt matter here).
However,
@Transactional
can't help me because as you know
spring
think that the procedure finished successfully and it accepts the transaction.

How to deal with this problem - I mean rollback in case of failure of execution of a ssis package ?

Edd Edd
Answer Source

According to this post, you can run a SSIS prodecure synchronously using the right parameter:

[...] You have to be sure that you add the “SYNCHRONIZED” parameter to the package execution. Before the start_execution procedure:

exec [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1

At this point, you should be able to wait for the completion of the procedure as usual and handle rollback normally.