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.
| 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 currently leads the 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.
|
Home: 
