Design-Time Data Source Objects

Projects in Business Intelligence Development Studio include two design-time objects that make it easier to implement connections in packages: data sources and data source views.

You build data sources and data source views separately from packages, and then you use them in packages. You can build data sources and data source views in Integration Services, Analysis Services, and Reporting Services projects.

In SQL Server Integration Services, data sources and data source views are not deployable objects; instead, SSIS creates a connection manager from the properties of the object and adds the connection manager to the package. You can use the same data source or data source view in multiple packages to provide a consistent approach across multiple packages. For example, if multiple packages or multiple elements in a package —tasks, sources, transformations, or destinations—access the Customer table in the AdventureWorks2008R2, you can create a data source that provides the connection to AdventureWorks2008R2, create a data source view that includes only the Customer table, and then use the data source view in all the packages and tasks.

Integration Services provides synchronization between a data source and the connection managers that reference the data source, so that managing the connection is easier. For example, when the connection string in a data source changes the connection string in each connection managers that references the data source, are updated also.

Data sources and data source views cache their metadata. Caching lets you edit and extend data sources and data source views without connecting to the data stores on which they are built. The metadata includes information about tables, column attributes, and foreign key relationships between tables. For example, you can open a data source view in Data Source View Designer and create new relationships between tables.

When you create a package programmatically, you do not use data sources or data source views; instead, you create a connection manager and set its properties.