SSIS Package does not fail, force failure timout with alert

Question

Good day,

I have a ssis package within an SQL Agent Job, 2 things that fails on this pkg

1. Oracle connection, continously tries to reconnect and not fail
2. second part imports the oracle data within sql tables

Part1 would not find a connection and will run forever, second part also does not fail

Is there a way to set timer limit on pkg step to rather fail and email/sms me, i know the alert operator and gateway.
Perhaps in the pkg events to force failure of pkg
I am busy setting up another job to monitor steps times but this is difficult as a step can run1min to 2 hours, how does one determine it failed or hanging

Please any deas!
Regards

Answer 1

Hi @Ismail Cassiem ,

1. We can set Event Handler OnError Event in ssis package.
   
2. Please view the job history log.
   a. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
   b. Expand SQL Server Agent, and then expand Jobs.
   c. Right-click a job, and then click View History.
   d. In the Log File Viewer, view the job history.
   e. To update the job history, click Refresh. To view fewer rows, click the Filter button and enter filter parameters.

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thank You very much for assisting!

The issue is the pkg randomly runs for like forever and not fail, i dont want to force failure if it successfull but only when it runs past x time.

I am thinking of setting the task in its own Job and then set a timer with a notify operator to sms

1. how do i set a timer to a job and force failure?
2. how to notify send sms, not email to sms has i dont have that service "not provided" its a cost.

Please Help!