SQL Integration Services Access

Once upon a time, a long time ago, in SQL 2008, all users had access to connect to Integration Services in Management Studio. Naturally, SQL 2012 does it differently, and you have to explicitly grant access via Component Services

1. Run Dcomcnfg.exe. This lets you edit the registry.
   
2. In the Component Services dialog, expand
   Component Services --> Computers --> My Computer --> DCOM Config .
   
3. Right-click Microsoft SQL Server Integration Services 11.0, and then click Properties.
4. On the Security tab, click Edit in the "Launch and Activation Permissions" area.
   
   
   
5. Add users and assign appropriate permissions, and then click OK. If you don't like your users remoting onto the server, chose 'remote launch' and 'remote activation'
   
   
   
   
6. Repeat steps 4 - 5 for the "Access Permissions" area. Again, grant remote access if you don't allow users to log directly onto the server.
   
   
   
   
   
   
7. Restart the Integration Services Service in SQL Configuration. Restart Management Studio if you had it open, and were trying to connect from that.
   

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....