Configuration and Deployment Approaches

The following sections provide a few ideas for how to use package configurations and other tools. These ideas are not necessarily original or comprehensive. Undoubtedly, as you and others use Integration Services over time, better and better practices and utilities will emerge. The following sections provide some ideas and approaches to using the tools as a starting point.

Location Dependence and Machine-Resident Configurations

The location dependence problem was touched on earlier but how to work around it wasn't fully described. Now that you've learned about the tools in more detail, you're ready to explore an approach to the problem.

The Problem

Your package references resources such as folders and files in locations that are specific to a given machine.

What's Needed

You need a way to isolate the package from the environment in which it functions, while still providing ways to configure the package so it can reconcile itself and access resources.

The Solution

Create a machine-resident configuration that reflects the machine's environment. Machine-resident configurations stay on one machine and describe the machine environment to the package. Packages reference the configuration through an indirect configuration. The machine-resident configuration configures standard variables that you create in all your packages that specify the locations of files, names, or servers and any other location-dependent settings. The sample packages use this method.

Causes of Location Dependence

The following are some causes of nonportable packages and some suggestions for eliminating those causes:

• Hard-coded references to files, servers, and other resources Eliminate such references as explained previously.

• Using incorrect package protection level For production, use server storage, if possible.

• Directly referencing configuration files Use indirect or SQL Server configurations.

• Using standard security Use integrated security, if possible.

• References to unavailable subpackages Store packages on SQL Server.

• References to tasks and components not available on a machine All components must be installed on the machine where the package that references them is to run.

Database Objects That Move

Some companies are growing very quickly. In fact, they are adding new servers on a regular basis. For them, maintaining packages can be a nightmare because the environment is changing around the packages as they bring new servers online and repurpose older servers.

The Problem

Your packages reference a number of objects that you move regularly and it breaks your packages.

What's Needed

You need a way to specify a reference to a database object, such as a table that will remain valid, even if it moves to a different server. In other words, you need to use a name that is not the four-part specific name.

The Solution

Use synonyms to sever the dependency between physical database object locations and packages. A synonym is a database object that provides an alternative name for another database object. Synonyms provide a layer of abstraction that protects a client application, in this case your packages, from changes made to the name or location of the base object. Using synonyms, you can use a simple naming convention instead of the four-part name form.

Server.Database.Schema.ObjectName 

Using synonyms, even if the location of the object were to change to another server, the package would still remain valid because the synonym will change underneath. The synonym name will remain the same. Use synonyms if you have a fairly dynamic environment where packages reference objects that are moved or changed often.

Overriding Configurations

You want to have a way to temporarily modify the behavior of your packages. For example, you want to change all packages to reference a backup server instead of the production server while you do maintenance.

The Problem

The problem is that you have to physically change configurations. That can be a bit messy and nobody wants to muck around with making quasipermanent changes to functioning systems.

What's Needed

You need a way to temporarily configure packages to reference a different server.

The Solution

The ability to set the same property from multiple configurations is useful when you want to have a default configuration but override it with other package or environment-specific behaviors. For this example, you need to take a server offline for maintenance; you can use this behavior to temporarily switch all packages to reference a backup server.

You do this by creating a secondary or backup configuration in all your packages that is applied after the default configuration. Normally, while running all packages against the default or production server, the backup configuration wouldn't contain any configurations. But, when you need to take the production server offline, you can add a configuration to the backup configuration to point all packages to the backup server. Now, if any of your scheduled packages happen to run during the time the production machine is offline, they will pick up the second backup configuration and point to the backup server. SQL Server Table configurations are especially handy for this because they can be changed in one location, yet referenced across your enterprise. By simply changing the value of one field in one row, you can effectively and temporarily change the behavior of every package in your enterprise. To turn off the secondary configuration, simply remove the secondary configuration value.

Note

You can do the same thing with the default configuration. However, this method allows for more flexibility because you can have more than one secondary configuration and segment the packages you redirect by selectively editing configurations.

Configuring Connection Managers

This is really just a more specific location dependence problem, but it has given enough users problems that it is worth mentioning.

The Problem

Your network isn't configured to support Integrated Authentication. You need to automatically configure a password, in this case on connections, noninteractively because you're going to execute the package using SQL Agent.

What's Needed

You need a way to set the password without having to type it in but yet still be secure.

The Solution

Use a SQL Server Table configuration to set the value of a variable, which you later use in a property expression for the connection string, such as the following:

"Provider=sqloledb;Data Source=" + @SERVERNAME + ;Initial Catalog=pubs;User ID=sa;Password=" + @PASSWORD + ";" 

This expression builds the connection string with the server name and password correctly embedded and looks similar to the following when evaluated:

"Provider=sqloledb;Data Source=ROMEO;Initial Catalog=pubs;User ID=sa;Password=foo- bar;"