Putting Integration Services Packages into Production

Integration Services packages can be executed in the Business Intelligence Development Studio. This is convenient for development and debugging, but it probably is not the way you want to use these packages in a production environment. This section looks at how we deploy packages to our servers and execute those packages once they are there.

Deploying Integration Services Packages

In most cases, Integration Services packages are developed on a test or development server and put into regular usage on one or more production servers. This means we need to deploy the package from the test or development environment to the production environment. Fortunately, Integration Services offers several tools to allow this to happen in a convenient and easily maintainable manner.

Package Configurations

The first thing we need as we prepare to deploy a package to a different server is a straightforward means of modifying the information in the package that is server-specific. Package configurations provide us with just such a tool. We use the package configurations feature to create one or more stores of configuration information that are external to the package. These external stores can be easily modified as the server-specific information changes from server to server.

To create package configurations, open the Integration Services package in the Business Intelligence Development Studio. Select Package Configurations under the SSIS menu. The Package Configurations Organizer dialog box appears. Check Enable Package Configurations to enable package configurations for this package.

Click Add to create a new package configuration. This launches the Package Configuration Wizard. On the Select Configuration Type page of the wizard, select the type of configuration using the Configuration Type drop-down list.

The following types of configurations are supported:

• XML Configuration File The configuration values Cor several package items are stored in an XML file. Use Configuration Filename to specify the path and filename of the XML file or use Environment Variable to specify the name of a Windows environment variable that will contain the path and filename of the XML file.

• Environment Variable The configuration value for a single package item is stored in a Windows environment variable. Use the Environment Variable drop-down list to select the name of an existing environment variable or type in the name of a new environment variable.

• Registry Entry The configuration value for a single package item is stored in a Windows registry entry. Use Registry Entry to specify the name of the registry key or use Environment Variable to specify the name of a Windows environment variable that will contain the name of the registry key.

• Parent Package Variable The configuration value for a single package item is stored in a variable in the parent package that is launching this package through the Execute Package task. Use Parent Variable to specify the name of the parent variable or use Environment Variable to specify the name of a Windows environment variable that will contain the name of the parent variable.

• SQL Server The configuration values for several package items are stored in a SQL Server table. Use the Connection drop-down list to select an existing OLE DB Connection Manager or click New to create one. Use the Configuration Table drop-down list to select an existing table to store the configuration information or click New to create one.

The package configuration can contain values for any or all of the following:

• Package-scope variable properties

• Task-scope variable properties

• Task properties

• Connection Manager properties

• Package properties

If we select the XML Configuration File or the SQL Server configuration type, we need to place checkmarks on the Select Properties to Export page for each of the properties we want included in the XML file. If we select the Environment Variable, Registry Entry, or Parent Package Variable configuration types we need to highlight, on the Select Property page, the property we want to provide a value for. This completes the SSIS Configuration Wizard.

We may create multiple configurations for the same package. Integration Services attempts to load each configuration in order. The last value loaded in a particular property is the value that is used during package execution. We can change the order in which the configurations are loaded using the up and down arrows in the Package Configuration Organizer dialog box. Any configurations that cannot be found are ignored.

Creating a Package Deployment Utility

To easily deploy the package to another server, we need to create a package deployment utility. The package deployment utility installs the package on a server. In addition, the package deployment utility sets up the necessary package configurations.

To create a package deployment utility, open the package in the Business Intelligence Development Studio. Right-click the project entry in the Solution Explorer window and select Properties from the Context menu. This displays the Property Pages dialog box for this project,

On the Deployment Utility page of the Property Pages dialog box there are three properties to set. The AllowConfigumtionChanges property determines whether the package configuration settings can be changed at the time the package is installed on another server. The CreateDeploymentUtility property determines whether or not a package installer is created for this project. To create a package deployment utility, this property must be set to True. The DeploymentOutputPath property determines the location where the package deployment utility is created. By default, it is set to the binDeployment folder within the project folder.

Once the CreateDeploymentUtility property has been set to True, the package deployment utility can be created by building the project. This is done by right-clicking the project entry in the Solution Explorer and selecting Build from the Context menu. The Output window shows us the progress of the build process and whether the build failed or was successful.

Note 

All of the packages in the Integration Services project are part of the package deployment utility that results from the build.

Installing with a Package Deployment Utility

Once the package deployment utility has been created, copy the entire contents of the Deployment folder to a location that can be accessed by the target server. From the target server, execute the DTSInstall.exe file in the package deployment utility. This launches the SSIS Package Installer. Choose between installing the package as part of the file system or in SQL Server. In most cases, the SQL Server option is used if SQL Server is to be used to share the package between servers. We can also choose whether or not to have the package validated after installation. The validation lets us know if the package is likely to function properly on the new server, so it is probably a good idea.

If a file system installation is selected, we need to specify the file path for the installation. If a SQL Server installation is selected, we need to specify the SQL Server name and login credentials. We also need to specify a folder for the SQL Server installation. This folder holds the configuration files. If the AllowConfigurationChanges property was set to True, we can change the settings of the configurable properties. If package validation was selected, pay attention to the warnings and error messages that result from the validation process.

Note 

The DTS Server Windows service must be running before we attempt to do a SQL Server installation.

After we install a package to SQL Server, we can manage that package by creating a connection to that server in the SQL Server Management Studio. To do this, we select Integration Services in the Registered Servers window and connect to an Integration Services server. An Integration Services server is any PC that is running the DTS Server Windows service.

Executing Integration Services Packages

Integration Services packages can be executed in a number of different ways:

• In the Business Intelligence Development Studio

• In the SQL Server Management Studio

• As part of a SQL Server job

• From the command line using the dtsexee command line utility

• From .NET code using the Package class in the Microsoft.SqlServer.Dts .Runtime library

The dtutil command line utility can be used to copy, move, delete, or validate the existence of an Integration Services package.