September 7, 2011 at 10:53 am
I noticed that others have struggled with the following error message from their SQL Server Maintenance Plans.
Message
Executed as user: \xxxxx_admin. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 1:00:00 AM DTExec: The package execution returned DTSER_FAILURE (1).
Started: 1:00:00 AM
Finished: 2:24:11 AM
Elapsed: 5051.28 seconds.
The package execution failed. The step failed.
However, when I checked the server on which the backups are running, I find that they did run complete and are valid backup files.
As it has been stated many times in this forum, DTSER_FAILURE (1) is the most generic error from the 'old' DTS days and it have never changed. It also does not give a clue as to the real problem. I caught an article on the PRADEEP ADIGA Blog that pointed me in the right directions. I want to share since we are on SQL Server 2008 R2 (Windows Server R2 Enterprise, 64-bit architecture) and still receiving these messages occasionally.
You must be diligent and research the issue from an SSIS perspective. That means you have to export the package in question from the server's MSDB database to your PC where you can do some troubleshooting. When you open the package, at least one of your objects will show an error. That is the one you need to fix. In my case, I deleted databases from the 'Specific Databases...' list in the maintenance plan but SSIS engine did not remove it from the list. So, the underlying SSIS package was still trying to backup databases that no longer existed. Apparently a missing database name in the 'Collection' set (under package properties) is not serious enough for SSIS to throw a 'real' error so it delays its confusion until just before the package completes and then notifies you. You can select to create an output file for all errors coming from the SQL Agent job. Be ever diligent as stated before. I suspect this is just one of the most common issues that can occur. Don't overlook anything that doesn't looks right for what you are trying to accomplish.
You might also gain more insight into the problem by looking in the following folder location after the job execution:
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log. This is, of course, wherever you are writing your log information.
September 7, 2011 at 11:04 am
is that a question or an answer?
Regards
Sushant Kumar
MCTS,MCP
September 7, 2011 at 11:13 am
I was explaining the process I went through to discover the problem. I should have closed by saying the following.
I removed the 'offending' database names from the list of 'Specific Databases..' in the SSIS package and that solved the problem of it throwing invalid messages.
Thanks for pointing this out.
David Wynn
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply