• Article

Using Data Source Views in Packages

Business Intelligence Development Studio includes two design-time objects that facilitate the use of connections in Integration Services packages: data sources and data source view objects.

A data source object is a reference to a connection, and at a minimum, it includes a connection string and a data source identifier. A data source view is based on a data source. For more information about creating a data source, see Using Data Sources in Packages.

A data source view is a named, saved subset of a relational schema. A data source view can include schema information such as tables, unique keys, and foreign key relationships and attributes such as named queries, calculated columns, relationships and logical primary keys. For information about, Data Source Views (Analysis Services - Multidimensional Data) and Data Source View (SSIS).

The advantages of using data source views in Integration Services packages include the following:

  • A data source view can be defined one time and then used by multiple data flow components.

  • A data source view can be refreshed to reflect changes in its underlying data sources.

  • A data source view caches the metadata from the data sources on which it is built.

  • A data source view can focus the lists of objects that appear in the user interface of data flow components.

You can build multiple data source views on a data source and customize each data source view to your needs. For example, by using a data source that references the AdventureWorks2008R2 database, you can build one data source view that includes only the Production.Product table, and a second data source view that includes only the HumanResources.Employee table.

After you add a connection manager that references a data source object to a package, any data flow component in the package can use the data source view to define sources and destinations. Transformations that use reference tables such as the Lookup transformation can also use data source views.

The following data flow components can use data source views:

There is no dependency between a data source view and the packages that reference it. When you use a data source view in a package, the view definition is added to the package definition in the pertinent data flow component property. For example, in a package that includes a Lookup transformation, the view—a SELECT SQL statement—is stored in the sqlCommand property of the Lookup transformation. Even if a data source view is no longer part of the project, the package continues to be valid because the SQL representation of the data source view is saved in the package definition.

Working with Data Source Views

For information about how to work with data source view objects, click one of the following topics:

Modifying Data Source Views

For information about how to modify data source views, click one of the following topics:

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.