Visual Studio Standard Options

One of the reasons that Visual Studio is such a great platform is that it provides so many commonly required features for individuals, ISVs, shareware developers, or corporations that develop tools. The options and features about to be described are all provided as part of the Visual Studio Industry Partners platform. This is a publicly available SDK you can download free of charge from MSDN. The Business Intelligence team created the Business Intelligence Development Studio essentially as a premier partner with Visual Studio using the same SDK as third parties. For more information about this program, see MSDN at http://msdn.microsoft.com/vstudio/extend/vsipoverview/default.aspx.

What follows is a quick run through of the Visual Studio features as they apply to Integration Services. It's a testament to the flexibility and time-proven design of Visual Studio that the generalized features found in Visual Studio apply so well to specific domains such as Integration Services. Whole books have been written about Visual Studio, so this section covers those Visual Studio features that are specific to or useful for Integration Services.

The Solution Explorer

To get started, look at the place where most Integration Services work begins, the Solution Explorer. For such a simple tree view, there are a remarkable number of things you can accomplish using the Solution Explorer. To start out, make sure you open the sample solution for this chapter, S05-BIDS.

Notice that there are three projects in the solution. There is an Integration Services project named BIDS and two other projects. One is an Analysis Services project named DummyASProject; the other is an Integration Services project named DummyISProject. The dummy projects have been added to illustrate the nature of solutions and to provide a little more with which to work while discussing the Solution Explorer.

Tip

If you only have one project in the solution, by default the solution node is hidden. Likely, this is to eliminate the confusion that can result from having two root nodes. Also, the menu items for solutions are redundant if there is no more than one project in a solution. To always view the solution node, click Tools from the top menu, and then click Options. In the Options dialog box, select the Projects and Solutions tab and then the General node. Select the Always Show Solution check box and the solution node will be visible even when you only have one project.

Figure 5.32 shows the solution in the Solution Explorer.

Figure 5.32. The Solution Explorer is the central solution management window

The Solution Explorer Context Menu

As in other places in BIDS, a lot of the solution and project features are accessed with context menus. Figure 5.33 shows the context menu for the sample solution. This menu contains many of the same menu items as the main File menu, but is better focused around managing the solution. It also contains many of the same options as the Project context menu you'll see in a moment. The Solution Explorer context menu is provided for convenience. When you select one of the solution build menu options, for example, it simply traverses the tree of projects and calls the corresponding project build options on each contained project.

Figure 5.33. The Solution Explorer context menu provides quick access to solution management features

Build

Several build options are available in the Solution Explorer context menu. These are mostly self-explanatory, but it's worthwhile to understand some of the finer details of what these options do. The build options include the following:

• Build Solution Calls Build on all projects

• Rebuild Solution Calls Rebuild on all projects

• Deploy Solution Deploys each project according to its project settings

• Clean Solution Calls Clean on all projects

• Batch Build Opens the Batch Build dialog box for selecting which projects and configurations to selectively build

• Configuration Manager Opens the Configuration Manager dialog box for creating new project configurations

• Project Dependencies Opens the Project Dependencies dialog box to create dependency relationships between projects

• Project Build Order Shows the build order of projects

Note

Depending on the load and build state of your solution, some of the context menu options might not be available.

The Build Solution option traverses the tree of projects and builds each project. What happens when the project builds is wholly dependent upon the project type and its settings, which are discussed shortly. What is interesting here is how Visual Studio decides the order in which to build the projects. That's where the Project Dependencies and Project Build Order menu items come in. Figure 5.34 shows the Project Dependencies dialog box that opens when you select the Project Dependencies menu option. This dialog box allows you to describe to Visual Studio which projects depend on others. For example, you might have a custom task you're building in the same solution as the package you use to debug it. To ensure that the task is compiled and registered before the package attempts to run, you use the Project Dependencies dialog box to specify that the Integration Services project is dependent on the custom task project.

Figure 5.34. The Project Dependencies dialog box controls the project build order

The Build Order tab shows the build order that results from the dependencies. In the previous example, because the Integration Services project is dependent on the custom task project, the custom task project builds before the Integration Services project. In many cases, project build order isn't important. But if you ever have a project that depends on the successful output of another project in the same solution, ensure that the project dependencies reflect that relationship.

Set Startup Projects

The Startup Projects dialog box allows you to configure which projects start when you start debugging by clicking the Start Debugging button on the Standard toolbar, when pressing the F5 button, or when choosing Start Debugging from the Debug menu. To open this dialog box, click the Set Startup Projects menu option from the Solution Explorer context menu. Figure 5.35 shows the dialog settings for the BIDS solution.

Figure 5.35. The Startup Projects dialog box helps you flexibly debug projects

In Figure 5.35, the Startup Projects dialog box is configured to start the DummyISProject without debugging and to start the DataViewers.dtsx package in the debugger.

Caution

This can be a little disconcerting if you're not aware of what's happening. The DummyISProject pops up a command window with the output from DTExec.exe and shows a message box. When you launch a package without debugging, it executes the package using the package execution utility named DTExec.exe.

This is useful in cases in which you need to have one process running that another process uses. Often, this can come in handy when debugging a custom component or when you have a custom user interface for accessing a server system. You can set up one project to execute the user interface without the debugger while the other project is running the server within the debugging environment. Although these types of debugging scenarios are fairly rare, when you really need this kind of debugging support, it is invaluable.

The Project Context Menu

Figure 5.36 shows the Project context menu, which contains many commonly used menu options used to manage projects. These same options are also available from the main Project menu. One of the first things you'll likely notice is that there are a few of the same options that are on the Solution Explorer context menu. This is because the Solution Explorer context menu options call down to each of the projects and some project types have different behaviors for the different menu selections.

Figure 5.36. The Project context menu contains many commonly used options

Build, Rebuild, and Clean

Because packages don't compile like code projects, the Build, Rebuild, and Clean menu options operate a bit different than they do for projects like C# or Visual Basic. The Integration Services Build option copies all the packages in the project to a certain folder and if deployment is enabled for the project, it also copies some deployment-related files to a certain folder. Rebuild overwrites the binaries previously placed in the locations where Build placed them and Clean removes them from those locations. How these Build functions behave is largely dependent on the settings in the Project Properties dialog box. To configure these settings and where these files go, open the Project Options dialog box shown in Figure 5.37 by selecting Properties in the project's context menu.

Figure 5.37. The Project Properties dialog box lets you control build behavior

Properties

The Project Properties dialog box has three tabs. The first tab is the Build tab. The only setting on this tab is the OutputPath. This path tells Visual Studio where to place the package when you build the project. The default path is "bin". With this setting, Visual Studio creates a folder named "bin" below the project's home directory. So, if your project is at C:\Solutions\BIDS, the default setting is to place the packages in the project in the C:\Solutions\BIDS\bin folder. You can also specify the fully qualified path of a folder and Visual Studio dutifully places the package in the specified directory.

The second tab contains the settings that control if and where the deployment utility and files get created. The Deployment Utility tab in the project's Property Pages dialog box has three settings. AllowConfigurationChanges allows the person who runs the deployment utility to change configuration settings when installing on the destination machine. CreateDeploymentUtility turns on the feature when building. DeploymentOutputPath lets you configure where the designer drops the deployed files. Like the OutputPath on the Build tab, DeploymentOutputPath is relative to the location of the project. The default folder is bin\Deployment.

The third tab, Debugging, controls the settings related to debugging your packages:

• RunInOptimizedMode Turns on the Data Flow Task optimized mode, which attempts to improve performance by removing unused columns, outputs, and components from the data flow.

• InteractiveMode Indicates if interactive features, such as dialog boxes should be enabled. For example, the Script Task uses this setting to determine if it should pop up error messages when there is an error while running the task's script.

• Run64BitRuntime Uses the 64-bit runtime if it is available.

• StartAction Indicates what to do when debugging is started. Typically, this is set to ExecutePackage; however, if you're writing a custom client that programmatically creates or loads a package, you can set this to Program.

• StartApplication Indicates the application to launch when debugging. If you've selected Program for the StartAction, this is where you need to specify the application to run.

• StartObjectID Specifies the package to execute.

• CmdLineArguments Specifies the command-line arguments to pass to the package.

Unload Project

Unload Project does just what it says. It unloads the project and closes all the files it contains. At first sight, this probably doesn't seem too useful. But there are some advantages to unloading projects, for example, if you have a lot of projects in one solution, unloading projects makes the solution load faster. To reload a project, you simply right-click on the project and select Reload from the context menu.

Set as StartUp Project

This menu item is a shortcut to the setting you make in the Set Startup Projects dialog box mentioned previously. If you select this option, the startup project listed in that dialog box changes accordingly.

The SSIS Packages Node Context Menu

Figure 5.38 shows the SSIS Packages context menu. This is an important menu for managing packages in the Visual Studio environment. It provides an easy way to create new or add existing packages to the project.

Figure 5.38. The SSIS Packages context menu provides several ways to add packages to the project

Each of these options results in a new package being added to the project. The first option is New SSIS Package to create a new package. Select this option and the new package is created and opened in the designer for you. The SSIS Import and Export Wizard option opens the Import/Export Wizard and generates a package. The Migrate DTS 2000 Package option launches the DTS 2000 Migration Wizard, which can generate one or many packages. Finally, the Add Existing Package option allows you to select packages to add to the project that are stored in SQL Server, the file system, or the SSIS Package Store.

Data Sources and Data Source Views

Data sources are small files that contain information about how to connect to a server and some metadata; they are essentially a connection string saved to a file. Data Source Views are objects that allow you to create a view on a source of data. You create both by right-clicking and selecting the single create option. For Data Sources, select New Data Source. To create a new Data Source View, select New Data Source View.

The Individual Package Context Menu

By now, you should be getting the idea that context menus are really powerful and that there are a lot of them in Visual Studio. You might find little use for the menu bar menus after you've grown accustomed to context menus.

The final context menu covered here is the one found when you right-click on an individual package. Figure 5.39 shows that context menu.

Figure 5.39. The context menu on individual packages provides important package-specific options

Set as Startup Object

This menu item is project specific and tells BIDS which package you want to execute when debugging. This is useful when your Integration Services project has more than one package.

Execute Package

This is a quick way to execute a package and is especially useful if you have used the Set as Startup Object option to set a different package as the default to run when debugging. If this menu option didn't exist, you'd have to change the startup object setting every time you wanted to run a different package than the currently selected startup object.

View Code

This menu item opens an XML viewer with the contents of the package. This is useful when you want to edit the settings in the package directly instead of through the designer view.

Caution

Microsoft does not support editing the package directly in this fashion. However, studying a package in Code View can yield some interesting and helpful information. If you ever use Code View to make changes to your package, you should make sure that you back up the package first. It is extremely easy to break a package in Code View and should not be attempted unless you know what you are doing.

View Designer

This menu item opens the package in the designer.

Exclude from Project

This option removes the package from the project, but doesn't delete the package file from the file system. To include the package in the project again, you can select the Add Existing Package option from the Project context menu.

Editing Menu Options

These options do as they say, but be careful. The Delete option not only removes the package from the project, it deletes the package file from the file system.

Reload with Upgrade

This option loads the package, but sets a special property on the package that instructs Integration Services to attempt to upgrade any tasks that are referenced in the package to newer versions of the component if available.

These are the important features of the Solution Explorer. Not all of them are covered here, so take a few minutes and look around the Solution Explorer some more. Right-click on everything in the Solution Explorer window. See if you can find some context menus or other features that aren't mentioned here. You'll want to get to know the Solution Explorer well because it's where almost all project-level management starts.

The Properties Window

The Properties window is another Visual Studio idiom that provides a lot of power to the Integration Services designer. With the Properties window, you can quickly see and modify the values for properties on any object in the package. This is important because packages are very declarative, or, in other words, a package's execution time behavior is dependent on its design time settings. By default, the Properties window is positioned on the right side of the designer. If it isn't open yet, right-click on any task, variable, or package, and select Properties.

Figure 5.40 shows the Properties window for the sample package called BIDS in the sample solution for this chapter. There are a lot of properties there and as you select different objects in the designer, the set of properties changes.

Figure 5.40. The Properties window shows properties for package objects

Variables Window

The Variables window is where you create, modify, and delete package variables. Figure 5.41 shows the Variables window with the default settings.

Figure 5.41. The Variables window shows a variable's name, scope, namespace, type, and value

Along the top of the Variables window are five icons for managing variables. The following is a list of the icons and their purpose in order from left to right.

• Create New Variable Creates a new user variable.

• Delete Variable Deletes an existing variable.

• Show System Variables Shows/Hides variables in the System namespace. There are quite a few system variables and they can be overwhelming. This button allows you to simplify the variable view a bit.

• Show User Variables Shows all user variables in the entire package, not just those in scope.

• Choose Variable Columns Allows you to change the columns that are visible in the Variables window. This is how you make a variable's namespace visible or hide other properties, such as scope or data type.

Figure 5.42 shows the Column Picker dialog box for the Variables window.

Figure 5.42. Use the Column Picker dialog box to modify the visible columns

The File Menu

Although you are likely familiar with the File menu, two menu choices are worth exploring in more detail. They are the Save Package As and the Save Copy of Package As menu options. These two menu items don't behave exactly as you might expect.

Save Package As

Save Package As allows you to change the name of the package, but it also affects a few other things that are not so obvious:

• The designer leaves a copy of the package with the old name in the same folder.

• The designer provides the option to change the name of the package object as well. This changes the package name that you see in the properties grid.

• The source control history is lost for the package in Source Safe.

None of these side effects of changing a package name is severe or detrimental, but it is good to understand them so you're not surprised when it happens. For example, you might rename your package several times and then, upon viewing the package folder and finding more than one package, become confused as to which package is the current one. Just be aware of this behavior and you should be able to avoid such confusion.

Save Copy of Package As

This menu option is a deployment shortcut. It allows you to save the package to a location other than the solution folder. When you select this menu item, a dialog box opens that lets you save directly to the Integration Services package store, SQL Server, or the file system.

Caution

This menu option can be a little hard to find because it is only visible when there is a package loaded and you have selected one of its designers.

Options Dialog Box

A wealth of settings can be found in the Options dialog box. To get there, click Tools on the main menu, and then click Options. From this dialog box, you can change the look and feel of the designer, alter file-handling behavior, change fonts, set up key mappings, and control just about every other setting in the environment. If you haven't looked around this dialog box before, it's worth a few minutes to just select each node and familiarize yourself with the settings found there.

There are too many settings to cover them all here, but there are a few Integration Services settings worth mentioning.

Business Intelligence Designers

These settings control the Analysis Services and Integration Services designers. Under the Integration Services node, there are three subnodes.

General

On the General tab are the settings for digital signatures for ensuring that packages are signed. There is also a setting for people who have color differentiation problems. The Show Precedence Constraint Labels check box enables labels to show whether a constraint is for success, failure, or completion. Figure 5.43 shows precedence constraints with this setting enabled.

Figure 5.43. Show Precedence Constraint Labels clearly show constraints

Auto Connect

Figure 5.44 shows the Options dialog box with the Control Flow Auto Connect tab selected. This dialog box allows you to configure how the designer adds tasks to the design surface when you double-click on it in the toolbox. This is really handy when you build packages quickly, such as when building a lot of packages, or if you just can't stand building packages from left to right. The Data Flow Auto Connect dialog box serves the same purpose, but for the Data Flow Task.

Figure 5.44. Auto Connect lets you decide the default visual layout for components

Environment

On the Environment tab, there is a Fonts and Colors subtab. Selecting this tab shows a dialog box with some hard-to-find font settings. With the Fonts and Colors subtab selected, select the Show Settings For drop down and select Business Intelligence Designers. Play with these settings a bit and watch where the fonts change in the designer.

Debugging Windows

Occasionally, a package will have a bug or behave differently than designed, making it necessary to debug it. The following windows provide a view into the package that would otherwise be difficult or impossible to obtain. Wise use of these windows can make debugging packages much simpler. Often, by simply setting a breakpoint and watching the value of a variable, you can quickly find the reason behind package problems. Get to know these windows well.

The Breakpoints Window

The Breakpoints window shows all the breakpoints in the package and you can configure the window to show different properties using the Columns selection menu. Figure 5.45 shows the Breakpoints window with one breakpoint set. There are seven buttons at the top of the window for modifying the existing breakpoints.

Figure 5.45. The Breakpoints window helps to manage package breakpoints

Caution

The New button does not function in Integration Services. It's provided to support creating new breakpoints in code-based projects and currently cannot be used to create breakpoints in packages.

The Breakpoints window is the only debugging window that is visible while in design mode. The remaining debugging windows are only available while executing a package. To open these windows, select the Debug menu option from the main menu and then select the window you want to open from the Windows menu.

The Watch Window

The Watch window is useful for viewing values of objects as they change during execution. Currently, the Integration Services designer only supports viewing variables in the Watch window. You can view the value of a variable by dragging it from the Variables window and dropping it on the Watch window. Figure 5.46 shows the Watch window with the system variable CreationName added. This is the only way the designer provides a way to view the value of variables during execution.

Figure 5.46. The Watch window lets you view variable values at execution time

Caution

Often, users attempt to view the value of a variable in the Properties window during execution. Because the designer creates a separate instance of the package for execution, the values displayed in the Properties window reflect the package state at design time and never change. The values of the variable in the separate instance of the package can only be viewed through the Watch window or the Locals window.

The Locals Window

The Locals window shows all local-scoped variables and some key properties of the currently selected container. Figure 5.47 shows the Locals window for a package that is currently suspended after hitting a breakpoint on a Script Task. Notice that there are three variables in the User namespace: User::ScriptTaskScopeVariable, User::SequenceScopeVariable, and User::PackageScopeVariable. Each of these was created at different variable scopes, as indicated by the names. If a variable is not in scope for a task or container, it will not show up in the Locals window. This is useful when trying to understand why a task is unable to find a variable or when you just want to see all the variables in a snapshot. Why would you use the Watch window instead of the Locals window then? The Watch window shows you the value for a variable at any time during the execution of the package, not just when it's in scope. It's also easier to focus on the value of a variable in the Watch window than in the Locals window.

Figure 5.47. The Locals window lets you view key values and variables in scope

The Call Stack Window

The Call Stack window shows you how the package arrived at its current execution location. This debugging window is useful when you have a hierarchy of containers and you need to see how the package flows through that hierarchy. For flat packages, packages without much hierarchical structure, the Call Stack doesn't provide much value. However, if you have multiple embedded containers, this window can help you better understand your package.

Figure 5.48 shows the Call Stack window next to the package it documents. Notice that the package is at the bottom of the call stack. This will always be true. The stack grows up from the bottom in reverse order from the execution order. Notice that the Script Task is at the top of the call stack because it is the task that was executing when the designer hit a breakpoint. The Language column typically shows the language used to develop the particular function call. Because Integration Services isn't a language, this column is empty.

Figure 5.48. The Call Stack window shows the execution order of containers

Diagnostic Windows

The diagnostic windows are useful for troubleshooting packages or to simply understand how a package works. These are the first windows you should consult when trying to fix a package that fails validation or execution. They contain most of the information that the package exposes about its execution, errors, warnings, information events, execution times, planning, and so on.

The Error List Window

The Error List window provides a quick way to see the errors, warnings, and information events produced by the current package. Figure 5.49 shows the Error List window after loading a package containing an unconfigured FTP Task and an invalid configuration. The buttons at the top of the window let you filter which type of message you want to see. If you only want to see errors, you can toggle the warnings and messages off by simply clicking on the corresponding buttons.

Figure 5.49. The Error List window shows errors, warnings, and messages

There are a couple of hidden features in the Error List window of which you should be aware. First, if there is a dialog box for the task that generated an error and you double-click on the error, the designer opens the task dialog box for you. This makes it really handy when building a package, especially large packages, to quickly find and correct the error.

Second, if you right-click on the errors, warnings, or messages, you can choose the Show Help option from the context menu. This menu option displays more information about the error, warning, or message in your default browser. At the time of this writing, there were very few of the messages documented in this way. The plan is to gradually add additional documentation as the message is better understood and as more documentation becomes available. This feature makes it possible to continually update the documentation for a given message and even add links to other sources of information, such as magazine articles, Knowledge Base articles, or MSDN resources.

The Output Window

The Output window is a favorite window in the designer. It provides a relevant and continuous flow of information as the package executes. The information is a combination of diagnostic messages, package status, and debugging information. Figure 5.50 shows the Output window with an example of the kind of information it provides.

Figure 5.50. The Output window shows package diagnostic information

Figure 5.50 also shows the buttons and options at the top of the window. The drop down allows you to select the type of output you want to view. The second button from the right with the small, red x clears all content and the button on the far right allows you to toggle word wrap, making it easier to see very long messages.

The Task List Window

The Task List window, shown in Figure 5.51, isn't really a diagnostic window, but it can be used to track a list of things you need to do. Don't confuse "Task" with SSIS tasks. This is the more generic term that means "things to do."

Figure 5.51. The Task List window helps you organize your remaining work items

Two controls are at the top of the Task List window. The drop down lets you choose whether to view comments or user tasks. The button with the check mark creates a new task. To delete a task, right-click on it, and select Delete.

The Server Explorer Window

Visual Studio provides access to servers with the Server Explorer window. To see this window, select the View menu and then select Server Explorer. The Server Explorer window lets you create new databases, explore SQL Server and other servers, view Windows System event logs, traverse the Windows Management Instrumentation class hierarchy, manage MSMQ Queues, view performance counters, and view the services control panel. It's one more way that the Visual Studio simplifies access to your system. Figure 5.52 shows the Server Explorer window.

Figure 5.52. The Server Explorer window lets you access server-related settings

Note

The Server Explorer window is not available in all editions of Visual Studio 2005. Also, in some editions of Visual Studio 2005, the Server Explorer window is called the Data Explorer or Database Explorer. You must have Visual Studio 2005 installed to get some Server Explorer features, such as the Management Classes node.