Prior to building and deploying your SSIS project to the SQL SSIS server, you need to build and create a deployment utility file in your machine. You may also create a configuration file that updates the properties of the package elements during runtime. This step is optional and configuration properties are automatically included when the packages are deployed anyway.
Building your SSIS Database Project
You need to make sure that your SSIS project will build successfully prior to deployment. You need to complete all errors or issues found during your initial build.
After all errors are addressed you will need to do another build this time creating a deployment utility.
To create a deployment utility, in your SSIS project properties, see screenshot below, go to Deployment Utility from the left pane menu. Change the value of CreateDeploymentUtility to TRUE. Click OK to save changes and rebuild your SSIS project.
After the build is successful, your Output window should be similar as below;
Check your DeploymentOutputPath in your local machine to verify that all deployment files are created. Verify that the file type Integration Services Deployment Manifest is created along with copies of your SSIS packages in the same deployment folder.
Remember that your deployment folder is updated or refreshed everytime you do a new build. This means that any file saved to this folder that is not copied to the folder again by the build process will be deleted. So any configuration files or readme files that you may have will be deleted.
Move/Copy your Deployment Files
You will need to copy this entire deployment folder in your SQL SSIS server. The installation of your SSIS packages must be done on the actual server. During this time you will need to check your permission to copy files to the server or your DBA may copy the files for you.
Run Package Installation Wizard
To start with the installation processs, click on the deployment manifest file to launch the installation wizard.
Select the ‘SQL Server deployment’ for the location of the file. This ideal for organizations that uses SQL SSIS server for all database servers and your package dependencies such as configuration files will be copied to the folder in your system. Tick the checkbox to validate packages after installation and you’re good to go.
Specify the instance of SQL Server to install the packages to and select the authentication mode. Choose the package path within the SQL Server.
On the next screen, specify the location of the package files and dependencies within the host server.
If your packages have any configurations, you may set this up in the Configure Packages window. After the packages have been successfully deployed, the wizard creates a log of the events and a summary of the deployment.
And there you have it, all your SSIS packages deployed to the SQL Server. Now all you need to do now is how to trigger these packages to execute either via by scheduled jobs, in your applications or manually. But that’s another topic 🙂
Signing off for now for a week’s beach vacation!