Scheduled Job Fails if SSIS Package Step Fails
I have a scheduled job in SQL Server 2012 that runs an SSIS package, created in Visual Studio 2012. The first step in the package is to import an .xml file into a database table. The package is designed so that if the file is not there and that step fails, an e-mail is generated. This all works just fine. My issue is that the scheduled job fails if that package step fails, even though the package actually ran as designed and did not report failure. The MaximumErrorCount on the package is set to 2, to allow for this one planned failure.
In the job step properties, "Fail the package on validation warnings" is NOT checked.
I don't want the scheduled job to report failure if the package succeeded. How can I get this set up? The error I get in SQL Server Agent Job Scheduler is below.
Executed as user: [Domain]\[Username]. Microsoft (R) SQL Server Execute Package Utility Version 11.0.5058.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 2:36:37 PM Error: 2014-12-04 14:36:40.51 Code: 0xC020F440 Source: Import XML Data to RA_XML Table XML Source [127] Description: The file "\\ComputerName\webcontent\ftproot\RA\Filename.xml " was not found. Please verify the file path and try again. End Error Error: 2014-12-04 14:36:40.52 Code: 0xC004706B Source: Import XML Data to RA_XML Table SSIS.Pipeline Description: "XML Source" failed validation and returned validation status "VS_ISBROKEN". End Error Error: 2014-12-04 14:36:40.52 Code: 0xC004700C Source: Import XML Data to RA_XML Table SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2014-12-04 14:36:40.52 Code: 0xC0024107 Source: Import XML Data to RA_XML Table Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:36:37 PM Finished: 2:36:40 PM Elapsed: 2.688 seconds. The package execution failed. The step failed.
