Kirk Haselden

DevTeach Day Three

Configurations and Deployment

Today I gave the Configurations and Deployment talk. Very few of the attendees had even installed SSIS. I was struck by the fact that SSIS is still new to most people. There are a lot of folks out there that are still active and consistent DTS users. I tend to forget that, since I've been working on SSIS, talking about it, and writing about it for literally years now.

I talked a lot about what I've been calling two stage configuration and I thought I'd get some of those ideas down here. The tools lend themselves to a simple single stage configuration, that is, configurations that modify properties on tasks, connections etc. directly. The configuration gets deployed along with the package and the user changes the configuration when it's deployed to force the package to work on the destination machine. There are numerous problems with this method of configuring packages namely, configuration files propagate with each package, configuration management becomes a headache and the power of configurations is only being partly utilized. We've been criticized for those limitations and even had a few bugs filed. But, I would recommend that folks take a second look. Configurations are very flexible and may be used in a wide variety of ways. Single stage configuration is useful for certain cases, but for the general case, when you want to make your packages portable and easy to maintain, there is two stage configuration.

Single Stage vs. Two Stage

Two stage or environment configuration is using configurations to describe the environment into which a package is deployed. The way it works is a configuration is created per machine. Any of the configuration types will work, the differences are only mechanical. For example, an XML configuration would be named the same, and for added flexibility for differences between environments, use an indirect configuration. For SQL Server Configurations, you can use a different filter for different machines. Since SQL Server Configurations use connection managers to access the server, the connection manager is configurable. So it's possible to configure the configurations in this way. For example, you could have an environment variable configuration that configures the SQL Server Configurations connection manager to access a different server.

Those machine specific configurations describe the machine in terms of the resources, ie. hard drives, folders, data sources, package locations, drop file paths, and so on. The configuration describes the environment by configuring variables, not properties. That's the first stage. Then the variables may be used inside of property expressions to configure the properties. That's the second stage. Now, each machine in the package development lifecycle has a resident configuration like this. Deploying a package is a simple matter of moving the package. The package will use the machine resident configuration when it is deployed "into" that environment to configure the variables. 

Variations

There will be variations and tuning you can do with this basic method. For example, you may wish to have one configuration for generic machine dependant values and a few different configurations for each of the different classes of package you may have in your organization. Perhaps you have some packages that are used for loading dimensions and they have a set of unique configuration requirements. Then, you can have a configuration that meets those requirements that only those packages reference.

Summary

The point is that there are many ways to use configurations. If you choose to propagate numerous configurations to the point where it becomes a management nightmare, you should rethink your strategy. What I've suggested here is one way to make configurations and package deployment both manageable and simple. Your configuration policy should be factored and designed to reflect your package development lifecycle. Just like tasks, transforms and other components in SSIS, configurations are one piece of a well factored platform that you can use to build solutions. How you choose to use those pieces to build your solutions is the fun part, and it's really up to you.