The Business Intelligence Blog

January 14, 2010

Deploying Integration Services packages using Deployment Utility

Here the first step after creating the package is to create a deployment utility. For more details on how to create a deployment utility, please refer to the post Creating Deployment Utility for Integration Services Packages. Once the deployment utility has been created, you will have to copy the contents of the folder (\bin\Deployment) to the server where you want to deploy your packages and double-click the .SSISDeploymentManifest file to launch the Package Installation Wizard. On the welcome page, click next. The Deploy SSIS Packages dialog appears as shown in the figure below.

Here you can either choose File system deployment or SQL Server deployment to deploy the packages. You can also
opt to validate the packages on the target server after installation. Depending on the option you select on this page, you will see different pages in the installation process.

1.1 File system deployment
On the <Select Installation Folder page as shown in the figure below, click the browse button to navigate to the location where you intend to store your packages and their dependencies(such as configuration files, log files, checkpoint files etc) and click next.

If you store the packages under the location Program Files\Microsoft SQL Server\100\DTS\Packages, then the packages
will be managed by the Integration Services service and these packages will show up in the File System under
Stored Packages in SQL Server Management Studio(SSMS) when you connect to Integration Services. Even if you manually place the packages under this location, they will be managed by Integration Services service.

On the Confirm Installation page as shown in the figure below, click next to deploy/install the packages to the file system.

Securing packages in File system deployment:
In File System deployment, Integration Services packages can be encrypted using the Package Protection Level property as well as by controlling access to the package and the dependent files(configuration files, checkpoint files, log files etc) by using folder/file permissions at the operating system level. Also, please note that the Package Protection Level does NOT protect files that are stored outside the Integration Services package. For example configuration files, checkpoint files, log files will not be protected by the package protection level, so you will have to secure them using some other means such as folder/file permissions at the operating system level.

1.2 SQL Server Deployment
On the Specify Target SQL Server page as shown in the figure below, you specify the following information:

Server Name: The server name where you want to deploy your packages.
Authentication: The authentication to use to log on to the server. You can either choose windows authentication or SQL server authentication.
Package path: The logical folder in MSDB database where you want to store your packages, type “/” to use the default folder. Integration Services packages are stored in sysssispackages table in MSDB database.
Rely on server storage for encryption: Select this option to secure the entire package using SQL server database roles.

On the Select Installation Folder as shown in the figure below, click the browse button to navigate to the location where you want to save the package dependencies(such as configuration files, log files, checkpoint files etc).

On the Confirm Installation page as shown in the figure below, click next to deploy/install the packages to SQL server.

Securing packages in SQL server deployment:
In SQL Server deployment, Integration Services packages can be encrypted using the Package Protection Level property as well as stored inside MSDB database, where the access to the package is controlled by Integration Services database roles. Database roles are assigned to packages using SQL Server Management Studio. However, there may be additional files(configuration files, checkpoint files, log files etc) that need to be deployed as well. If you have sensitive information in the additional files, access to those files can be controlled by using folder/file permissions at the operating system level. Please note that the Package Protection Level does NOT protect files that are stored outside the Integration Services package. For example configuration files, checkpoint files, log files will not be protected by the package protection level, so you will have secure them using some other means such as folder/file permissions at the operating system level.
Here is a brief overview of the built-in database roles available with Integration Services:

db_ssisadmin – This role has unlimited privileges to all the integration services packages stored on the server. In short, this role has god rights.
db_ssisoperator – This role has all the read permissions(enumerate, view, execute, export) but no write permissions(import,delete).
db_ssisltduser – Users belonging to this role will have god rights on the packages owned by them. Furthermore, the users will be able to enumerate all the packages.

Please note that the names of the built-in database roles have changed between SSIS 2005 and SSIS 2008. Here is the comparison:
db_dtsadmin in SQL Server 2005 is db_ssisadmin in SQL Server 2008
db_dtsltduser in SQL Server 2005 is db_ssisltduser in SQL Server 2008
db_dtsoperator in SQL Server 2005 is db_ssisoperator in SQL Server 2008.

Advertisements

1 Comment »

  1. […] 1. Using Deployment Utility in Business Intelligence Development Studio […]

    Pingback by Deploying Integration Services Packages « My Blog — January 14, 2010 @ 8:09 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: