Kirk Haselden

Data Sources, Data Source Views and Deployment

In an earlier blog I covered some aspects of configurations that I think help make them amenable to deployment and also make them easier to manage for multiple packages and for multiple machines. That blog entry is here.

Another way to make packages easier to move is to use data sources and data source views. For those not familiar with data sources, they are a way to specify a connection that can be shared between packages, machines and even project types, RS, IS and AS etc. If you create connection managers in a package they are specific to the package and cannot be shared. If you modify the connection manager, it will not be reflected in the connection managers found elsewhere in other packages that point to the same server with the same connection string etc. Data sources on the other hand may be shared.

This gives several distinct advantages that should be considered by anyone wishing to make their packages more portable:

1) Modifying one file, one data source, modifies all connections that use that data source. With connection managers, if you don't configure them, you must modify each and every connection manager every time something about the connection string changes. In this way, data sources are very similar to configurations. They're a central location that can configure or modify package behavior without modifying the package directly.

2) Data source views use data sources. Data source views provide a way to cache and reduce the complexity of the metadata for the objects found at the data source. For example, this makes it simpler to focus on the tables that are important for a given solution by eliminating those that aren't necessary or related to the solution. For example, when you open the editor for an OLEDB connection manager created from a data source view, only those tables in the data source view will show up in the tables drop down. Data source views may be updated to reflect changes in the data source and speed up offline use in some cases by providing metadata to work against when the server isn't available.

3) Data sources are machine resident, much like the practice I recommend for configurations. One deploys packages into an environment described by configurations, data sources and data source views.

4) Data sources are the common link between different project types. If you're using the same server for AS and IS, it's possible that you may also only need one data source to represent that connection.

5) Multiple data source views may be created per data source. This allows for even more flexibility by providing different views into one server, you can essentially isolate the parts of a database or databases of interest and only see problem or package specific tables.

Ultimately, data sources provide a level of indirection similar to configurations. Where there is a level of indirection, there is an opportunity for abstracting away the environment which makes it easier to move packages around.

Take a look at data sources anytime you need to create a package that references an OLEDB connection. Chances are, the more you use them, the more you'll like them.

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden