Integration Services Tools

Reflecting its totally new architecture, the Integration Services tool set has been completely revamped and sports an entirely new look. While there are some similarities in some of the simpler tools—the Integration Services Import/Export Wizard, for instance, which is used to perform ad hoc data transfer operations—the more sophisticated tools such as the Integration Services Designer are completely different. Whether you’re a DTS expert or a novice DTS user, SQL Server 2005’s Integration Services will require you to learn how to use the new tools from the ground up. In this section you’ll gain an overview of the new Integration Services tools and utilities that come with SQL Server 2005.

SQL Server 2005’s Integration Services toolset is divided into two basic areas: tools that are used to create and execute Integration Services packages, and tools that are used to work with existing DTS packages. The Integration Services Designer tools include the basic Integration Services Import/Export Wizard and the more advanced Designer. As you’ll see next, the Integration Services Designer reflects the architectural separation of control flow and data flow by providing separate editors for designing package data flow and control flow. The Integration Services Designer provides a graphical representation of the work flow that occurs inside a Integration Services package. The new Integration Services Designer provides enterprise-level features such as source control to facilitate multideveloper environments. There are also built-in package deployment and debugging tools, including the ability to set and monitor breakpoints within a package. You can also use the Integration Services Designer to monitor a package’s execution—its overall progress and the outcome of individual tasks and transformations. The Integration Services package tools include a Package Migration Wizard that is designed to migrate SQL Server 7 and SQL Server 2000 packages to the new SQL Server 2005 format. There is also a Integration Services Package Installer Wizard that creates installation programs for Integration Services packages, making it easier to deploy these packages to remote systems. In addition, there is a Integration Services Package Execution utility that enables you to execute Integration Services packages from the command line; this is a handy tool for incorporating Integration Services transfer as part of your management scripts.

Integration Services Design Tools

Here you get a closer look at the new Integration Services design tools. First, you’ll see the Integration Services Import/Export Wizard. Next, you’ll get a more detailed look at the new Integration Services Designer.

Integration Services Import/Export Wizard

The Integration Services Import/Export Wizard is SQL Server 2005’s first entry point into the new Integration Services. Like its predecessors found in SQL Server 7 and SQL Server 2000, the SQL Server 2005 Integration Services Import/Export Wizard provides a series of dialogs that lead you through the process of selecting the data source, the destination, and the objects that will be transferred. The Integration Services Import/Export Wizard also allows you to optionally save and execute the Integration Services package. You can start the Integration Services Import/Export Wizard by select the Integration Services Import/Export option from the SQL Server menu or by entering dtswizard at the command line. Saving the packages generated with the Integration Services Import/Export Wizard and then editing them in the Integration Services Designer is a great way to learn more about Integration Services— especially if you’re just getting started with Integration Services or if you’re transitioning to the new SQL Server 2005 Integration Services from one of the earlier versions. You can see the new Integration Services Import/Export Wizard in Figure 9-5.

Figure 9-5: Integration Services Import/Export Wizard—Data Source Selection

The Integration Services Import/Export Wizard first leads you through the process of choosing a data source. In the Data Source drop-down, you select the provider that you want to use. Depending on the provider that you select, the options for the rest of the screen change. If you select the Microsoft OLE DB Provider for SQL Server, you will see a screen like the one shown in Figure 9-5, where you then select the server that you want to connect to followed by the database and the type of authentication that you need to use. Clicking Next leads you through the subsequent wizard dialogs. The next dialog allows you to select the data destination, which is essentially identical to the data source dialog except that it defines where the data will be transferred to.

Note 

While the SQL Server 2005 Integration Services Wizard does provide the same basic functionality as the Integration Services Import/Export Wizard that was included in SQL Server 7 and 2000, the new version lacks the ability to perform custom data mappings as well as the ability to input custom data transformation scripts. In order to use these more advanced capabilities, you need to use the Integration Services Designer.

After you select the data source and destination, the wizard prompts you to optionally save and execute the Integration Services package. As each task in the package executes, the transfer window is dynamically updated, showing the Integration Services package’s transfer progress. When the Integration Services package has been successfully executed, the Integration Services Import/Export Wizard will display the dialog that you can see in Figure 9-6.

Figure 9-6: Integration Services Import/Export Wizard—Package Execution

Integration Services Designer

While the Integration Services Import/Export Wizard is useful for simple ad hoc transfers, extraction, transformation, and loading (ETL) tasks require significantly more sophistication and power than the Integration Services wizard exposes. By their nature, ETL tasks are far more than just simple data transfers from one destination to another. Instead, they often combine data from multiple sources, manipulate the data, map values to new columns, create columns from calculated values, and provide a variety of data cleanup and verification tasks. These more demanding capabilities are outside the scope of the simple Integration Services Import/Export Wizard. That’s where the new Integration Services Designer comes into play. The Integration Services Designer is a set of graphical tools that you can use to build, execute, and debug Integration Services packages. You can use the Integration Services Designer to open up simple Integration Services packages that were created using the wizard, or you can use it to create very powerful and robust data transfer and transformation solutions.

The Integration Services Designer is started from the Business Intelligence Development Studio by selecting File | New | Project to open the New Project dialog. Then to create a new Integration Services project, you select Business Intelligence Projects from the Project Types list and then Data Transformation Project from the list of Templates, as is shown in Figure 9-7.

Figure 9-7: Opening a data transformation project

Note 

Don’t be confused by the fact that the Integration Services Designer is started from the Business Intelligence Development Studio. It is not limited to just Analysis Services projects. The Integration Services Designer and the projects developed in the Business Intelligence Development Studio are fully capable of working with relational and other types of data and are not limited to analysis service data.

Once you’ve created the project, you can open the Integration Services Designer by right-clicking Packages in the Solution Explorer window that’s displayed on the right portion of the screen. Then select the New Package option to start the Integration Services Designer. When the Integration Services Designer first starts, you’re presented with a blank design surface that looks nothing like the earlier Integration Services Designers, so getting started can be a bit of a challenge.

New Control Flow Designer

While you can approach using the Integration Services Designer in a couple of different ways, the easiest method is probably to begin with the Control Flow tab and then open the Control Flow Toolbox. After the Toolbox is displayed, click the Data Flow task and then drag it to the design surface. The Integration Services Designer’s Control Flow surface will appear as shown in Figure 9-8.

Figure 9-8: Control Flow Designer

At this point the Integration Services package is pretty simple, as it consists of a single Data Flow task. Obviously you could make this package much more complex by adding additional tasks from the Control Flow toolbox and even organizing multiple related tasks into containers.

New Data Flow Designer

At this point the package knows that it is going to perform a data transfer operation, but it doesn’t know what it’s going to transfer or where the data is coming from or going to. After adding the Data Flow task, the next step is to define the actual data flows. To define the data flows, you double-click the Data Flow task to display the Data Flow tab. Initially, the Data Flow tab will be blank. To add data source and data destination connections, open the Data Flow Toolbox on the left side of the IDE and then drag and drop an OLE DB Source data flow item and an OLE DB Destination data flow item onto the Data Flow Design surface. In addition, you can add a Flat File Destination that can be used to output any error rows to an ASCII log file.

Now you’ve told Integration Services that you’re transferring data from one OLE DB source to another OLE DB destination, but you still need to define the source and destination connections. In addition, you need to set up a connection for the flat file error flow. Basically, every data flow source or destination needs an accompanying connection, which essentially provides the specific details about the connection’s endpoint. Because this example uses three Data Flow Items, three different connections are needed: two OLE DB connections and one Flat File connection. To define those connections, you can right-click in the Connection pane shown at the bottom of the Integration Services Designer, which displays a pop-up menu enabling you to select the type of connection that you want to create. Selecting the connection type from the pop-up menu displays the Connection Manager. In Figure 9-9 you can see the Connection Manager being used to create a new OLE DB connection.

Figure 9-9: Connection Manager

Once the connections are created, you need to associate each connection to the appropriate data flow item. To link a connection to a data flow item, you right-click the data flow item, select either the Edit or the Advanced Edit option from the context menu, and then select the connection from the drop-down list of connections displayed in the Edit dialog. As you define each connection, you specify the column mapping and any other transformations that will be performed. When you complete the configuration, the Integration Services Designer’s Data Flow tab will appear something like the one shown in Figure 9-10.

Figure 9-10: Data Flow Designer

In the middle of the Data Flow Design tab you can see the OLE DB Source, OLE DB Destination, and Flat File Destination data flow items. A straight green arrow data flow connects the OLE DB Source to the OLE DB Destination, and a bent red arrow error flow connects the OLE DB Source to the Flat File Destination. The connections for each of these items are shown in the Connections pane at the bottom of the display.

Graphical Presentation of Package Execution

Once all of the data flows and connections have been defined, the package can be run by clicking the green run arrow on the toolbar or by selecting the Start option from the Debug menu. Before running the package, you can optionally define breakpoints in the package by selecting an item followed by the New Breakpoint option from the Debug menu. When the package is run, the Watch and Call Stack windows are automatically opened and the currently executing Data Flow Item turns green. You can see the result of running the sample package in Figure 9-11.

Figure 9-11: Running the package inside the Designer

New Package Explorer

Another new feature that is found on the Integration Services Designer is the Package Explorer. The Package Explorer provides a hierarchical tree view of the Integration Services Package that’s displayed in the designer. The Package Explorer is illustrated in Figure 9-12.

Figure 9-12: The Package Explorer

At the top level you can see the package name, and each of the package’s components is listed beneath the package. Clicking the plus sign in front of each component displays the actual items. It’s worth noting that the Package Explorer is not just a view-only tool. From the Explorer view, you can delete items as well as edit their properties. However, you can’t add new items. That can be done only in the design surface.

Configuration

The new Integration Services configurations feature was designed to make it easier for you to deploy Integration Services packages. The new configurations feature enables you to dynamically update a package to run in a different environment. For instance, connections require connection strings, and these connection strings are often suitable only to a given environment. Configurations enable you to dynamically update these types of site/server-specific values when the package is deployed. You can create configurations using the Configuration Wizard. You can start the Configuration Wizard using the Integration Services | Configurations menu option to display the Package Configurations Organization dialog, where you can click Add to start the Configuration Wizard. You can see the Configuration Wizard in Figure 9-13.

Figure 9-13: Configuration Wizard

Although this section was able to touch on only a fraction of the capability found in the SQL Server 2005’s new Integration Services, hopefully this section has given you a feel for how the Integration Services Designer works.