Backup

Some more steps are required to be included in the backup packages because the SSIS file system store packages are actually stored in the file system. Even packages stored in MSDB within the SQL Server might have, depending on the package, data files or some configuration stored in the file system.

Backing Up SSIS File System

There are procedures for backing up and restoring the SQL Server 2005 Environment. However in addition thereto, backing up the file system is also critical. The SSIS file system store directory is %Program Files%\ Microsoft SQL Server\ 90\ DTS\ Packages\. This is also to be backed up. All the packages stored in the SSIS file system package store are backed up if the above directory is backed up.

Security Features

A number of security features are supported by SQL Server Integration Services. The entire contents of the packages are protected from unauthorized execution, modification and sensitive information by these security features. For verification of the authenticity of the packages, besides roles and protection levels, packages can also be signed with certificates. In addition to security for packages provided by SSIS, there are other areas of security relating to packages that deserve attention. Data, configuration and log files are often used in packages. However the SSIS security mechanisms cannot protect these files. The locations of all these files should also be protected to prevent exposure of confidential information. This is typically done at the operating system level through the ACL controls and the Encrypting File System (EFS). There are three database roles in SSIS to control accessing of packages. The database roles can be broadly divided into three categories- administrator, user and operator. User defined roles can also be created if there is a need for more granular rights assignment. The fixed security roles are db_dtsadmin or sysadmin, db_dtsltduser, db_dtsoperator and Windows administrators. They have different Read and Write actions.