SQL Server Integration Services (SSIS) - Options to Deploy a Package in SSIS

Introduction

In this article we are going to see how to deploy a SSIS package upon completion of developing, building and executing the package. Deploying an application is something we need to also do since it is done based on application requirements such as when the package should execute, who should execute the package etc. We have 3 options available with deploying a SSIS package and the options are as follows:

1.       Deployment Utility

2.       Command line Executable

3.       SQL Server Management Studio

We will see each approach and the steps involved in deploying the package. You can refer to my previous articles on SSIS to get some idea before going ahead with this article. 

Approaches

Approach 1: Deployment Utility

This approach is used to create an installer for the package and can be executed whereever it's required. This utility is available by default to all the Integration projects; we will see the steps to make use of this utility.

Steps:

Go to BIDS (check my previous articles on how to go to BIDS) and open the package solution that you want to deploy and right click on the project and go to properties as shown in the figure below.

A dialog will open where you have the option for Deployment Utility. Clicking on that will show the options at the right side of the dialog box where we need to set the CreateDeploymentUtility to TRUE and give the path where the installer needs to be created as shown in the screen below.

Once we are done, now right click on the project and give BUILD; it will show the result at the bottom of the page if the build is succeeded or failed. If it's succeeded it will create the deployment installer as shown in the following screen. You can copy these files to any location and double click to make use of it.

 

Approach 2: Command Line Executable

Microsoft has provided an Executable DTUTIL.EXE that can be used to deploy SSIS packages. This command line utility is a good option to script the package. We can use this executable directly or can make use of it in the batch file.

To execute it at the command prompt check the following script. Go to the path of the package and execute the following scripts.

DTUTIL /FILE Package1.dtsx

       /COPY

       SQL;SSISPackage1

 

In order to deploy the package at the file system level use the following script

DTUTIL /FILE Package1.dtsx

       /COPY

       FILE;C:\SSIS\SSISPackage1.dtsx

 

For more option on this utility check the following url:

http://msdn.microsoft.com/en-us/library/ms162820%28SQL.90%29.aspx

Approach 3: SQL Server Management Studio

This approach requires integration services to be connected and must be in a running mode in order to use this deployment option. In the object browser you can see something like below:

To deploy our package right click on the File system and select the Import Package from the menu. Fill in the details as shown in the following screen:

Clicking on OK will deploy the package.

Conclusion

This article describes the options available to deploy the SSIS packages.