There are a number of functions that SQL server integration services (SSIS) fulfil, and each of these roles adds to the appeal of making full use of what the process has to offer.
To get you up to speed on the general purposes and key elements of SSIS, this basics guide is a good place to start. For a quick look at the relevant roles themselves, read on.
Security
The first thing that is worth establishing is that the term ‘roles’ can be applied generically to SSIS in order to identify its broader benefits, while also having a specific meaning in this context. Roles themselves are database-level security principals that determine how permissions are handled across the operating environment.
Because of this, they are of course capable of managing access and providing protection to mission-critical data. This is achieved through permissions, which can be assigned to individual SSIS packages as needed, or applied more generally to an entire database.
Without server integration service roles being used effectively, sensitive information could be put at risk, especially as it is being transported from its source to its destination, leaving it vulnerable unnecessarily.
The top-level security tools will be available to those who have ssis_admin privileges within the server environment, and you can provide this to members of a particular group, rather than solely laying all the power at the hands of an individual.
Authentication
Of course security is not a binary choice in an SSIS context, in that users need not either have complete access to all packages and data, but can be afforded appropriate levels of contact and influence as required.
Permissions can be granted or denied across specific actions and, perhaps most importantly, the user who grants the permission will also be recorded, so that this type of interaction is traceable.
For example, roles can be used to define whether a user has the ability to create, modify, execute and read objects themselves. They can even be allowed to manage permissions, or of course be prevented from doing so.
The result is that SSIS roles can empower you to not only make sure that data is well protected, but also allow SSIS to be accessible to an entire hierarchy of users with precision.
Additional roles
Aside from the SSIS-specific role of administrator that has proven to be an important asset in recent years for any SQL server implementation, there are also a number of other database roles worth considering.
This includes administrators and operators governing everything from the process of creating backups of data to making minor or major alterations alike. Ultimately all of these roles are about server security, albeit on the level of user management, permissions and access controls alone. They should be used as part of a wider security strategy which allows SSIS operations to be completed without taking any compromises, including through considerations of the physical protection that is afforded to server hardware and the network infrastructure over which it is made accessible
