SSIS – “Access is denied” when connection to remote SSIS service

 SSIS Security  27 Responses »
Apr 132007
 

A fellow MVP, Jeff Johnson, has been having some issues (Access is Denied) with respect to connecting to a remote SSIS service. Books Online does not, apparently contain complete information. (Reference: http://msdn2.microsoft.com/en-us/library/aa337083.aspx)

Bottom line, a few steps should be added to that document to ensure your users can connect. (The root problem is the need to keep the user out of the server’s local administrator group.)

Instead of documenting them here, I’ll just point you to the thread that contains the complete picture. http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/24eed7bd-b21a-4c64-9af2-8d0756ef727d/

In summary:

  • Note: These steps are performed on the remote server that you cannot connect to
  • Edit Launch Permissions to include the Distributed COM (DCOM) user group
  • Be sure to customize “Access permissions” to include the Distributed COM (DCOM) user group
  • Make sure all remote access boxes are checed appropriately
  • Add the user account/group to the DCOM user group
  • You may need to restart the SSIS Service after making these changes — in fact, I recommend it.

SSIS – Dynamic substrings based on control table

 SSIS Advanced Techniques  5 Responses »
Apr 062007
 

Last night a user posted to the SSIS Forum a situation where he needed to be able to dynamically substring one field based on the substring rules contained in a table. So I put together a package that does just this. Before we go there though, I just want to mention that there are many ways, progmatically of course, to tackle this problem. The example below strictly follows my interpretations of Bill’s challenge. There is a better way by using the split() function, but never-the-less here’s the example using substring().

Continue reading »