SSIS Package fails from SQL Agent

I've been configuring a test version of one of our Virtual Servers, which was created by cloning the 'Live' server using VMWare. I managed to negotiate round the @@Servername issue , and made sure to rename the databases so that we were quite clear that we were on the test server.

It took a couple of days, and the addition of the new server into the SSIS package which checks for failed jobs, before I noticed that the Scheduled Maintenance Plans to rebuild indexes were failing on the new server: because, in part, they were trying to connect to the old server. So, I changed things around, pointed the plans at the new server, waited 24 hours, and still they failed.

Odd.

There was nothing in the job history for the Maintenance Plan, and only this in the Job History Log (servername blanked to protect the guilty).

"Unable to start execution of step 1 (reason: line (1): Syntax error). The step failed".

A similar job gave the error:

"The command line parameters are invalid. The step failed." Again, no errors...

Looking inside the job, the command line seemed reasonable, giving, as it did "/SQL "Maintenance Plans\Weekly Check Integrity and Rebuild Indexes" /SERVER Servername /CHECKPOINTING OFF /SET "\Package\Weekly.Disable";false /REPORTING E"

What I should have done next was run that in sqlcmd. What I did was start searching Google. I found a helpful discussion thread, and peered at the SQL Agent Job Step.

Now, given that the job had been scheduled via the Job Scheduler within the maintenance plan, all should have been well. However, it wasn't. There was a missing "\" in the Maintenance Plan path.

Browsing to the package using the button with the three dots to the right of the Package Path made sure I was looking in the right place: and, to my surprise, the Package Path changed every-so-slightly once I'd clicked OK.

A backslash appeared, and the job ran successfully.

Now, there are other reasons why an SSIS package would fail when called from a SQL Agent Job, and Microsoft describes them here: they apply regardless of the Service Pack you have on your SQL 2005 instance.

As far as I can tell, however, the issue I've described here only applies to SQL 2005 RTM. The server is on the list to be service packed, and, now that we have the test version, we can check that the third party software won't throw a wobbly before applying it to live.

All's well that ends well....

SPFun

Or what I learned about Service Packs.

A SQL Maintenance Plan job had been running fine on my SQL 2005 RTM machine for months: until I joined the company, and made the mistake of having a look at it and doing some tidying. Next thing I know?

"Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed.".

"But, but, but, but!" I cried "I've just stopped it faffing about with Adventureworks, and removed that database from the server. I haven't touched the syntax directly, I dealt with this in SQL Management Studio. The syntax, surely, should be fine?"

I shrugged, recreated the job... and the same thing happened the next time it ran.

Hmmm.

It would seem that this particular error happens when SP2 is not yet installed on one's SQL box, and one edits a job via a newer version of SSMS. If one plays about with the job on the box, the problem is solved. Or, if one does the sensible thing, and installs the service packs.

I foresee some out of hours work for us here.