Remotely connecting to SQL Server 2008 Integration Services – Access is denied error.

Very annoying error and I haven’t seen clear concise instructions for getting this to work. I will attempt to do that here.

1. Open Administrative Tools > Component Services
2. Navigate to DCOM Config and open the properties for MsDtsServer100
3. Click on the Security tab
4. Add the appropriate users/groups that you need and assign their permissions to BOTH “Launch and Activation Permissions” AND “Access Permisions” (Both of whom, select Customize then Edit)
5. Click Ok to save changes and close those windows.
6. Right click on Computer, select Manage.
7. Navigate to System Tools > Local Users and Groups > Groups
8. Double click on Distributed COM Users
9. Add the users/groups you wanted to give permissions to.

Advertisement

Troubleshooting SSIS – The component could not be added to the Data Flow task.

So if you are getting this error creating an SSIS package and you happen to be testing out a new component; these are the things to do:

1. Check the name, version, and SNK (Strong Name Key) between BIDS, GAC (General Assembly Cache), and in the components source.
2. If more than one version exists in the file location or in the GAC, delete all entries and reinstall the correct version.
3. Restart the SQL Integration Service.
4. Delete the component (in the package) and reset the toolbar in BIDS.

Hope this helps. I applaud the SSIS beginners, experts, and gurus out there!