Chapter 8: Deployment

Most enterprise environments rely on consistent and reproducible patterns in development to keep cost down and deployments predictable. Whether your environment is an enterprise-sized company or a smaller company, you want minimal work in order to move your solution through development, quality control, and production. This chapter shows a number of options to help you with development of your SSIS solution, and shows how to lower your total cost of development by creating simple, easy migrations from environment to environment.

Working with a Team in SSIS

As you can imagine, in most companies, there isn’t just a single person developing SSIS packages for a single project. Generally, for most medium and large companies, there would be a few people developing the project’s ETL, and they would need to collaborate on the solution. Because of this, source control becomes mandatory for keeping one developer from coding on a package that another developer is already working on. Another tactic is to break large packages into smaller, more modular packages to allow for multiple components to be worked on at the same time. Additionally, package templates can create consistency between the developers.

Source Control Integration

A key ingredient to a stable development environment is going to be source control. Source control ensures that only a single developer can work on a package at any given point in time, and, if a problem occurs, you can roll back code to a previous version. The great thing about the SSIS development environment in BIDS is that it can take advantage of the Visual Studio 2005 source-control integration. One of the most popular source control systems is Visual Source Safe (VSS). In this section, you’ll see how easy it is to integrate your SSIS project into Source Safe 2005. You can also use Visual Studio Team Systems, a concept that is covered in the book Professional SQL Server 2005 Integration Services (Wiley Publications, 2006).

First, however, a word of caution about source control. As with any proper development process, as you add control, you sometimes lose flexibility. The advantage of source control systems is knowing who made changes and when, but the disadvantage is you lose a little of the “cowboy development” that you may be used to (where you can make whatever changes you want). However, the benefits of source control far outweigh the negatives.

With Visual Studio, you can either create a fresh project that integrates into VSS from the onset of the project’s creation, or you can add an existing project into VSS. If you have VSS installed, the integration options automatically show up in BIDS. The VSS architecture will allow you to store your project locally on your machine, or on a central server for all the developers to check code in and out of. It is most certainly recommended that you install the VSS repository on a central server that is regularly backed up.

Adding a New Project

Assuming that you already have VSS installed, let’s create a new project that will be checked into the repository. Open BIDS and create a new SSIS project called TestVSSProject. Ensure that the Add to Source Control option is checked and click OK. The Add SourceSafe Database Wizard will then open. On the first screen, choose the Create a New Database option and click Next. On this first screen, you could also add the project to an existing VSS database. In the Location tab, find a good location to place the files. However, if your VSS repository is installed locally, type C:\ExpertSSIS\VSS. In the next screen, type ExpertSSIS for the Connection Name option.

The next screen is the Team Version Control Model, shown in Figure 8-1. This screen is where you choose whether you want the file to be locked when your developers are modifying it, or whether you want for them to operate off of a local copy and then merge the packages back in. In SSIS package development, even though it’s less functional, it is recommended to select the Lock-Modify-Unlock Model option. SSIS packages are not the type of program that can be easily merged, and it can result in errors if you do this inappropriately.

Figure 8-1: Team Version Control Model

After the confirmation screen, click Finish. The VSS database will then be created behind the scenes, and you’ll be prompted for your Windows credentials to log in to the database. After you log in, you’ll see the Add to SourceSafe dialog box shown in Figure 8-2. Click OK to add it to the root.

Figure 8-2: Add to SourceSafe dialog box

The solution, project, and default package.dtsx file will now be added into VSS under a folder called TestVSSProject.root, and potentially a directory under that. In the Solution Explorer, you will see a new icon to the left of each package, project, and solution (shown in Figure 8-3). This icon represents the state of the file. A check mark means the file is checked out, and no one can access it if you selected exclusive mode earlier in the project’s creation. A plus sign represents a new file, and a lock means that the file is checked in and no one is accessing the file for write access.

Figure 8-3: New icon to the left of each package, project, and solution in Solution Explorer

You can right-click any file to see a menu structure that now includes source control integration. If you are just starting your day, it is wise to right-click the project or solution and select Get Latest Version. This will ensure that the local copy on your machine is up-to-date with VSS. If a file isn’t up-to-date, you’ll be prompted to replace your local copy. Only get the latest version of files if you have already checked in your files. Otherwise, you can overwrite your files inadvertently.

As you make changes to the packages, the files will check out automatically and no one else can access them. When you’re finished with your changes at the end of each day, right-click the package and select Check In. This will open the Check In dialog box shown in Figure 8-4, where you can add comments about what you’re checking in. This will show up later in reports, or when other developers are trying to figure out what changes you made in a build.

Figure 8-4: Check In dialog box

One of the most useful screens you can use is the Pending Checkins window shown in Figure 8-5. You can access the window under the View menu. The screen enables you to see everything that you have not checked in. You can also quickly add comments to each file here. If you check each file and then click the Check In button, everything will be checked in for you.

Figure 8-5: Pending Checkins window

It’s important to note that when you create a new package or rename a package, your project is automatically checked out behind the scenes, and no one else can create a new package or rename a package until you check in the project. The same applies when you create a new project under the solution. Because of this, ensure that you only have the project checked out for a very limited period of time.

After you’ve created multiple versions of a file, you can right-click the file and select View History. This opens the History dialog box, which enables you to select which release you’d like to retrieve by clicking the Get button. You can click Diff to see the difference between two releases. This is not as useful in SSIS, though, because VSS will detect changes between the back-end XML files, which are not very readable.

VSS is a powerful way to ensure that one developer doesn’t step on another developer’s code. It also helps you properly document builds and roll back from a mistake. You can also add an existing project to VSS by going to File Source Control and following the same prompts that you followed to add a new project.

As you open a file and make a slight change, BIDS will automatically check out the package and lock others from editing the package. Some find this quite annoying, as they may only want to have made a quick change locally uncoupled from VSS. One tactic to fix this is to go to Tools Options. Then, under Source Control Environment (shown in Figure 8-6), you can set the On Edit option to Do Nothing, which will not automatically check out the package any time you happen to make a small change. The risk with doing this, however, is that you may make several small or large changes and forget to check them in. So, changing this option requires that you have discipline to check the packages in and notify the other developers manually.

Figure 8-6: Environment Options screen