SQL Server Integration Services (SSIS) overview

SQL Server 2005 Integration Services (SSIS) introduces the concepts of control flow and data flow in packages. Learn more about this SQL Server 2005 tool in this excerpt from "Inside SQL Server 2005 Tools."

Before we begin the discussion of the Integration Services tools, you should be familiar with some Integration Services concepts to help you create Integration Services packages successfully from the very beginning. This is not a comprehensive overview of the Integration Services architecture and object models; we discuss only concepts that are relevant to the use of the Integration Services tools. Those concepts include the following:

  • Packages
  • Control Flow
  • Data Flow
  • Connection Managers
  • Package Configurations
  • Property Expressions
  • Data Sources and Data Source Views

A package presents a unit of work that addresses a business requirement. The package is the Integration Services object that you save, manage, or run. In SQL Server 2005 Integration Services introduces the concepts of control flow and data flow in packages. A control flow consists of the tasks and containers. The tasks perform specific types of work such as executing SQL statements or sending email messages, and the containers define repeating subsets of the control flow or group subsets of the control flow to make the package easier to manage. The tasks and containers are usually connected by precedence constraints that specify the sequence in which tasks and containers are executed and the conditions that must be satisfied to run the next task or container in the control flow. A data flow consists of sources that extract data, transformations that modify data, and the destinations that load the data into data stores.

To connect to the data stores, a package uses connection managers. The connection managers are defined when you create the package. From the definition, the Integration Services runtime creates a connection at run time. When you construct a package, you configure properties of the connection managers, control flow and data flow items in the package, as well as the package itself. Frequently, a package must be configured differently for each environment to which you deploy it. For example, the connection string of connection managers may require updating to specify a different server, the location, the location of the data sources it accesses may change, and so forth. Integration Services provides package configurations to support this common scenario. Package configurations make it possible to dynamically update properties at run time. A configuration is a name/value pair that maps a property and a value. The configurations are stored outside the package in XML files, Database Engine tables, variables, or Registry entries. When the package is run, the value from the configuration replaces the value of the mapped-to property within the package. The values of the properties are not changed permanently.

You can set property values of packages and package objects in two different ways: directly by setting the value of each property, or indirectly by using property expressions. An expression, mapped to a property, is called a property expression. You build property expressions by using the operators and functions that the Integration Services expression language provides and variables. When the package is validated, which occurs when you save the package, the evaluation results of the property expressions replace the original values of properties.

A data source is a connection reference that you create and save outside a package, and then use as a source when adding new connection managers to a package. A data source represents a simple connection to a data store and therefore makes all tables and views in the data store available to the package. A data source view is built on a data source. It can contain only selected database objects and it can be extended with calculated columns that are populated by custom expressions, new relationships between tables, and queries. You can also apply a filter to a data source view to specify a subset of the data selected. In Integration Services, data sources and data source views are saved within the package definitions of the packages in which they are used.

Use the following table of contents to navigate to chapter excerpts, or click here to view Inside Integration Service Tools in its entirety.




Inside SQL Server Integration Services Tools

  Home: Introduction
 Part 1: Integration Services overview
 Part 2: Integration Services tools overview 
 Part 3: Using Integration Services tools in business scenarios 
 Part 4: Common package development scenarios 
 Part 5: Common package deployment scenarios
 Part 6: Common package management scenarios 

About the book   
Microsoft SQL Server 2005's high-powered management tools can dramatically improve DBA productivity and effectiveness. Now there's a comprehensive guide to SQL Server 2005's toolset, straight from the Microsoft team that created it. This book covers the entire toolset in unprecedented depth, guides database professionals in choosing the right tools, and shows them how to use various tools collectively to solve real-world problems. Purchase Inside SQL Server 2005 Tools from Addison-Wesley.
About the author   
Lead author Michael Raheem is a senior product manager in the SQL Server Marketing team at Microsoft. Michael leads SQL Server enterprise marketing efforts, including high availability, scalability, performance, and SQL Server Always On Technologies. Prior to joining the marketing team, he led the design and implementation of several SQL Server 2005 tools such as Management Studio, Upgrade Advisor, Database Mail and Surface Area Configuration. Michael has spoken at several conferences, including TechEd, TechReady, PASS and SQL Connections. Additionally, he has contributed to the Answers from Microsoft column in SQL Server Magazine and has over 13 years of experience in designing and developing solutions with Microsoft SQL Server.


Dig Deeper on Database software management