Allowing a DBO to manage SSIS packages (SQL 2008)

We have developers that manage their own application's databases (dbo) and SSIS jobs.  In order to give them "least priv" to SQL 2008, this is what I've been doing to give them rights to manage their database(s) and their SSIS jobs on SQL server.

• Add user admin account to local "Distributed COM Users" group
• Give "Distributed COM Users" group remote access to the DCOM config for SQL; see http://systems808.blogspot.com/2010/07/access-denied-to-ssis-remotely.html
• Give user admin account access to msdb database with the following rights:
• DatabaseMailUserRole
• db_ssisltduser
• public (default)
• SQLAgentOperatorRole
• SQLAgentReaderRole
• SQLAgentUserRole

Hope that helps!


For SQL 2012+, see:
https://docs.microsoft.com/en-us/sql/integration-services/service/integration-services-service-ssis-service?view=sql-server-2016

Unable to start execution of step 1

On SQL 2005, I created a simple database and log file backup job using the Maintenance Plan wizard and scheduled it, but it does not work. When I run it, it generates an error:

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

I can't believe MS would have this kind of bug. Something generated by a wizard needs to work. PERIOD.

Anyway, the fix for me was this:

1. Go to SQL Server Agents\Jobs. Select job and click Properties.
2. Go down to Steps
3. Highlight your SubPlan and click Edit
4. Add a Backslash to the beginning of the path listed in the "Package" box

And, like magic, no error and job runs.

Hope that helps!

Access Denied to SSIS remotely

Note: These steps are performed on the remote server that you cannot connect to:

• If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.
• Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.
• Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.
• Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.
• Right-click on MsDtsServerXXX and select Properties.
• In the MsDtsServer Properties dialog box, select the Security tab.
• Edit "Launch Permissions" to include the Distributed COM (DCOM) user group with
• Remote Launch
• Remote Activation
• Be sure to customize “Access permissions” to include the Distributed COM (DCOM) user group with "Remote Access"
• Make sure all remote access boxes are checked appropriately
• Restart the SSIS Service after making these changes.

Update 1/12/2011: When deploying remotely, make sure you specify "sqlserver\instance"
Reference: http://msdn.microsoft.com/en-us/library/aa337083.aspx

Step by Step SSIS Package

Reference: http://technotes.towardsjob.com/sql-server/steps-to-create-and-deploy-ssis-package-as-a-sqlagent-job/

Configuring SSIS

SSIS is not cluster-aware so installing to a SQL 2005 cluster is problematic. I'm running an Active/Active, two instance, two physical server Windows 2003 cluster so I installed SSIS on both nodes and configured to reference MSDB on both virtual server\instances. I did not create anything within cluster groups for SSIS.

Reference: http://msdn.microsoft.com/en-us/library/ms137789(SQL.90).aspx