Share via

  • Article

Using Data Sources in Packages

Business Intelligence Development Studio includes two design-time objects that you can use in Microsoft Integration Services packages: the data source 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. It can also include additional metadata such a description, a name, a user name, and a password. For more information, see Data Source (SSIS).

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

  • A data source has project scope, which means that a data source created in an Integration Services project is available to all the packages in the project. A data source can be defined one time and then referenced by connection managers in multiple packages.

  • A data source offers synchronization between the data source object and its package references. If the data source and the packages that reference it reside in the same project, the connection string property of the data source references is automatically updated when the data source changes.

There is no dependency between a data source and the connection managers that reference it. If a data source is no longer part of the project, the packages continue to be valid, because information about the data source, such as its connection type and connection string, is included in the package definition.

You use a data source object in a package by adding a connection manager that references the data source object to the package. You can add it to the package before you build the package control flow and data flows, or as a step in constructing the control flow or data flow. For information about how to use a data source object in a package, see How to: Use a Data Source in a Package.

A data source object represents a simple connection to a data source and provides access to the objects in the data store that it references. For example, a data source object that connects to the SQL Server AdventureWorks sample database includes all 60 tables from the database. When you want to use only some of the tables, views, and data in a database, you should consider creating a data source view on the data source and then using the data source view instead of the data source. For more information, see Using Data Source Views in Packages.

Working with Data Sources

For information about how to work with data source objects, 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.