CODEWITHSUNDEEP
sql-serverssisetlsql-agent-jobsql-agent
Isha
Isha

Reputation: 121

How to continue executing SSIS SQL job even after task fails?

Scenario The scenario is to connect to multiple POS terminals and fetch data and save to a table in a staging Db. For instance, there are 10 POS terminals, each terminal has different IP and Database name. However the table where the select query is executed is the same. In the SSIS package, foreach loop picks the IP and DB name from a source table, connects to POS DB and save to stage table (lets say, Destination_table). Since foreach loop is used, the connection string is developed using SSIS variables. The package gets executed successfully.

At times, the POS db may not be online or due to network issues, the connection string will fail in middle of for loop, resulting in SSIS package failure. To overcome this, the for each loop properties ForceExecutionResult is set to “Success”. After this, on SSIS package execution, it overrides any connectivity issue and simple continue with the next task in the package and completes execution.

Problem The package when deployed in SQL Agent job, it fails. From the error logs, It was identified that the reason for failure is OLEDB connection error. This means, unlike SSIS manual execution, SQL job is not overriding the ForceExecutionResult property setting.

Question Is there any work around to override such connection issues in SQL and continue the task execution? Can we doe this using event handlers? Or is there any easy property settings that will do the job?

Upvotes: 2

Views: 2612

Answers (3)

MNF
MNF

Reputation: 717

For me, I increased The MaximumErrorCount property. It works fr me.enter image description here

Upvotes: 0

Isha
Isha

Reputation: 121

I did add eventhandlers on error to insert POS ip and few other variable into a table to debug where its going wrong. Eventually, I found that the SQl job is in fact executing for loop without breaking, however once all POS ip connections are visited/completed, the whole job status is set to failure(because some connections failed – guess so).
As a work round, I set the ForceExecutionResult of the package itself to “Success”. Now, the SQL job executed success.

The event handler inserts data “failed POS IP“ to errorlogTable and another execute task after the DFT inserts the “Success POS ip” to errorlogTable. Consequently, the table errorlogTable has all IP listed with status success/failure. This is a bonus for my task, as EOD, I know which all POS needs a manual intervention.

Upvotes: 1

Hadi
Hadi

Reputation: 37313

Assuming that you are using a DataFlow Task inside the ForEach Loop Container, you can try many workaround.

  1. Set the Delay Validation Property of the DataFlow Task, Oledb Connection Manager, to True
    • Add a Script Task before the DataFlow Task (connect it to the DFT)
    • Inside the script task build the connection string and create a new SqlConnection object (must Imports System.Data.SqlClient) and try to open this connection inside a try catch clause,
    • If it is open successfully then return a Success result, else return a failure.
    • Set the Script Task property FailPackageonFailure and FailParentonFailure to False

    • Below an example of the Script you can use:

      Public Sub Main()

    Dim strConnectionstring As String = .....

    Using sqlcon As New SqlConnection(strConnectionstring)

        Try

            sqlcon.Open()

            Dts.TaskResult = ScriptResults.Success
        Catch ex As Exception
            Dts.TaskResult = ScriptResults.Failure
        End Try


    End Using



End Sub

Upvotes: 1

Related Questions