sql-servervisual-studiossissql-server-agentssis-2019

SSIS Job randomly succeeding, usually resulting in "Unexpected Termination"?


The Job looks like this, with a Script Task checking a directory for an (.xlsx) file of interest available for processing, and if found, marks a boolean variable for Precedence Constraint as {True}. That filename gets assigned to a variable and used as a Dynamic Expression for the Excel Connection Manager, with each Data Flow Task handling a different sheet in the workbook:

enter image description here

Here is more detail of the Script Task:

enter image description here

enter image description here

The job executes and runs fine as expected in Visual Studio 2019/2022, and is package-deployed to SQL Server 2019. When ran through a SSIS Job scheduled hourly using a Proxy User credential with appropriate access to all locations, the job will usually 'not succeed', with a resulting status of "Unexpected Termination" as seen below for 10:00AM and 11:00AM. Then randomly at 12:00PM it worked.

enter image description here

Additionally - With no file present at the location, I would expect the script task to not find a file, and succeed the job after the Script Task, ignoring all further tasks when the precedence constraint is not met. But is instead resulting in a "Failed" status. When I put the file back in the input folder location and triggered the SSIS job ad-hoc, it again fails with "Unexpected Termination".

I have another package/step in the same hourly SSIS Job in this exact same setup - Script Task assigning variables, precedence constraint, etc. using the same Proxy Credentials that runs fine as expected, succeeding every time by stopping processing if no file is present or processing the file as expected.

enter image description here

Here is more details of the most recent "Unexpected Termination" execution.

enter image description here

All Validation Phase messages appear to be populating for the additional data flow tasks, and the only warning beyond two unused output columns that could be removed is one reading:

enter image description here

Could it have anything to do with the Execution Path showing as "Package1" even though the package has been renamed? Does anyone have any ideas? Thanks!

Edit:

Query results for billinkc query:

enter image description here

Detail view of the Membership Data Flow Task - reads data from spreadsheet, gets rid of any nulls included in flow, converts types as need be for Oracle backend, then does a Lookup to determine if a new record for Insert (OLE DB Destination), of if a match, does an Update (OLE DB Command):

enter image description here


Solution

  • I do not have a solid cause-effect resolution to this question, but I do have an update. In essence I did two things:

    1. Like mentioned in comments above by @billinkc, I clicked the [Control Flow] canvas of the package and changed the name from "Package1" to "Update Actuarial".

    2. To at least reduce the "Failure" status when file does not exist in folder for processing, clicked each of the Data Flow Tasks and changed DelayValidation to True.

    I had changed the job to run every 5min for debugging instead of hourly. After making these changes above, re-deploying the individual package to my host server, I tried running the Hourly Job manually starting with Step 2 (this package), and initially it failed.

    However, the next scheduled run (every 5min) succeeded though, and every run after has started working as intended; processing the file when it exists at location, and ending "Successful" after Script Task if there is no file in directory for processing.

    Thanks all for looking at this, and hope this information helps someone in the future!

    enter image description here