Integration Services (SSIS) Server

The server stores historical data about operations that occur on the server. By default, the server stores this historical data for a retention window of one year (365 days). If you enable SQL Server Agent, a scheduled job runs daily to delete historical data that is older than the retention window.

Note
When you install SQL Server with the default settings, SQL Server Agent is not enabled. If you do not enable SQL Server Agent, the scheduled job cannot run and historical data will not be purged.

You can specify a retention window that differs from the default value. If you change the retention window, follow these restrictions:

• The minimum value is 1 day.

• The maximum value is limited only by the maximum value of the SQL Server int data. For information about this data type, see int, bigint, smallint, and tinyint (Transact-SQL).

• 0 (zero) is not a valid value. You cannot disable retention completely.

You can also retain historical data indefinitely. To enable indefinite retention, use one of the following methods:

• Clear the Limit storage of historical data check box in Management Studio.

  —or—

• Specify -1 as the value of the RETENTION_WINDOW property when you call the catalog.configure_server stored procedure.

For information about how to view the historical data that the server retains, see the section, Viewing the History of Operations on the Server, in this topic.

The server encrypts the values of all configuration items that the server stores. For information about configurations and configuration items on the server, see Package Configurations.

Note
Packages are not encrypted. Instead, the access control features of SQL Server and of Integration Services protect packages and the data that they contain.

The default encryption algorithm is AES256. However, you can change the encryption algorithm to one of the other supported algorithms:

• DES

• TRIPLE_DES

• TRIPLE_DES_3KEY

• DESX

• AES_128

• AES_192

• AES_256 (default)

For information about encryption and encryption algorithms in SQL Server, see the topics in the section, SQL Server Encryption, especially the topic, Choose an Encryption Algorithm.

Changing the encryption algorithm is a time-intensive operation. First, the server has to use the previously specified algorithm to decrypt all configuration values. Then, the server has to use the new algorithm to re-encrypt the values. During this time, there cannot be other Integration Services operations on the server. Thus, to enable Integration Services operations to continue uninterrupted, the encryption algorithm is a read-only value in the Integration Services Properties dialog box in Management Studio. To change the encryption algorithm, you must call the catalog.configure_server stored procedure and change the algorithm directly.

Important
Before you can change the encryption algorithm, the database must be in single-user mode. For more information, see Set a Database to Single-user Mode.

Validating a package can be a time-intensive operation, especially when many connections have to be opened to external data sources. To avoid long delays in Management Studio, you can specify a time-out (in seconds) after which validation stops. If the time-out is reached while validation is in progress, validation stops and an informational message is displayed to the user.

By default, the validation time-out is 300 seconds (5 minutes). However, you can change this value. If you change the validation time-out, follow these restrictions:

• The minimum value is 1 second.

• The maximum value is limited only by the maximum value of the SQL Server int data type.

• 0 (zero) is not a valid value. You cannot disable the time-out completely.

To change the validation time-out, call the catalog.configure_server stored procedure.

Note
Currently, you cannot see or change the validation time-out in the Integration Services Properties dialog box in Management Studio.

For more information about how to validate packages, see the section, "Validating Packages," in the topic, Integration Services (SSIS) Server.

You can import packages to the server, or export packages from the server, by using the Integration Services Deployment Wizard.

• You can import packages from the file system, from another Integration Services server, or from the msdb database.

  When you import packages from the file system to the server by using the Deployment Wizard, you also import the configurations that are referenced by the packages. However, when you import packages from another Integration Services server or from the msdb database by using the Deployment Wizard, the configurations that are referenced by the packages are not currently imported.

• You can export packages to the file system, or to another Integration Services server.

  However, when you export packages to another server or to the file system by using the Deployment Wizard, the configurations that are referenced by the packages are not currently exported.

  You cannot use the Deployment Wizard to export packages to the tables in the msdb database where earlier versions of Integration Services stored packages.

For information about using the Deployment Wizard, see Deploy Packages by Using the Deployment Utility.

You can view the properties of an individual package on the General page of the Package Properties dialog box in Management Studio. The General page displays the name, description, package format, and version of the package, and the date and time when it was last deployed to the server. All properties on the General page are read-only.

For information about the other methods that you can use to view the properties of a package, see the following topics.

Transact-SQL access

To retrieve a single package and its properties, call the stored procedure, catalog.get_package (Integration Services Database).

To view packages and their properties, query the view, catalog.packages (SSISDB Database).

Programmatic access through the managed API

See the Microsoft.SqlServer.IntegrationServices.Store namespace and its classes, especially the Microsoft.SqlServer.IntegrationServices.Store.PackageInfo class.

You can manage the permissions on an individual package on the Permissions page of the Package Properties dialog box in Management Studio. You can grant permissions to additional users or modify the permissions of existing users.

For information about the other methods that you can use to manage the permissions on a package, see the following topics.

Transact-SQL access

To view the permissions that exist on a package, query the view, catalog.effective_object_permissions (SSISDB Database).

To grant or revoke permissions:

• Call the stored procedures, catalog.grant_permission (SSISDB Database), or catalog.revoke_permission (SSISDB Database).

• While you are on the Permissions page in the Package Properties dialog box, you can also use the Script button on the toolbar to write the Transact-SQL code for you. The generated script calls the stored procedures catalog.grant_permission (SSISDB Database) or catalog.revoke_permission (SSISDB Database) with the options required to make the same changes that you have made on the Permissions page. The script appears in a new script window in Management Studio.

Programmatic access through the managed API

See the Microsoft.SqlServer.IntegrationServices.Store namespace and its classes, especially the Microsoft.SqlServer.IntegrationServices.Store.IntegrationStore class and its Microsoft.SqlServer.IntegrationServices.Store.IntegrationStore.GrantPermission(Microsoft.SqlServer.IntegrationServices.Store.SecurableType,System.Int64,System.Int32,Microsoft.SqlServer.IntegrationServices.Store.PermissionType) and Microsoft.SqlServer.IntegrationServices.Store.IntegrationStore.RevokePermission(Microsoft.SqlServer.IntegrationServices.Store.SecurableType,System.Int64,System.Int32,Microsoft.SqlServer.IntegrationServices.Store.PermissionType) methods.