Deploying SSIS Packages in SQL Server 2005

In our last post on SSIS, we dealt with the creation of Integration Services Configurations and how the configuration files help us in altering the package properties at run time. As explained in our previous posts, the SSIS package is created in the Business Intelligence Development Studio (BIDS) and saved on the client. In this post we will discuss the deployment of the SSIS packages we created onto the SQL Server.

Deploying the SSIS Package to the Destination Server:

There are three steps involved in the deployment of the SSIS Package to the target server.

1. Creation of the Package Deployment Utility.
2. Copying of the Deployment Folder to the Target Server.
3. Running the Package Installation Wizard to deploy the package onto the target server.

1. Creation of the Package Deployment Utility:

Deployment utility is a folder which contains the files to be deployed to the target server. The Deployment utility is created on the client machine where the SSIS Package resides. In this post we will deploy the package which we made use of in our last post for which we created the configurations. I am placing the project folder in C:\SSIS\Packages location on the hard drive. The project name as well as the Folder Name is TextFileImport. Open up the solution for this Project. The figure below shows the SSIS Package:

We can include the miscellaneous files like the Read Me File or the Sample File for the Text File Import in the Miscellaneous folder of the Solution. For demo purposes let us include the text file for deployment. To do this, right click on the Solution and in the pop up menu move the mouse over Add option and click the Existing Item. The image shown below depicts this:

This will open up the Add Existing Item box. Browse to the text file location and click on Add button. The image below shows this:

To create the deployment utility and build the project we should modify the package properties. To do this right clicks on the project and in the pop up menu click Properties. The image below shows how to open up the Package Properties:

The property pop up for the SSIS package is as shown in the image below:

In order to create the deployment utility, click on the Deployment Utility option in this window. The following image shows the Deployment Utility Properties which should be modified to create the deployment utility:

To create the deployment utility change the CreateDeploymentUtility option to true. When we build this package the deployment utility is created in the location pointed by the DeploymentOutputPath. This path can be changed to our liking. For the purpose of this demo, we are going to leave it as the default. The default path is the bin\Deployment folder in the Project Folder. In our case it is C:\SSIS\Packages\TextFileImport\TextFileImport\bin. Click Apply and then Ok to return to the BIDS. To build the project right click on the Solution and click build on the pop up menu. The image shown below depicts this:

After building the solution, the deployment utility is created in the default location if the build is completed successfully. Navigate to the deployment folder, C:\SSIS\Packages\TextFileImport\TextFileImport\bin\Deployment in our case and we will find the deployment files i.e. the integration services deployment manifest file, the configuration files, and the sample text file for import.

2. Copying of the Deployment Folder to the Target Server:

After creating the deployment utility, next step is to copy the deployment folder to the target server. For our demo purpose we are going to deploy the package in our local machine itself. Hence, I am going to launch the Package Installation Wizard from the deployment folder location itself.
3. Running the Package Installation Wizard:

Navigate to the deployment utility folder created by building the SSIS Project. Launch the Package Installing Wizard by double clicking on the Deployment Manifest File. The image below shows the Welcome Page of the Package Installation Wizard:

We have option to bypass this page next time we run this wizard by clicking on the little check box near the Do not Show this starting page again. For this demo we will leave this box unchecked and click on Next. The next page shows the options available for us to deploy the package. The image shown below depicts this:

There are two options available for us to deploy the SSIS Packages.

a) File System Deployment:

As the name suggests, this option allows us to deploy the SSIS Project to the File System i.e. a physical location on the hard disk on the target server. If this option is chosen, we have to back up the SSIS Packages manually every night for any disaster recovery.

b) Sql Server Deployment:
This option allows us to deploy the SSIS project to the SQL Server i.e. on the MSDB database. Since the msdb is usually backed up every night, this method of deployment saves us some extra work of backing up the SSIS Packages manually if deployed using the file system.

For this demo purpose let us select the Sql Server Deployment Option and Click next. In the next page we have to specify the target server information. For our demo purpose let us install this package on our local server. The image below shows the Target Sql Server Page of the Package Installation Wizard:

Specify the server information i.e. the local host and the authentication used to log into the server and click next. The next page asks us to specify the package installation folder. By default the folder is C:\Program Files\Microsoft SQL Server\90\DTS\Packages\<Project Folder Name>. In our case it is, C:\Program Files\Microsoft SQL Server\90\DTS\Packages\TextFileImport.

If you want to change the installation folder you are welcome to do so by clicking browse and selecting the installation folder. For this demo, let us leave this as default and click next:

Click next and the Wizard shows the Confirmation Page. Clicking on Next takes us to the Package Configuration Page. The server by default selects the Configuration File. The Configuration File can be found in the installation folder after the package is deployed on the server. The image below shows the Package Configurations Page:

Clicking on next takes us to the summary page. Review the Package Summary and click on the Finish button to complete the Installation.

Viewing the deployed SSIS Package on the Server:

To view the deployed package, log in into the Integration Services Engine using the Sql Server Management Studio. To do so, on the Server Connection dialogue, select the Integration Services as the Server Type, the Target Server Name and the required authentication type and click connect. In our case the target server is our local machine.

In the Object Explorer, navigate to the Stored Package folder. Since we stored the package on the SQL Server (MSDB), we can find our package in the MSDB Folder. Alternatively we can see that there is an entry in the sysdtspackages90 table in the msdb database for our SSIS Package. The image below shows this:

Running the SSIS Package from the SQL Server:

The SSIS Package can be run manually from the Server by right clicking on the Package and clicking on Run option in the Pop up menu. This opens up the Execute Package Utility. The image below shows the Execute Package Utility:

In this utility we can optionally specify the Configuration Files in the Configuration Page. The various options in this utility are left as an exercise for the reader. Alternatively the Execute Package utility can be invoked from the command prompt by typing the command dtexecui:

Type the command dtexecui at the command prompt and hit the enter key. This will pop up the Execute Package Utility as shown in the image below:

The SSIS Package can be run by selecting the appropriate values in the related text boxes.
In our case, since we deployed the package to the SQL Server select the SQL Server option in the Package Source text box and specify the target server name. Then the text box for Package gets enabled and we can choose the package to execute.

We can also execute the SSIS package from the command prompt or using the xp_cmdshell extended stored procedure by using the dtexec command line utility. This is the command line for executing the SSIS Packages rather than using the GUI for executing them. You can read more on the dtexec utility in BOL or at MSDN over here.

To Sum Up

In this post, we discussed the various steps involved in deploying the SSIS Package on the Server. We also went ahead and deployed one of our packages created as a part of this SSIS series on the SQL Server in the MSDB system database. We also saw the various methods to execute the SSIS Package including the command line utility dtexec. In the next post in the series, we will look into the scheduling of this SSIS Package to run as a job in SSMS.