This blog post is about one the causes of failure on SQL Agent Jobs that have Maintenance Plans inside with SSIS Packages ‘SQL Server Integration Services templates for developing SSIS projects’. These packages are the real jobs inside a maintenance plan.
The SSIS ‘project’ Packages are developed with BIDS ‘Business Intelligence Development Studio’ BIDS is the tool that we use to develop SSIS packages and it is included within SQL Server. At this point it can be difficult to diagnose and fix a plan that keeps consistently failing with no logging in the windows event viewer or agent log.
In my case we migrated SQL 2008 databases /32 bit packages to SQL 2012 and created new maintenance plans “Reorganize Index Task and a couple of sub jobs” The failure can be happening in any type of plan. Even though the return of the package was set to verbose, all I could see was the logs from the start of the execution, and not the error message itself.
So I went back and modified the plan in single scripts, and started to run this script in batches. The first executions ran fine, but then while running the script from the reorganize index task, one error message appeared:
Message: The job failed. The Job was invoked by User domain\testuser. The last step to run was step 1 (Subplan_1).
At this point I noticed that we have a 32 bits runtime vs 64 bits runtime mismatch in the packages because we migrated from a 32 bit system to 64 bits and the packages are from a 32 bits system. It can be difficult for a database administrator to see/check this. So we changed the setting within the job definition itself to solve this issue by checking the Use 32 bit runtime checkbox.
For a better solution in the future we will migrate the SSIS packages to T-SQL.