Using Data Source Views in Packages

Business Intelligence Development Studio includes two design-time objects that facilitate the use of connections in Microsoft SQL Server 2005 Integration Services (SSIS) 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) 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 AdventureWorks 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:

• OLE DB Source
• Lookup Transformation
• Fuzzy Lookup Transformation
• Term Lookup Transformation
• OLE DB Destination
• SQL Server Destination

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:

• How to: Define a Data Source View Using the Data Source View Wizard
• How to: Refresh a Data Source View Using Data Source View Designer
• How to: Browse Data Using Data Source View Designer
• How to: Delete a Data Source View in Solution Explorer
• How to: Use Data Source Views in Packages

Modifying Data Source Views

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

• How to: Add, View, Modify, or Delete a Named Query Using Data Source View Designer
• How to: Add or Remove Tables or Views in a Data Source View Using Data Source View Designer
• How to: Add or Remove Tables or Views in a Data Source View Using Data Source View Designer
• How to: Add, View, Modify, or Delete a Named Query Using Data Source View Designer
• How to: Set or Modify a Logical Primary Key Using Data Source View Designer
• How to: Add, Delete, View, or Modify a Logical Relationship Using Data Source View Designer
• How to: Add, View, Modify, or Delete a Named Calculation to a Table Using Data Source View Designer
• How to: View or Change the Properties of a Data Source View Using Data Source View Designer
• How to: Define, Rename, Modify, or Delete a Diagram using Data Source View Designer
• How to: View Objects in a Data Source View Using Data Source View Designer

See Also

Other Resources

Creating Packages in SSIS Designer

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
14 April 2006	New content: Added link to new topic about using data source views in packages. Added information about data flow components in which you can use data source views.