Updated March 16, 2023
Introduction to SSIS Deployment
The following article provides an outline for SSIS Deployment. The project deployment period and the traditional package deployment model are both supported by Integration Services. In addition, the application deployment methodology enables projects to be deployed to Integration Services. Finally, the project deployment file is a self-contained installation container that holds only the most vital information about just the project’s packages and settings.
SSIS deployment overviews
The advent of the SSIS Package Deployment Model in SQL Server 2012 offers an easy approach to producing a single unit of deployment (deployment package) that comprises whatever is needed for the deployment, which has always been a hurdle for SSIS writers. The information provided in the Integration Services project file also isn’t captured in the project deployment file (.dtproj extension). Supplementary text files used for taking notes, for instance, are not kept in the project deployment file and are not delivered to the catalog. The Incremental Package Deployment feature of Integration Services (SSIS) allows us to deploy one or more packages without deploying the entire project. SQL Server 2012 and the SSIS Catalog added the ability to deploy SSIS developments and the project’s package(s). Thanks to this package organization, entities, attributes, and values can be transferred between packages in an SSIS project.
SSIS Project Deployment
Here we should develop a basic application to transfer a short text file from a server-side to a production server using the SSIS excellent component “File System Task.” First, we would verify the sample locally, and maybe we’ll transport the final document to the production server for actual execution. SSIS packages can be deployed to the following platforms:
Linux – Microsoft Azure Cloud
A project with modules and variables is deployed to the SSISDB catalog on a SQL Server instance.
To deploy any packages using this deployment model, we must take the following four steps:
- First, create a package configuration file in step one.
- Next, create a deployment utility in step two.
- Finally, move the Deployment folder to the desired location.
- Install the packages.
Let’s look at each stage one by one.
To generate a Package Configuration File, follow these steps.
Step-1: Create a package configuration file that modifies package element properties at runtime in this step. Right-click anywhere else in the Control Flow area and select ‘Package Configuration,’ as seen below:
Next, click add button to show the wizard. Followed by clicking on the configuration type. Here we shall select an XML configuration file and save the folder or file in the respective location. The deployment program duplicates all the packages, XML configuration files, irrelevant project files, and the installation kit configuration file. These files are copied to the Deployment output path property placed inside the project. path \ bin\Deployment is the default value.
The next step is to check a container and export configuration files.
Finally, we would get the summary list of a configuration file.
Step-2: Doing Deployment facility
We’ll need a package deployment tool for the project at this stage, which contains whatever package we intend to distribute. To make a Deployment Utility, right-click on your package and select Properties. The following dialogue will appear:
Assign Deployment utility to true. The next step is to build a Project by right-clicking on Project. On successful execution, we shall see the below message:
Installed SSIS deployment
The Integration Services Deployment Wizard provides two deployment models:
- Model for project rollout
- Model for deploying packages
Using the Project Deployment methodology, one can deploy a SQL Server Integration Services (SSIS) project to the SSIS Catalog as a single entity. One can use the Package Deployment approach to deliver packages to the SSIS Catalog without having to deploy the entire project. The Package Installation Wizard can be used to deploy SQL Server Integration Services packages. For example, we could deploy programs to one of two places using this wizard:
– To a SQL Server instance.
– To the computer’s file system.
This article’s procedures explain how to deploy packages to each site. First, to execute the Package Installation Wizard, we need to transfer the deploy folder to the target machine, which was created when we constructed the deployment tool.
- Open the deployment folder on the destination computer to deploy packages to an instance of SQL Server.
- To open the manifest file, double-click it.
- To begin the Package Installation Wizard, type SSISDeploymentManifest.
- Select the SQL Server deployment option on the Deploy SSIS Packages page.
- Validate packages after installation is an option for validating packages after they’ve been installed on the target server.
- Select an authentication mode and a SQL Server instance to deploy the packages on the Specify Target SQL Server page. Users must specify login details if they choose SQL Server Authentication.
- Select the folder in the file system where the package dependencies will be installed on the Select Installation Folder page. Users can edit configurations if the package includes them.
SQLCLR must be enabled on the SQL Server instance to use the Project Deployment paradigm. Below is a script that turns on the SQLCLR:
sp_configure ‘show advanced options, 1;
GO
RECONFIGURE;
GO
Create a catalog by right-clicking on ‘Integration Services Catalogs’ and selecting ‘Create Catalog.’
Next, complete all the settings – the deployed SSIS project will be in the ‘Projects’ folder, while the separate sets of environment settings will be in the ‘Environments’ folder.
Note: There seems to be no method to distribute simple updates to a single container under the enterprise deployment paradigm. Instead, the entire project must be deployed regularly.
The SQLCLR is required to deploy the packages to the SSIS catalog, but this is not required to operate the packages after that. Therefore, even in organizations with strict security standards, the SQLCLR can be enabled during the setup and deployment of the SSIS catalog and then disabled again.
Conclusion
We covered how to deploy SSIS Project to SQL Server in this blog. We’ve also shown how to construct an Integration Service Catalog, build a project using the Project Deployment Model, and deploy an SSIS project to the Integration Services Catalog. This significantly reduces the time it takes to deploy large-scale environments.
Recommended Articles
This is a guide to SSIS Deployment. Here we discuss constructing an Integration Service Catalog and building a project using the Project Deployment Model. You may also look at the following articles to learn more –
