Someone in the SQL communities had an issue with the Backup Maintenance Plan. The Maintenance plan would backup the database but fail with the below error.
DTExec: The package execution returned DTSER_FAILURE (1)
It was a simple Maintenance Plan which would take the database backups to disk and delete the files older than 2 days. Everything looked good when the Maintenance Plan was opened in SSMS. I remember having the same issue where in a Maintenance Plan was configured to backup “SELECTED” databases and one of the databases was dropped later on. The Maintenance plan would backup the databases but will fail since it was still looking for the dropped database. However there is no mention of this error anywhere in all the logs. Let me try reproduce the same.
On my system I created a new database named DropMe and created a new Maintenance Plan to backup this database along with others.
After the Maintenance Plan has been created, I drop the DropMe database. Then the next execution of this Maintenance Plan failed with the same error. The problem with this is that there is no clue anywhere as to why it is failing.
I opened up SQL Server Business Intelligence Development Studio and opened the Maintenance Plan inside a new Integration Services project.
After this package was imported into the project, when the mouse was hovered on the Backup Database Task, it showed up the error message.
I had done all this just to get a proof of the job failure. Now we know why the Maintenance Plan was failing, how to fix it? It is very simple. Open the Maintenance Plan in SSMS and just click on Ok in the Backup Database Task.
Save the Maintenance Plan and the error message vanishes! Look out for this whenever a Backup Maintenance Plan fails with a generic error.