Share via

  • Article

Integration Services in Business Intelligence Development Studio

Business Intelligence Development Studio is the environment that you will use to develop packages for data extraction, transformation, and loading (ETL) in Integration Services. Business Intelligence Development Studio is the Visual Studio 2008 environment with enhancements that are specific to business intelligence solutions. After you create a new Integration Services project by using the New Project dialog box, Business Intelligence Development Studio opens, ready for you to start designing your package in the SSIS Designer. The windows that you see include the familiar Toolbox on the left, the design surface in the middle, and the new Solution Explorer on the right. For more information about the general features of Business Intelligence Development Studio, see Introducing Business Intelligence Development Studio.

Note

BI Development Studio, the 32-bit development environment for Integration Services packages, is not designed to run on the Itanium 64-bit architecture and is not installed on Itanium servers.

The package development experience for Integration Services packages is much richer in Business Intelligence Development Studio than it was in the single-window designer available in Enterprise Manager that you may have used as a package developer in SQL Server 2000 Data Transformation Services (DTS). The DTS Designer displayed menus, a toolbox of connections and tasks, and a single package design surface. Although the SSIS Designer still has each of these elements, the design surface now includes multiple tabbed windows, and other new features include the Solution Explorer and the Variables and Log Events windows. The new development environment and new features combine to offer Integration Services developers a comprehensive and powerful environment for creating business intelligence solutions.

This topic briefly introduces the Business Intelligence Development Studio features that are unique to Integration Services projects. For more information about the Visual Studio development environment in general, see Introducing Business Intelligence Development Studio. For a walkthrough of how to develop an Integration Services package, see Tutorial: Creating a Simple ETL Package.

Important

Many new and useful actions are available from the shortcut menus that you see when you right-click objects in the user interface. As you explore Business Intelligence Development Studio and the Integration Services features discussed in this topic, make sure that you right-click various items to learn about these options.

This topic discusses the following features of Business Intelligence Development Studio:

  • Solution Explorer

  • SSIS Designer

  • SSIS Menu

  • SSIS Designer Options on the Tools Menu

  • Integration Services Project Properties

  • Other Integration Services Windows and Features

Solution Explorer

Of the three prominent windows displayed when you open a new Integration Services project, only the Solution Explorer is completely new, and had no equivalent in the DTS Designer. By default, a new Integration Services project contains a single package file, named package.dtsx, but you can also add items of other types, which are grouped in the following Solution Explorer folders.

The shortcut menus available on these folders generally let you add a new item to the folder, whereas the shortcut menus available on individual items in the folder let you open the item, copy, delete, or rename the item, or exclude the item from the project. The View Code shortcut menu option available on most folder items displays the XML definition of the selected item.

  • Data Sources folder. A data source represents a connection that can be shared among multiple packages in a project. You can create a connection manager in a package from an existing data source. For more information, see Using Data Sources in Packages.

  • Data Source Views folder. A data source view represents a subset of the data in a data source, and can also contain named queries. Data source views can be shared by multiple packages in a project. You can designate tables, views, or named queries from a data source view as the source of data for a data flow source. For more information, see Using Data Source Views in Packages.

  • SSIS Packages folder. A package represents an organized collection of connections, control flow elements, data flow elements, and other objects. The package is the unit of work in Integration Services that provides and supports extraction, transformation, and loading (ETL) functionality. A single project can contain multiple packages. The shortcut menu available on the SSIS Packages folder lets you do the following:

    • Add a new or existing package to the project.

    • Run the SQL Server Import and Export Wizard to jumpstart creation of a new package.

    • Run the Package Migration Wizard to migrate existing DTS packages.

    • Run the SSIS Package Upgrade Wizard to upgrade SQL Server 2005 Integration Services (SSIS) packages.

    In the SSIS Packages folder, packages have a shortcut menu. This shortcut menu lets you designate a package as the startup object in the project, run the package, and perform other maintenance tasks.

    For an introduction to packages, see Integration Services Packages.

  • Miscellaneous folder. If you add files of other types to your Integration Services project, such as documents or images, these files are grouped in the Miscellaneous folder.

By default, when you create a new project, Business Intelligence Development Studio also creates a new solution as a container for the project. You can add more projects to the same solution, such as an Analysis Services project or a Reporting Services project, or a C# or Visual Basic .NET Class Library project in which you can develop custom components to use in your packages. Unlike Analysis Services and Reporting Services, Integration Services provides only a single project type in Business Intelligence Development Studio—the Integration Services project. For more information about Integration Services projects, see Integration Services Projects.

SSIS Designer

SSIS Designer is a graphical tool for creating packages that includes separate tabbed design surfaces for building the control flow, data flow, and event handlers in packages.

  • Control Flow tab. On the Control Flow tab, you arrange and configure the tasks, including the Data Flow task, that provide functionality in packages, the containers that provide structure in packages and services to tasks, and the precedence constraints that connect containers and tasks into a control flow. The shortcut menu available on the Control Flow design surface lets you add text annotations, set breakpoints for debugging, and zoom out or zoom in on the layout of the package. The shortcut menu available on individual tasks lets you execute the task by itself, without running the whole package. For more information, see Creating Package Control Flow and Control Flow Tab.

  • Data Flow tab. On the Data Flow tab, you combine into a data flow sources that extract data, transformations that modify and aggregate data, destinations that load data, and paths that connect the outputs and inputs of data flow components. The shortcut menu available on the Data Flow design surface also lets you add text annotations. The shortcut menu available on the paths that join data flow components lets you configure Data Viewers to watch data as it passes through the data flow. For more information, see Creating Package Data Flow and Data Flow Tab.

  • Event Handlers tab. On the Event Handlers tab, you configure workflows to respond to package events. For example, you can create an event handler that sends an e-mail message when a task fails. For more information, see Creating Package Event Handlers and Event Handlers Tab.

  • Package Explorer tab. The Package Explorer tab provides a convenient explorer view of the package, with the package as a container at the top of the hierarchy, and underneath it, the connections, executables, event handlers, log providers, precedence constraints, and variables that you have configured in the package. For more information, see Viewing Package Objects and Package Explorer Tab.

  • Progress tab. The Progress tab displays information about package execution when you run a package in Business Intelligence Development Studio. For more information, see Progress Tab.

  • Connection Managers area. Integration Services uses connection managers to encapsulate connections to a data source. These connection managers are shared within the package by control flow components, data flow components, and log providers, and are displayed in a special area of the designer at the bottom of the Control Flow, Data Flow, and Event Handlers tabs. For more information, see Creating Connection Managers and Connection Managers Area.

The designer also provides access to the dialog boxes, windows, and wizards that you use to add functionality and advanced features to packages and to troubleshoot packages. For more information, see Designing Packages in Business Intelligence Development Studio.

SSIS Menu

When an Integration Services project is active in Business Intelligence Development Studio, an SSIS menu is added to the main menu bar. When the SSIS Designer has the focus, this menu contains the special Integration Services options listed here; when the focus moves to another part of the Business Intelligence Development Studio user interface, the SSIS menu displays only the Work Offline option.

SSIS Designer Options on the Tools Menu

Under the Business Intelligence Designers node in the Options dialog box available on the Tools menu, you will find three pages of preferences for properties that are unique to the Integration Services designers.

  • General page. On the general page, you can specify options for checking or requiring digital signatures and for displaying labels on precedence constraints. For more information, see General Page.

  • Control Flow Auto Connect and Data Flow Auto Connect pages. The two Auto Connect pages let you specify the default behavior for connecting objects that you put on the design surfaces. For more information, see Control Flow Auto Connect Page and Data Flow Auto Connect Page.

  • Confirm Delete page. On this page, you can specify whether a confirmation message appears when components are deleted.

Integration Services Project Properties

The Project Properties dialog box, which is available from the shortcut menu on the project node in Solution Explorer, contains three pages of default design-time property values for an Integration Services project.

  • Build page. On the Build page, you can specify the output path for files generated by the build process.

  • Deployment Utility page. On the Deployment Utility page, you can specify whether to create a deployment utility for the package, where to save the deployment utility, and whether to allow updates to configurations during deployment. For more information, see Creating a Deployment Utility.

  • Debugging page. On the Debugging page, you can specify several options that apply when you test the package by running it in Business Intelligence Development Studio at design time. For example, you can specify the following options:

    • RunInOptimizedMode (default False). Optimize the data flow execution plan. This debugging option temporarily overrides the value of the property of the same name on the Data Flow task.

    • InteractiveMode (default True). Allow modal dialog boxes during debugging.

    • Run64BitRuntime (default True). Use the 64-bit runtime if it is available. The value of this property is ignored on 32-bit computers. This project property applies only at design time.

    • StartAction, StartApplication, and StartObjectID. Specify what to do when debugging starts. By default, StartAction = ExecutePackage, StartApplication is blank, and StartObjectID = <Active Package>.

    • CmdLineArguments. Run the package with the specified command-line arguments. For information about command-line arguments, see dtexec Utility.

For more information about debugging, see Debugging Packages.

Other Integration Services Windows and Features

Integration Services objects and tools also appear in the following Business Intelligence Development Studio windows:

  • Toolbox. Integration Services populates the Toolbox with a rich variety of ready-to-use control flow tasks and data flow sources, transformations, and destinations. The shortcut menu available on the Toolbox lets you sort the items listed there, add or remove items, or reset the list. You can optionally display the Toolbox by using the predefined Ctrl+Alt+X key combination.

    • Control flow items. When you are working on the Control Flow tab or the Event Handlers tab, the Toolbox displays two groups of items: Control Flow Items and Maintenance Plan Tasks.

    • Data flow items. When you are working on the Data Flow tab, the Toolbox displays three groups of items: Data Flow Sources, Data Flow Transformations, and Data Flow Destinations.

  • Variables window. The Variables window is specific to Integration Services projects. By default, the Variable window appears near the Toolbox, but it may not be visible until the first time that you select Variables on the SSIS menu to display the Variables window. Variables let objects in a package communicate with each other, and can be used in expressions and in scripts. You can optionally display the Variables window by mapping the View.Variables command to a keyboard shortcut of your choice on the Keyboard page of the Options dialog box. For more information, see Using Variables in Packages and Variables Window.

  • Log Events window. The Log Events window is specific to Integration Services projects. By default, the Log Events window appears near the Toolbox, but it may not be visible until the first time that you select Log Events on the SSIS menu to display the Log Events window. Logging lets you keep a record of a package's run-time events. You can view the logged events easily at design time in the Log Events window. You can optionally display the Log Events window by mapping the View.LogEvents command to a keyboard shortcut of your choice on the Keyboard page of the Options dialog box. For more information, see Implementing Logging in Packages.

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.