Scenario
Let's look at the folder-reference problem in a little more detail. Say you have a package that runs nightly. It inserts the data from each flat file that an automated process drops to a folder and, when the package has processed each flat file, it moves the file to another location. Even in this simple scenario, if you were to move the package, several things can go wrong. Most of them have to do with the hard-coded references to folders on a hard drive.
You have several requirements for this project. First, you want your package to deploy seamlessly from development, test, quality-assurance (QA), and production machines. Second, you have a policy that only signed packages may be moved to the production server. You can't change the package after it's signed. Modifying it requires new regression tests to double-check that nothing has changed; then you must get QA to sign off on it again and resign the package. And third, many packages use the same folder for similar functions and you want it to be easy to modify the location of the dropped files without a management headache. In other words, you'd like to be able to change the file location in one place and have all the packages automatically reflect that change.
How to Do It
First, on the Advanced tab in the Properties Control panel of My Computer, click Environment Variables and create an environment variable called RESIDENTCON-FIGURATION that contains the fully qualified name of your resident configuration. Specifying the location of the XML configuration in an environment variable is like setting up configurations. Hard-coding the location of the configuration file brings the same problems as hard-coding the location of data files or servers. Using an environment variable to point to the resident configuration, however, allows even the resident configuration location to vary from machine to machine. Using an environment variable to reference the location of a configuration in this way is called indirect configuration.
To verify that the environment variable is correctly set, use the set command on the command line to ensure that the correct fully qualified configuration file name has been set. It should look something like this:
C:\>set res
RESIDENTCONFIGURATION=Z:\ISCONFIGURATIONS RESIDENT.dtsconfig
On each machine where you build, test, or otherwise deploy packages, you create this environment variable. Every package you create should reference this environment variable with an indirect XML configuration. If the resident configuration location varies on any machine where packages are deployed, that's OK because the package looks in the environment variable to "indirectly" find the location of the resident configuration.
Next, right-click the Control Flow Designer surface and select Package Configurations from the context menu to bring up the Package Configurations Organizer dialog box that Figure 1 shows. After ensuring that the Enable package configurations checkbox is selected, click Add to bring up the Package Configuration Wizard that Figure 2 shows. (I'm assuming you've already created an XML configuration file that configures your variables and that the XML configuration is in the location to which the RESIDENTCONFIGURATION environment variable points.)
In Figure 2, notice that I've selected an XML configuration file configuration type. I've also selected the Environment Variable option, which tells the package that while loading configurations, it should go find that environment variable, retrieve the location for the XML configuration, and configure itself from the XML configuration it finds there. In this way, you meet the requirement that the package never change once in production. If the location of the configuration file was directly set in the package, and if the location of the configuration file ever changed for some reason, I'd need to edit the package to reflect its new location and re-sign it. This way, the location of the package configuration may change, but the package is insulated from that detail. This is one more way of keeping the package in the dark.
In the resident package configuration, I have one configuration. It configures the "User::FlatFileDropDir" variable in my package to contain the drop folder location, which is Z:\FFDROP. That's configuration step 1. Next, I have a multi–flat file connection manager that points to the files that need to be processed in the Data Flow task. To avoid one-stage configuration, I'll use a property expression to build the location from the configured variable. The property expression looks like this:
@User::FlatFileDropDir + "\\" + "*.txt"
This expression will evaluate to Z:\FFDROP\*.txt. Now, as long as this package is moved to a machine with the resident configuration, it will successfully find the dropped flat files.
To ensure packages consistently conform to the appropriate policy, build a template package and add all the commonly used variables and the configurations that configure those variables as described. Give the variables a distinctive namespace such as RESIDENT or CONFIGURED. Also, name the configurations with a convention so that those who are unfamiliar with the template can easily identify which configurations modify which variables. Then, every time you need to build a new package, use the template package as a starting point. This will help you ensure that all your packages are consistently location-independent.
Other Causes of Location Dependence
I've covered perhaps the most common and problematic causes of non-portable packages: hard-coded references. But you should be aware of several others as well. Here's a summary of the more common causes of non-portable packages, with suggestions for eliminating those causes.
- Hard-coded references to files, servers, and other resources: Eliminate such references as I explained.
- 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.
- Using tasks or other components not installed on a machine: All components must be installed on the machine where the package that references them will run.
- Directly referencing parent package variables from sub-packages: Use Parent Package configurations if possible.
- Using a different user account to test the package than the one you use to execute it in production: When testing, use an account with identical permissions as the one you use in production. In production, use the Microsoft Agent SSIS subsystem to execute the package, using a proxy with the identical account. (For more information, see my August 2005 article "Security in SSIS," InstantDoc ID 46723.)
Everyday Application
Creating easily deployable packages isn't difficult, but it does require some effort, forethought, and discipline. Every environment is different, so there's no way to articulate one overarching package-creation policy or strategy that will work for everyone. However, what I've explained here is a general approach you can use for building packages that automatically adjust to their environment. When you build packages without hard-coded references to resources, you keep them in the dark about where they're running, where resources and data are stored, where to find files, and even where configuration files are stored. For keeping packages location-independent, the less the package knows, the better.
End of Article
Prev. page
1
[2]
next page -->
