You can deploy packages using the project deployment model or the package deployment model. The project deployment model, enables you to use the parameters and deploy to the Integration Services catalog.
There are several differences between the project deployment model and the package deployment model, as shown in below screen shot.
Project Deployment Model
In previous versions of SSIS, you deployed packages to either a server or a file system. Those options are still available but are not the recommended practice since SQL Server 2012. The project deployment model is the standard and makes deployment much easier, as you will see in Administering SSIS Topic. Parameters and environments are great features also and will be the standard for your SSIS development replacing configuration files and tables. The old configuration methods are still available though not recommended. This project deployment method along with the parameters and environments is a big change in the way you think about SSIS package deployments. You will now consider deploying projects as a group of work not individual packages. Think of a project as a body of work that needs to be completed together. This may include just one package or several. This section will cover the project deployment model.
When choosing a deployment model, you should consider the administration options and the deployment options. The project deployment model provides some development features, such as parameters and environments.
The Integration Services catalog is the location where packages and all SSIS objects are stored in SQL Server 2014 if the project deployment model is selected. An instance of SQL Server can have one catalog, and a catalog can contain folders for organizing your instance. Folders hold the SSIS projects and environments. As mentioned earlier, the folders in the catalog can also be used for security.
Parameters are covered briefly in The SSIS Tools Topic. In Administering SSIS Topic you will learn how they work from the administration side. When you create a parameter, you can scope it to either the package level or the project level. Parameters work much like variables. They can be used in expressions and tasks. You can assign parameters at runtime with T-SQL or let the package use the default value set during development.
Once you have a package deployed with numerous parameters, you may want to control them with different sets of values. Environments give you this capability. Environments store a set of variable values that can be referenced during package execution. You can store values for different runtimes. For example, you might have different values for weekend runtimes versus weekday runtimes. By storing all the parameter values in an environment, you can execute your package and point it to the correct set of parameter values, rather than set each value individually.
After you have completed your package development, you are ready to deploy the project to the SSIS catalog. In this example you have a package that contains an Execute SQL Task. You will have one parameter on the package, and deploy the package to the SSIS catalog and set up an environment. This sample package is available with the code download for this Tutorial.
In this example package you will simply use the Execute SQL Task to write a row to a table in AdventureWorks. Use the following code to create this example table:
CREATE TABLE [dbo].[InsertTest](
[StringCol] [varchar](50) NULL
) ON [PRIMARY]
The SQL statement in the Execute SQL Task in the package is as follows:
INSERT INTO [dbo].[InsertTest]
The project will contain one package with an Execute SQL Task that will write a value to the InsertTest table. The value that is inserted into this table is the parameter value. This parameter value will be changed using the environments. This first example uses a package level parameter. The example package has a package level parameter on it named strInput with a data type of String and a value of PackageParam, as shown in below screen shot.
Now that you are familiar with the package, you are going to deploy it to the SSIS catalog.
- To start the deployment, right-click on the project name in Solution Explorer in SQL Server Data Tools, and select Deploy (see Below screen shot). In the middle of this menu you should see the option Convert to Package Deployment Model. If you instead see Convert to Project Deployment Model, then your project is in the package deployment model, and you will need to click this option to convert it to the project deployment model.
- The first window of the SSIS Deployment Wizard is the basic information screen. Read this and then click the checkbox at the bottom to hide the screen in the future, and then click Next.
- The next window asks for the server name and the path. Enter your server name. This example uses LocalHost as the server name.
- Now click the Browse button next to Path and select a folder into which the package should be deployed. You can create a new folder by clicking the New Folder button at the bottom of the window. You must deploy the project into a folder; you cannot put it in the SSISDB root. For this example, use the folder named AdminDemo, as shown in below screen shot, and click Next.
- This next window is the Review window. Check the source and destination here to ensure everything is correct, and then click Deploy.
- The last window is the Results window. You should see a passed result for every step, as shown in below screen shot.
Congratulations. You have successfully deployed your first project to your SSIS catalog. The next section describes the package deployment model. Then we will return to this project in the T-SQL section of Administering SSIS Topic, where you will learn how to execute the package and set the parameter values and environments.
Package Deployment Model
The package deployment model is the older version of deployment, and you should be changing your methods to take full advantage of the newer method.
Because of the fact that some users will still want to use the old model, either because of large frameworks they have in place or just because changing can be difficult, this model is covered in this section.
In the package deployment model, you can create a deployment utility that helps users install your project of packages and any dependencies. This deployment utility is similar to creating a program like InstallShield, and it is for times when you want to pass a set of packages to a customer or a production DBA who may not know how to install SSIS packages manually. When you create a deployment utility, all the files necessary to install the project are copied into a centralized directory, and an .SSISDeploymentManifest file is created for the installer to run, which opens the Package Installation Wizard.
Creating the Deployment Manifest
To create a deployment utility, simply right-click the SSIS project in SQL Server Data Tools and select Properties. In the Property Pages dialog, go to the Deployment Utility page and change the CreateDeploymentUtility property to True, as shown in below screen shot. This is set to False by default. The AllowConfigurationChanges property is a key setting as well, and when set to True, it asks installers whether they would like to change any settings that may be exposed via a configuration file at installation time. The DeploymentOutputPath property specifies where the deployment utility will be outputted to underneath the project folder.
Next, under the Build menu, select Build <Project Name>, where <Project Name> represents your project’s name. You should see “(package deployment model)” next to the package name, as shown in below screen shot. If you don’t see this, then you are in project deployment model. If so, right-click on the project in Solution Explorer to convert to the package deployment model.
Clicking Build will open each package and build the project. If there are any errors in the package, you will see them at this point. As it builds the project, each package, and the project’s .SSISDeploymentManifest file, is validated and then outputted into the \bin\deployment directory under your project’s folder.
NOTE After building the deployment utility, you should change the CreateDeploymentUtility option to False again. Otherwise, each time you click the Play button to execute the package, each package will be validated and executed, which could take an enormous amount of time for a large project. Instead of using the green debug arrow at the top of SQL Server Data Tools, the best practice is to right-click on the package in Solution Explorer and then click Execute package. This ensures that only the package is executed.
The Package Deployment Wizard
Now that you have created a deployment .SSISDeploymentManifest file, you’re ready to send the contents of the «project location»\bin\deployment folder to the installation person. The installation person would then need to copy the contents of the folder to the server he or she wishes to deploy to and double-click the .SSISDeploymentManifest file. The installer could also run it remotely, but it is recommended that you run it on the same server as the target deployment server to simplify the installation. You can also modify the .SSISDeploymentManifest file in your favorite XML editor to specify which packages should be deployed. If the wizard does not open on double-clicking the mainfest file, choose dtsinstall found in program files as the program to open it.
After the introduction screen, you are asked where you want to deploy the packages, as shown in below screen shot. You can choose either a file system deployment or a SQL Server deployment. A file system deployment just copies the packages, which are the .dtsx files, to a directory on the server. A SQL Server deployment stores the packages in the MSDB database on the target server. Select the SQL Server deployment option if you are following this example, and then click Next. You can also have the wizard validate each package after you install it. This ensures that the package delivered to you is valid on your machine, including the Data Sources.
The following table lists the pros and cons of the MSDB versus File System Deployment. Security is usually the defining factor here.
If you selected SQL Server deployment, the next screen prompts you for the SQL Server 2014 instance to which you wish to deploy the packages. Additionally type “/” (without the quotes) for the Package Path property. This specifies that the packages will be installed into the root path. If you had selected a file system deployment, the next screen prompts you for the file path to which you wish to deploy the packages. The last option in the SQL Server deployment screen enables you to specify whether you want to rely on the SQL Server to protect the package by encrypting it. This is the recommended option, and it will change the ProtectionLevel package property to ServerStorage as it installs each package. You’ll learn more about the ProtectionLevel property later in Administering SSIS Topic.
Even though you selected a SQL Server deployment, you may still need to deploy files such as configuration files and readme files. The next screen enables you to specify where you want these files. Generally, they’ll be located in a subfolder named after the project under the C:\Program Files\Microsoft SQL Server\120\DTS\Packages folder.
After you click Next, the packages will be installed in the package store on the server. After the packages are installed, if the developer selected True for the AllowConfigurationChanges option in SQL Server Data Tools, then an additional screen appears, giving you, as installer, a chance to edit the values in the configuration file at deployment time. This is shown in below screen shot, and you can click the dropdown menu to see multiple configuration files. Unfortunately, it does not show the packages to which these files are associated.
The only other additional screen you might see is a pop-up if there were a user password on any package.
After the packages have been deployed, they are validated, as shown in below screen shot. Any problems will appear in this Packages Validation screen, and you can redeploy after correcting the problem. The last screen is a summary screen to complete the wizard.
If you wish to deploy a package in Management Studio, as shown later in Administering SSIS Topic, you have to do it one package at a time. The file system, however, is much easier. With this method of storage, you can just copy the .dtsx and supporting files manually into a directory that is monitored by the SSIS service, and the packages can be seen immediately from Management Studio.
The main point to remember about using the deployment utility is that every package and all project dependencies are deployed. If you don’t want to deploy that many packages, you can edit the .SSISDeploymentManifest file in a text editor to remove any extra files you don’t want to migrate. Some find it useful to create a project in the same solution that contains a subset of the packages that they wish to deploy, if deploying all packages and dependencies is too aggressive for them.
If you did want to edit the .SSISDeploymentManifest XML file before sending the folder to a client, you could just remove one of the «Package» lines, as shown in the following XML example. The header of the XML file indicates who created the deployment tool and when. This information is useful if the project doesn’t install correctly. If you don’t wish to deploy a configuration file with the wizard, you can remove the «ConfigurationFile» line in order to prevent the configuration file from overwriting the older configuration files that may already be on the server file.
<?xml version=”1.0″ ?>
Using the SSIS Package Store
The other option for deploying your packages is the SSIS Package Store. This is the older option now, and you should be moving your packages to the SSIS catalog to take advantage of features like parameters and environments. In some cases, the Package Store will actually physically store the package, such as the MSDB database option. If you’re using file system storage, the Package Store just keeps a pointer to the top-level directory and enumerates through the packages stored underneath that directory. In order to connect to the Package Store, the SSIS service must be running. This service is called SQL Server Integration Services, or MSDTSServer120. There is only one instance of the service per machine or per set of clustered machines.
You can configure the SSIS service in the Services applet (select Control Panel ⇒ Administrative Tools ⇒ Services). Double-click SQL Server Integration Services. The service is set to automatically start by default, under the NT AUTHORITY\NetworkService account. In the Recovery tab, you can specify that the service should automatically start up again in the event of a failure, as shown in below screen shot. You can specify what action should be taken if the service fails the first, second, and subsequent times. In this case, the service has been changed to restart if a failure occurs two times. The failure count is also reset after two days.
Although you can run and stop packages programmatically without the service, the service makes running packages more manageable. For example, if you have the service run the package, it tracks package execution, and users with the proper permission can interrogate the service to find out which packages are running. Those who are in the Windows Administrators group can stop all running packages. Otherwise, you can stop only packages that you have started. It can also aid in importing and exporting packages into the Package Store. We cover other uses for the service throughout Administering SSIS Topic, but another benefit is that it enables you to create a centralized ETL server to handle the execution of your packages throughout your enterprise.
The MSDTSServer120 service is configured through an XML file that is located by default in the following path: C:\Program Files\Microsoft SQL Server\120\DTS\Binn\MsDtsSrvr.ini.xml. This path varies if your servers are in a cluster. If you cannot find the path, go to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\120\SSIS\ServiceConfigFile registry key. By default, the XML file should look like the following:
<?xml version=”1.0″ encoding=”utf-8″ ?>
– <Folder xsi:type=”FileSystemFolder”>
– <Name>File System</Name>
There isn’t much to really configure in this file, but it does have some interesting uses. The first configuration line tells the packages how to react if the service is stopped. By default, packages that the service is running will stop if the service stops or fails over. You could also configure the packages to continue to run until they complete after the service is stopped by changing the StopExecutingPackagesOnShutdown property to False, as shown here:
The next configuration sections are the most important. They specify which paths and servers the MSDTSServer120 service will read from. Whenever the service starts, it reads this file to determine where the packages are stored. The default file contains a single entry for a SQL Server that looks like the following SqlServerFolder example:
The «Name» line represents how the name will appear in Management Studio for this set of packages. The «ServerName» line represents where the connection will point to. There is a problem, however: if your SQL Server is on a named instance, this file will still point to the default non-named instance (.). If you do have a named instance, simply replace the period with your instance name.
The next section shows you where your file system packages will be stored. The «StorePath» property specifies the folder from which all packages will be enumerated. The default path is C:\program files\microsoft sql server\120\dts\Packages, which is represented as ..\Packages in the default code that follows. That part of the statement goes one directory below the SSIS service file and then into the Packages folder.
Everything in the Packages folder, and below that folder, will be enumerated. You can create subdirectories under this folder, and they will immediately show up in Management Studio; you don’t have to modify the service configuration file. Each time you make a change to the MsDtsSrvr.ini.xml file, you must stop and start the MSDTSServer120 service.
In this section you learned how to use the SSIS Package store. Again keep in mind that this is an older method that you should move away from. Using the SSIS catalog and SSISDB gives you the ability to take advantage of the features like versioning, parameters, and environments.