Using Data Sources in Packages

  • Article

Business Intelligence Development Studio includes two design-time objects that you can use in Microsoft SQL Server 2005 Integration Services (SSIS) 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: Add a Data Source Reference to 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:

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 information about how to use a data source in a package.