Centralized SSIS Server?

  • Has anyone every created / managed a centralized SSIS server?

    We have a virtualized environment in which SSISDB is forbidden for "reasons." We have hundreds of application teams of which probably a dozen or so use SSIS, with the packages being stored on NAS shares. When I brought up the issue of business continuity / DR planning, one of our managers asked if maybe we can create a centralized server for everyone to use.

    I've been doing a little research. As far as I can tell, once permissions are given in SSISDB, those permissions apply to everyone, similar to msdb permissions. There's no way to segregate teams unless we do different instances for everyone. And there's a max of 50 instances on a server. Maybe that will be enough, maybe not.

    Does anyone have any thoughts or links for creating a high availability scenario that centralizes SSIS in one place that can be managed for multiple teams?

    Are my assumptions wrong about the permissions?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • if you use SSIS Catalog then you can specify permission at a project level and at a folder level.

    see https://learn.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-ver16#Permissions

    other permissions can be set at ssisdb level but those would be global

    so it would be possible to have multiple teams working on the same server without being able to access/modify items belonging to other teams.

    you can also (and should!!) setup multiple sql agent proxies/credentials so each team can (and is restricted to) use a account that has access only to their databases/file system folders and shares.

    a bit of planning and testing would be required - and likely a big server (and therefore expensive license wise) would be required.

    and whoever prohibited SSIS catalog should go through some education to elucidate them on the benefits of it versus filesystem packages.

  • Hello this is Gulshan Negi

    Well, creating a centralized SSIS server for multiple teams is possible but requires careful planning and consideration of the limitations and potential challenges. Some options to address these challenges include using multiple instances of SQL Server, third-party tools for management and scheduling, and implementing high availability solutions such as failover clustering or Always On Availability Groups.

    Hope it can give you an idea.


  • You also need to consider who and how agent jobs get created and managed - it is possible but also needs planning up front to ensure that each team is not able to affect other teams jobs.


    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • An instance of SQL Server designated as a Central Management Server maintains server groups that contain the connection information for one or more instances. You can execute Transact-SQL statements and Policy-Based Management policies at the same time against server groups.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply