LearnNowOnline

Blog

SQL Server 2008: The BIDS Interface and Components

Excerpt by Don Kiely | February 13, 2014

SQL Server Integration Services is not the only member of the Microsoft Business Intelligence suite to use BIDS for development. Analysis Services and Reporting Services developers also use BIDS to create their work. Using project templates supplied by Microsoft, Analysis Services developers build cubes, data mining models, and other analytical objects, while report developers build reports and report models in BIDS. BIDS supports any or all of these project types within the same solution, as shown in Figure A.

Figure A. The BIDS Solution Explorer window showing multiple SQL Business Intelligence project types.

TIP: You can create as many Integration Services packages as you want in a single solution. Modularizing a complex ETL workflow into multiple packages makes it easier to develop and debug the individual components. When all the packages are working correctly, you can group them to execute all at once using an Execute Package task.

Creating a new Integration Services project-rather than just opening a package as a standalone file-exposes some additional sections of the interface, such as access to the Data Sources and Data Source Views folders in Solution Explorer, as you can see in the Integration Services Project in Figure A. Items that you add to a Project are visible in the Solution Explorer window, and can be accessible to any part of the project.

• Data Sources contain a connection string, and are available in Solution Explorer in the Data Sources folder. They are created and maintained at the project level, outside of any package definition, but can be referenced by a package's Connection Manager. Connection Managers depend on Data Sources in Integration Services and can be used to share connection definitions among multiple packages in the same solution.

TIP: When you use Data Sources, you can change the data connection strings for multiple packages at once. Adding Connection Managers directly to a package creates local data links, which you must edit per package.

• Data Source Views are based on Data Sources, but support further filtering of the database schema. By creating package objects based on Data Source Views, you streamline working with lists of database objects.

• The Miscellaneous folder can contain any support files you need, such as documentation, flat files that contain data for import, etc.

You access Integration Services functions in BIDS or Visual Studio through the SSIS menu, the tabbed designer windows, and their related toolboxes:

• SSIS Menu: Choose options for setting up package logging, configurations, variables, and other options, as shown in Figure B.

Figure 2. The SSIS menu.

• Tabbed Designer: Lay out the logic of the package by dragging tasks from the toolbox to the designer to control the overall flow of processing steps. For example, you could download files via FTP before importing them into a database table.
• Toolboxes: The toolboxes associated with some of the designer tabs let you drag and drop components that perform the many potential actions of a complete Integration Services solution. If you are familiar with Web or Windows forms development in .NET, you'll be right at home with adding components to a package.

In this course, you will learn about the BIDS tools that enable you to build powerful ETL solutions in Integration Services. The major components of the BIDS interface include:

• Control Flow designer
• Data Flow designer
• Connection Managers
• Event Handler designer
• Package Explorer
• Progress pane

This post is an excerpt from the online courseware for our Microsoft SQL Server 2008 Integration Services course written by expert Don Kiely.