Deploying SSIS Packages on to Production Server

Deploying SSIS Packages on to Production Server

Performance of packages is one of the main factors considered during the ETL process. To get best performance out of SSIS packages and work with large amount of data business data, we must deploy package onto a high performance production server. Here I will show you how to deploy the packages from development environment to the production environment.

Packages can be deployed by building a deployment manifest and using deployment utility and package installation wizard. After deploying packages on to the server they can be started manually or can be scheduled to run automatically using SQL Server Agent.

Creating Deployment Utility

Configure the SSIS project to generate the manifest file used for deploying the packages to the deployment server. In order to do this we need to change the properties of the project to be deployed.

To do this from solution explorer, right click on the project and select properties.

This opens the project property pages dialog box.  On the left in the configuration properties select Deployment Utility. On the right select the create deployment utility and set it to True.

 When we build the project from the solution explorer or build menu the files are created in the project’s bin\deployment folder. There you can see all the packages of the project and a file with name <Project name>. SSISDeploymentManifest which will launch package installation wizard.

If you are deploying these packages on the production server you need to copy this deployment folder to the production server.

Configuring Installation Wizard

1.       Now double click on the manifest file to open the installation wizard.

2.       Click next on the welcome page

3.       Select the type of deployment. In SSIS we can do two types of deployment File System Deployment and SQL Server deployment.

File System Deployment: This option is used if you want to install the SSIS packages on a specified folder on the system.

SQL Server Deployment: This option is used if you want to install SSIS packages on SQL Server. This option writes the package content to the MSDB system database. This option is most secure and preferable because everything is written to the database catalog rather than file system and packages can be restored by simply backing up and restoring the MSDB database.

4.       Here I am selecting SQL Server Deployment option and click next.

5.       Specify the target server name.

6.       Click next three times keeping the default configurations on each page and click finish.

7.       Now your deployment is successful.

Connecting to Integration Services

1.       To connect to integration services, open SQL Server Management Studio and in the object   explorer and choose Integration Services.

2.       Now connect to the server that you specified as target server above and click connect.

3.       After you connect you can see the server name and two folders. [Running Packages and Stored Packages]

4.       Expand the Stored Packages folder; there you can see two folders, File System and MSDB.

5.       If you have chosen File System Deployment then you can see your packages there else expand the MSDB to find the deployed packages.