The Integration Services Options

After you've created an Integration Services solution and you have the package open, a number of important changes occur in the designer. The most obvious changes are that certain menus become available and some windows open. Following is a discussion of some important changes that result when creating an SSIS project. As you work through this chapter, important Integration Servicesspecific features are pointed out, but also try to keep an eye open for how the environment adjusts as you make suggested changes. This simple exercise can save you some later frustration and put you further down the learning curve by simply being extra observant.

The SSIS Menu

With the Integration Services project and a package open, a new SSIS menu becomes available between the Data and Tools menus on the main menu. The SSIS menu has the following menu items:

• Logging Set up logging options

• Package Configurations Create and manage package configurations

• Digital Signing Sign packages

• Variables Show the Variables window

• Work Offline Toggle offline mode

Caution

If you select the Work Offline menu item, be aware that in some cases, you might get errors. For example, if you are building a data flow and you attempt to access the Columns tab in the component user interface of an OLEDB connection, you receive the following error:

Error at Data Flow Task [OLE DB Source [1]]: An error occurred due to no connection. A connection is required when requesting metadata. 

If you get this error, ensure the Work Offline setting is disabled.

• Log Events Show the Log Events window

• New Connection Create a new connection manager

Caution

Keep this in mind if you ever attempt to access a menu and it's not there. In Visual Studio, the available menu options often vary depending on the currently selected designer window or the object selected within the window.

This menu only shows all menu items when the Package Designer pane is selected. If you click on one of the other windows such as the properties grid, the only option available is Work Offline. The SSIS menu and some additional menu items are also accessible by right-clicking on the package workflow designer surface. Figure 5.4 shows that menu.

Figure 5.4. The SSIS menu is available by right-clicking on the control flow designer

The additional interesting menu items include the editing options for Cut, Copy, and Paste. An additional option is available to add an annotation, which is a way of adding comments and is a good way to document the package.

The Edit Breakpoints menu item is how you add and remove debugging breakpoints. Figure 5.5 shows the Edit Breakpoints dialog box that opens when you choose to edit breakpoints.

Figure 5.5. The Edit Breakpoints dialog box lets you set and remove breakpoints

The Toolboxes

The toolboxes are where the tasks and data flow components are accessible. Two Visual Studio toolboxes are unique to Integration Services. Figure 5.6 shows the toolbox for the control flow with tasks in it. You can control which tasks are visible in the toolbox by adding and deleting items from it.

Figure 5.6. The Control Flow Items toolbox contains all currently selected tasks

Tip

You can also select the List View option to turn off List View mode in the toolbox. If you liked the DTS look with only icons, that might appeal to you. That option turns off the text portion of the tool and only shows the icon.

To delete an item from the toolbox, simply select it, and then press the Delete key. This does not uninstall the component from the machine, it simply removes it from the toolbox so that it is no longer visible.

To add an item to the toolbox, right-click on the toolbox anywhere and select the Choose Items option.

The Choose Toolbox Items dialog box opens, as shown in Figure 5.7. If you happen to accidentally delete an item from the toolbox, simply open this dialog box, put a check next to the item you deleted, and click OK. It then reappears in the toolbox. You can also remove items from the toolbox by deselecting the item in this dialog box. As you can see, the dialog box has tabs that organize the components into subgroups. For Integration Services, the three tabs of interest are the SSIS Data Flow Items, the SSIS Control Flow Items, and the Maintenance Tasks.

Figure 5.7. The Choose Toolbox Items dialog box allows you to add tasks to the toolbox

The Designers

This topic has the potential to be confusing; this book uses the term "designer" to describe the overall Visual Studio environment. It also uses "designer" to describe the windows in which you build packages. The context of the discussion should help you know which designer is intended. Figure 5.8 shows the designers as they appear just after creating a new empty package.

Figure 5.8. The designers are the windows in which you build the package

Note

Seasoned DTS users might be caught off guard by the separation between the design time environment (BIDS) and the management environment, SQL Server Management Studio. Microsoft has received more than one panicked email in which a customer wonders why the design environment was removed from the management environment and wondered how Microsoft expects anyone to create packages without it. In DTS, the design environment was embedded in the management environment, Enterprise Manager. But for SQL Server 2005, Microsoft made a distinction between design and management activities to reflect the separation of responsibilities that exist in many enterprise environments. BIDS is targeted at developers who are creating packages, cubes, reports, or, in other words, developing solutions. Management Studio is targeted at database administrators and operators. See Chapter 17, "SQL Server Management Studio," for more about that environment.

The Control Flow Designer

Clicking on the first tab to the left takes you to the Control Flow designer, where you create tasks, precedence constraints, and connections. You can think of this designer as the top-level package view because it shows the package at the highest level of detail. It's also true to its name and shows the flow of control from task to task.

Naming Best Practice

When I was growing up, my mother used to always tell me to "Clean up as you go, so you don't end up with messes like this!" Now I'm a dad and I find myself telling my kids this a lot. As it turns out, this advice also works well with building packages. When you're building a package, it can get pretty messy, pretty fast. To avoid creating a big messy package, keep this rule in mind.

"When you drop it, name it."

This means that whenever you drop an object on a designer surface, create a new connection manager, or do anything else that results in the creation of a new package object, give it a name and a description. You will best understand the purpose for the object you are creating at the time when you create it. Although this takes a bit of extra time, it is immensely better than the alternatives, such as scratching your head at 2:00 a.m. wondering why you created that connection manager or trying to wrap up a project by doing a commenting pass on packages you wrote months before.

Use a naming convention. Be verbose in your descriptions. Whatever conventions you choose to follow, make sure you follow them consistently and "Clean up as you go."

Drop a Data Flow Task onto the Control Flow designer surface. Drop a Script Task as well. Notice the dangling pointer beneath the task. That's a precedence constraint and is called a dangling connector. Drag it from one task and drop it onto another to create a precedence constraint between the two tasks. The precedence constraint controls the order in which the two tasks execute. Drag one more Data Flow Task onto the Control Flow designer so you have some tasks to work with on the other designers.

Note

Often, you might have the need to create a proof-of-concept package or might just need temporary placeholder tasks while you "sketch out" the design of a package, but you don't always know what tasks you'll need or even how you want the final package to be structured. In other cases, you might need a common starting point from which to begin the package, but you don't need the starting point to do anything. For example, you might want to execute a different part of the package depending on some condition. In these cases, it would be nice to have a placeholder or dummy task.

The Script Task is just such a task. By default, the Script Task returns success and does nothing else. Later, you can replace it with other tasks or modify it to add execution value. So, in addition to being a powerful customization option, it's helpful to think of the Script Task as a placeholder task.

To open the editor for a given task, simply double-click on it or right-click and select Edit from the context menu.

The Connections Tray

Below the Control Flow designer, there is a tabbed window called the Connection Managers window or Connections Tray. This is where you create, manage, and delete connections and it's available in the Control Flow, Data Flow, and Event Handler designers. To create a new connection manager, right-click on the Connections Tray and select the type of connection you want to create from the context menu shown in Figure 5.9.

Figure 5.9. The Connection Managers menu allows you to create new connection managers

If the type of connection you want to create isn't directly available to you in this menu, you can choose the New Connection option, which opens the Add SSIS Connection Manager dialog box shown in Figure 5.10.

Figure 5.10. The Add SSIS Connection Manager dialog box shows all available connection manager types

The Add SSIS Connection Manager dialog box shows all the connection manager types that are installed on the working machine. To create a connection manager from this dialog box, click the Connection Manager type you want to create, and click the Add button. The creation dialog box for the type of connection manager you chose opens.

The Data Flow Designer

Now let's take a look at the Data Flow designer. If you haven't dropped two Data Flow Tasks onto the Control Flow designer, do so now. To get to the Data Flow designer, you can either double-click on the Data Flow Task or click on the Data Flow tab.

Tip

Double-clicking the Data Flow Task is the preferred way to get to its designer, because it always takes you to the correct designer. If you have more than one Data Flow Task in the package, clicking on the Data Flow tab takes you to the Data Flow designer for the currently selected Data Flow Task, which might not always be the one you want to edit. Also, if there is a task selected and it's not a Data Flow Task, when you click on the Data Flow tab, the Data Flow designer for the Data Flow Task that was created first opens. So, it's easiest to just double-click on the Data Flow Task you want to edit.

Figure 5.11 shows the Data Flow designer. Notice that there are two available to edit, "Data Flow Task" and "Data Flow Task 1." Having two illustrates how the Data Flow designer supports more than one Data Flow Task per package. You can select which task you want to edit in the drop down at the top of the designer window.

Figure 5.11. The Data Flow Task designer allows you to choose which task to edit

After you're in the Data Flow designer, drag an OLEDB Source component onto the designer and double-click it. This opens the OLEDB Source Editor. Create a connection manager by clicking on the New button and point the connection manager at any server you have running. Select the Table or View data access mode (default), and then select any table, such as the CompanyAddresses table from the AdventureWorks sample database. Click the Columns tab on the left and the columns should be correctly mapped from the input table to the output.

Customizing the Toolbox

With the Data Flow tab selected, turn your attention to the toolbox. Notice that it looks similar to the Control Flow toolbox, but has different components in it. It functions the same way and you can change the contents in the same way as the Control Flow toolbox. However, you can customize the toolbox in a few more ways. If you right-click on the toolbox, the context menu shown in Figure 5.12 appears. Along with the Choose Items menu item previously mentioned, a few more options are worth noting here.

Figure 5.12. You can modify the toolbox using the context menu

The Reset Toolbox option is an easy way to reset the toolbox to the entire set of components installed on the machine. So, if you've deleted a bunch of components or reorganized them in a way that doesn't make sense to you, it's a simple way to return to the default toolbox setup. It's fairly expensive to do, however, and takes quite a while to complete. So, only do this if you're prepared to wait a while.

The usual editing commands, Cut, Copy, and Paste are available for moving items around too. So, you can cut or copy a component and paste it somewhere else in the toolbox. You can also rename components, and so forth.

Figure 5.13 shows a sample Data Flow toolbox. Notice at the top there is a tab called "My Favorite Components."

Figure 5.13. The toolbox is customizable

The toolbox supports creating your own tabs. This might be useful to you if you only typically use a small number of the components or tasks and are always searching for those items. You can create your own tab with only those items you use the most and make them much easier to find. To create your own tab, select the Add Tab menu item from the context menu.

Tip

The thumbtack in the upper-right corner of the toolbox is available on all the docking windows, such as the toolbox, Solution Explorer, and properties windows. Clicking on that button toggles the Autohide state of the window. If you toggle the Autohide state off, you can also drag these windows away from the main window edge and they become free (nondocking) standing windows.

Data Flow Component Context Menus

In the Data Flow designer, if you right-click on one of the components, a context menu appears. Figure 5.14 shows the context menu for the OLE DB Source component.

Figure 5.14. Data flow components have a context menu

There are the standard editing options and the Show Advanced Editor menu item. The Advanced Editor provides a way to edit component properties that are not normally exposed through their custom interfaces. For example, column types are not visible in the standard UI for the OLE DB Source, but they are visible in the Advanced Editor. Figure 5.15 shows the Advanced Editor.

Figure 5.15. The Advanced Editor exposes advanced component settings

The Autosize menu item is a quick-and-easy way to change the size of the task or component to match the size of the name. To see it work, simply change the name to be much longer or shorter and select the option in the context menu; the task or component box will be sized to fit the text.

Tip

Another useful formatting command is the Autolayout menu under the Format main menu. Select this option to have the designer organize the components on the designer. This is useful when your package gets a little visually unruly and you need to quickly reformat it. To get there, select the Format menu, and then select the Autolayout menu option.

Control Flow Component Context Menus

Tasks also have a context menu, as shown in Figure 5.16. The Task context menu has a few interesting menu choices. The Execute Task menu item executes the task individually. Only the task that was selected when you open the menu will execute.

Figure 5.16. The Task context menu is a quick way to access common features

Note

The context menu for containers also contains a menu option called Execute Task. That is a misnomer and in SP1 will likely be changed to Execute Container. Selecting this menu option will, of course, execute the container and its children only.

The Edit Breakpoints menu item opens the same dialog box shown previously; however, the difference is that the breakpoints are set on the task and not on the package.

The Group menu item creates a visual grouping container. If you select multiple tasks or containers and select this option, the designer creates a grouping container around all the selected objects. To remove a group without deleting its contents, right-click on the title bar for the group container and select Ungroup.

Note

The grouping container has the same look and feel as other containers, such as the Sequence or Foreach Loop. It contains other containers and tasks and you can collapse and expand it. However, it is not a true runtime container and does not provide the same services as true runtime containers, such as variable and logging scope. It only exists in the designer environment and does not affect execution.

The Zoom option is also available on the main menu under the View menu tree. This option lets you change the size of the objects in the designer. This is useful when you need to see more of the package simultaneously. Or, if you want to show the package in a demonstration, it's helpful to make the tasks larger for easier viewing.

Figure 5.17 shows the designer panning feature. In the lower-right corner of the designer, there is a small black cross with arrows. It is only visible if the package view is larger than the designer surface. If you click on that small button, it shows a view window with the contents of the entire package called the Navigator window. You can use the Navigator to quickly move or pan to different parts of large packages. The Navigator is available in all the box and line designers. To see it work, make the designer window small enough so that a task or component is outside the visible design surface.

Figure 5.17. Panning makes package navigation simple

Error and Warning Indicators

When tasks and components have errors or warnings, the designers indicate that to the user by placing small icons in the task and component. Figure 5.18 shows the Execute SQL Task after it has just been dropped onto the designer surface. Moving the mouse over the icon shows the error or warning.

Figure 5.18. Tasks with errors have a small, red, letter X icon

Figure 5.19 shows the Send Mail Task with a warning. Tasks and components raise warnings in various cases. For example, if a task has a setting that could possibly result in a failure or if there is some noncritical setting missing, it should raise a warning. In this case, the Send Mail Task raised a warning because there was no subject provided.

Figure 5.19. Tasks with warnings have a small, yellow, exclamation icon

Data Flow Paths and Viewers

To show you some of the Data Flow designer features, you need to build a data flow. The following instructions show you how to build a data flow that builds a contact list for all employees in the AdventureWorks table with a name that starts with the letter M and puts it into a flat file. Alternately, you can open the S05-BIDS solution in the samples folder, which has this package already created.

Setting Up the OLE DB Source

In this step, you'll set up the Data Flow Task and the OLE DB Source Adapter. Part of the setup for the OLE DB Source is creating an OLE DB Connection Manager as well.

1.	Delete all the tasks or create a new package.
2.	Drop a Data Flow Task onto the Control Flow designer.
3.	Double-click on the Data Flow Task.
4.	Drop an OLE DB Source Adapter onto the Data Flow designer.
5.	Double-click on the OLE DB Source Adapter to bring up the UI.
6.	On the Connection Manager tab, create a new OLE DB Connection Manager by clicking the New button.
7.	In the Configure OLE DB Connection Manager dialog box, click the New button.
8.	For the server name, select the local server where you've installed the AdventureWorks sample database.
9.	For the database, select the AdventureWorks sample database.
10.	Click the Test Connection button to ensure you've got a valid connection.
11.	Click the OK button in the two dialog boxes to return to the OLE DB Source Editor.
12.	Ensure the Data Access mode is set to SQL Command.
13.	Type the following query into the SQL Command text, as shown in Figure 5.20: Figure 5.20. Use the OLE DB Source Editor to set up the source SELECT EmployeeID, FirstName, LastName, JobTitle, Phone, EmailAddress, AddressLine1, AddressLine2, City, StateProvinceName, PostalCode FROM HumanResources.vEmployee WHERE FirstName LIKE 'M%'
14.	You can click the Preview button if you want to see the rows that result from the query.
15.	Now, select the Columns tab in the upper-left corner. This dialog box establishes which columns will be used in the data flow. For now, don't worry about the Error Output tab.
16.	Click the OK button.

Setting Up the Flat File Destination Adapter

Now, you can set up the Flat File Destination Adapter. While still in the Data Flow designer, complete the following steps:

1.	Drop a Flat File Destination component onto the designer.
2.	Drag the dangling green connector from the OLE DB Source and drop it onto the Flat File Destination.
3.	Double-click on the Flat File Destination.
4.	Click the New button to create a new Flat File Connection Manager.
5.	Choose the format you want to use, such as delimited.
6.	Click the OK button.
7.	The next dialog box is the Flat File Connection Manager Editor. Click the Browse button to find the folder to create the new flat file or type the filename in directly, for example, D:\TEMP\EmployeeContacts.txt.
8.	Click on the option to set column names in the first data row.
9.	Figure 5.21 shows the General tab of the Flat File Connection Manager Editor after it has been set up. Figure 5.21. Use the Flat File Connection Manager UI to specify the output flat file
10.	Click the Columns tab.
11.	Click the OK button.
12.	You should be back to the Flat File Destination Editor.
13.	Click the Mappings tab and check that the component correctly maps all the columns.
14.	Click the OK button.

Setting Up the Data Flow Path

The package should now be configured to extract the employees whose first names start with the letter M and you're now at a state at which you can examine data flow paths and viewers more closely. Figure 5.22 shows the Metadata tab of the Data Flow Path Editor dialog box. To open this dialog box, double-click on the path between the OLE DB Source and the Flat File Destination and click on the Metadata tab.

Figure 5.22. The Data Flow Path Editor shows the settings for data flow paths

With the Data Flow Path Editor open, click the General tab if it isn't already selected. A number of interesting settings and properties are available on this tab.

• Name The name is useful for documentation. Used well, it can document what data is going across the path. This path is named Employee Contact List.

• ID The ID is the unique identification number used to represent the path in the Data Flow Task. The Data Flow Task often shows the ID in errors that are related to the data flow path.

• PathAnnotation This setting controls the value the designer will use as the annotation for the path. There are four options available:

  • Never The designer shows no annotation.

  • AsNeeded The designer shows the annotations transforms provide.

  • SourceName The designer shows the source transform name as the annotation.

  • PathName The designer shows the name of the path as the annotation.

Figure 5.23 shows the data flow path with the PathName option selected. By changing the Name property, you can also change the annotation value that the designer displays.

Figure 5.23. The annotation can describe the data flowing through it

Setting Up the Data Flow Viewers

One of the more powerful debugging and troubleshooting features in the Data Flow Task is available in this dialog box on the Data Viewers tab. As data flows through the Data Flow task and across the data flow path, it would be nice if you could actually see the data as it flows. That's what Data Viewers do. They allow you to view the data as it flows through the Data Flow Task.

Click the Data Viewers tab and click the Add button to show the Configure Data Viewer dialog box shown in Figure 5.24.

Figure 5.24. Create and configure Data Viewers in the Configure Data Viewer dialog box

There are two tabs in this dialog box. Use the General tab for creating new Data Viewers. Use the Grid tab to configure the Grid Data viewer. Each Data Viewer type has a configuration tab with the same name as the type that you use for further configuring of the viewer. As you change the viewer type selection on the General tab, the name of the second tab changes as well. For this exercise, you'll create two viewers:

1.	From the Data Viewer dialog box, select the viewer type "Grid."
2.	Click the OK button.
3.	Click the Add button again.
4.	Select the Column Chart type.
5.	Select the Column Chart tab.
6.	Select the PostalCode column.
7.	Click the OK button twice to get back to the Data Flow designer.

Now, you're all set to see Data Viewers in action.

Running the Package

Press the F5 button or click the Start Debugging button to start the package running. The Data Viewers should appear immediately. Data Viewers are essentially breakpoints in the data flow. They temporarily stop the data from moving through the Data Flow Task to give you the opportunity to view it before it rushes off to the destination. You can look at the data as long as you want. The Data Flow Task waits until you allow it to continue. To tell the Data Flow Task to continue, use the buttons on the viewers. Figure 5.25 shows the Data Viewers as they appeared in the sample package.

Figure 5.25. Data Viewers allow you to interactively view data as it flows through the Data Flow Task

As you can see from Figure 5.25, there are three buttons on each of the Data Viewers. The Copy Data button allows you to copy the viewed data to the Clipboard. To allow the Data Flow Task to continue, press the small button with the triangle. This button tells the Data Flow Task to continue until it displays the next set of values in the viewer. Then it stops again. In this way, it's possible for you to view all the data that moves through the path on which the viewer has been created. You use the Detach button when you no longer want to view the data. Clicking it temporarily detaches the viewer from the flow and does not stop the Data Flow Task until the next time you run the package.

Viewers Interact

One of the interesting aspects of Data Viewers is that they interact. Figure 5.25 shows some of the data points selected. To do this, simply drag the mouse over the data points. Selecting rows in the Grid view also selects columns in the Column Chart view. The interactive nature of Data Viewers makes it possible to do real-time analysis or to simply spot-check the data. Certainly, it aids you in better understanding the shape of the data. In the example given, it's possible to quickly discover and retrieve which employees live within the 98020 ZIP Code.

The Progress or Execution Results View

When you execute a package, one of the changes that happens in the environment is a new designer window appears. It isn't that obvious because it only shows up as a tab at the top of the designers along with the Control Flow and Data Flow tabs. While the package is running, the tab is named the Progress view and is updated with important information as the package is executing. When the package is complete and you select the Stop Debugging button or [Shift+F5], the tab name changes to Execution Results and retains all the progress information from the package execution.

If your package fails for some reason, this is a good place to start looking for the cause. Any errors and warnings that the package raised during execution show up here as well as some other interesting information, such as execution order and duration. Figure 5.26 shows the Execution Results view as it appears after running the DataViewers sample package.

Figure 5.26. The Progress or Execution Results view shows important information

The Package Explorer View

The Package Explorer view is another way to view your package. It shows the entire package, both Control Flow and Data Flow, in one unified view. This is useful for getting to objects fast and, in some cases, such as for log providers, it is the only place where certain settings can be edited. Figure 5.27 shows the Package Explorer view for the DataViewers sample package.

Figure 5.27. The Package Explorer view is a quick way to access the entire package

The Event Handlers Designer

The Event Handlers designer is the way you edit event handlers. Event handlers are package containers that Integration Services executes when an object in the package raises an event. The Event Handler designer is practically identical to the Control Flow designer. You can create tasks and variables in this designer. The only difference is that the control flow you create in these designers is only conditionally executed when the associated event is fired.

The Log Events Window

Figure 5.28 shows the Log Events window with output from the execution of the DataViewers sample package. This window shows the same data that is output to logs and can be configured in the same way as log providers are configured.

Figure 5.28. The Log Events window shows log output during execution

To see the log events in the designer with the Log Events window, click the main View menu, click Other Windows, and then click Log Events. This shows the Log Events viewer. If you run the package now, with the default settings, nothing shows in this window. That's because you still need to enable logging, which is disabled by default for performance reasons.

To enable logging events, right-click on the Control Flow designer, and then click Logging. Figure 5.29 shows the Configure SSIS Logs dialog box. Notice that the check boxes in the left pane are both selected. This turns on logging for those containers. The default for these is unchecked. On the right side of the dialog box are the events that are available from the container. In this example, all log events are selected, but you can be selective and only choose those log events in which you are interested.

Figure 5.29. Use the Configure SSIS Logs dialog box to turn on logging

Tip

If the Log Events window isn't visible while your package is executing, you'll need to open it as explained previously. The set of visible windows during design time is different than the set visible while executing a package.

The Configure SSIS Logs dialog box is also where you create and delete log providers. Click the Providers and Logs tab and you'll see the dialog box shown in Figure 5.30.

Figure 5.30. Use the Configure SSIS Logs dialog box to add and delete log providers

To create a new log provider, select the type of log provider you want to create and click the Add button. In Figure 5.30, a log provider is created that logs to SQL Server. The configuration, in this case, is a connection manager to a SQL Server database. The Configuration setting takes different connections depending on the type of log provider. For example, text file log providers require a file connection manager, whereas Windows Event Log log providers don't require any configuration because they log to the local Windows Event Log system.

Package Configurations

Package configurations are a way to change the settings in the package without modifying the package directly. All the sample packages that accompany this book use configurations to make it easy to load and run packages on your machine. To get to the Package Configurations dialog box shown in Figure 5.31, right-click on the Control Flow designer and select Package Configurations.

Figure 5.31. Edit package configurations with the Package Configurations dialog box

The Package Configurations dialog box is a wizard that walks you through the process of creating a configuration. To start the wizard, click the Add button.

At this point, this chapter has covered those features of the Business Intelligence Development Studio that are specific to and created for Integration Services. Most of these features exist only in BIDS and are not found in the standard Visual Studio environment that ships with Visual C# or any other language.