'SSIS Execute SQL Task continue execution after completion of Oracle stored procedure

I have an Oracle stored procedure I am trying to execute in SSIS Execute SQL Task. This stored procedure involves refreshing materialized views. For each and every step completion we are logging it in a Audit table. I see the stored procedure completes and the final step log is entered in the Audit table but the Execute SQL Tasks continues to execute.

This is causing the job to run forever and not returning completion status. I checked with my Oracle DB to see if any session is active after the last step completes and he said he see no active session, which means no issue on database side.

Any idea what could be the issue?

sql-serveroraclestored-proceduresssis


Solution 1:[1]

You can Debug your SSIS Package in Visual Studio to see what's going on. You will need "SQL Server Integration Services Project" software for this. You can install it from VS MarketPlace OR download it separately from the below link. This works for VS 2019 but may work for other versions as well.

https://marketplace.visualstudio.com/items?itemName=SSIS.SqlServerIntegrationServicesProjects

More information about debugging SSIS Packages can be found at below links:-

  1. https://docs.microsoft.com/en-us/sql/integration-services/troubleshooting/debugging-control-flow?view=sql-server-ver15

  2. https://www.mssqltips.com/sqlservertip/4543/debugging-control-flow-in-sql-server-integration-services-part-1/

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gaurav Shah

Related Questions