Data Sources and Data Source Views

Data sources and Data Source Views are not connection manager types, but they are closely related to connection managers and often used in the same ways.

Introduction to Data Sources and Data Source Views

Data sources are a way to specify a connection that can be shared between packages, machines, and even project types. Data sources are similar to Data Source Names or DSN files. They are files with a .DS extension and contain the connection string to a server. Integration Services, Reporting Services, and Analysis Services all support data sources. One limitation of connection managers is that if you create one in a package, they are specific to that package only and cannot be shared with other packages or projects. Data sources, on the other hand, might be shared, giving several distinct advantages that should be considered by anyone wanting to make their packages more manageable.

• Data sources can be a common link between different project types. If you're using the same server for AS and IS, it's possible that you might also only need one data source to represent that connection. Fewer files means fewer locations to change as your system evolves.

• Modifying one file, the 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.

• Data sources are machine resident, and can be used as recommended in Chapter 14, "Configuring and Deploying Solutions." Data sources remain on a machine and packages deployed to the machine environment can automatically reference the machine resident data source.

Data Source Views (DSVs) use data sources. DSVs are objects that provide an abstraction layer between the underlying source data and the client application and provide a way to cache and reduce the complexity of the source data and metadata. For example, you can configure a DSV to only show tables that are important for a given solution by eliminating those that aren't necessary or related. So when you open the editor for an OLEDB Connection Manager created from a Data Source View, only those tables in the Data Source View show up in the Tables drop down. You can filter and generate data in DSVs. DSVs can 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. The following are some other ways you can use DSVs:

• Contain metadata from one or more underlying sources.

• Contain calculated columns that do not exist in the underlying data source.

• Contain relationships between objects that do not exist in the underlying data source.

• Contain queries that do not exist in the underlying data source.

• Can generate new metadata for building an underlying relational store.

• Can reference more than one data source, making it possible to present one unified view of multiple sources.

• Make it possible to easily alter underlying data store because client applications bind to the Data Source View instead of directly to the underlying data.

• Multiple Data Source Views can be created per data source so the DSV can provide different views into the same server; you can essentially isolate the parts of a database or databases of interest and only see specific tables of interest.

Data Source Views (DSV) can be hard to conceptualize, so let's take a look at one example of how you can use them. Suppose you want to create an application on your existing complex Online Transaction Processing (OLTP) system that requires some relationships and calculated columns. The OLTP database administrators are unwilling to make any changes to the production tables, not even to create a view. Also, you're only familiar with the tables that relate to your particular application and want to only view those tables in your IS projects.

The DSV can act as an abstraction layer. You can implement the changes, create a simplified "view" of the complex OLTP system by selecting only those tables you need to reference, and build the calculated columns and relationships between them in the DSV. The client application can reference the DSV and experience the benefits of the changes without needing to change the source system directly.

DSVs also allow you to efficiently explore, test, and experiment with data in an ad hoc, trial-and-error way. As part of preliminary system discovery, people often create test databases that are scaled down, smaller copies of production systems. Creating these test databases can be tedious and time consuming. You can quickly simulate such an environment by pointing the DSV at your system database, selecting the subset of tables with which you want to work, enter filtering queries to decrease the size of the data set, and generate new data. These are the kinds of benefits you can get from DSVs.

Setting Up Data Sources

To create a new data source, right-click the Data Sources node of your Integration Services project and select New Data Source. The Data Source Wizard appears. The first dialog box is the welcome screen. Click the Next button to see the Data Source Wizard shown in Figure 10.30.

Figure 10.30. Create a data source with the Data Source Wizard

The Data Source Wizard gives you an option to create a data source from an existing or new connection or based on another object. If you select the Create a Data Source Based on Another Object radio button, the wizard lets you select data sources in the current solution or based on an Analysis Services project in the current solution. Figure 10.31 shows the Data Source Wizard with those options.

Figure 10.31. Create a data source from an existing object

If you want to use an existing connection, you can click on one in the Data Connections list. For a new data source, click the New button and the Connection Manager dialog box opens where you can build the connection string. That's pretty much all there is to creating a data source. After you've created a new data source, you can double-click on it in the Connections window or in the Solution Explorer to edit it in the Data Source Designer, as shown in Figure 10.32.

Figure 10.32. Use the Data Source Designer to create and edit data sources

There is an option in the Data Source Designer to maintain a reference to another object in the solution. This is another way to create a data source from another data source or that references an Analysis Services project in the same solution.

Setting Up Data Source Views

To create a new Data Source View, right-click on the Data Source Views node of your Integration Services project and select New Data Source View. The Data Source View Wizard opens. The first dialog box is the welcome screen. Click the Next button to see the Data Source View Wizard shown in Figure 10.33.

Figure 10.33. Create a Data Source View with the Data Source View Wizard

The Data Source View Wizard gives you an option to create a Data Source View from existing relational data sources or to create a new data source. If you click the New Data Source button, the Create Data Source Wizard opens. To use an existing data source, select it in the pane on the left and click the Next button. The next dialog box in the wizard lets you select the tables to load into the DSV. Figure 10.34 shows the selections with the "dbo.dim" filter.

Figure 10.34. Select the tables to load into the DSV

Because databases can have many tables, the filter provides a way to narrow down the list, for example, only the dimension tables. Click the Greater Than button and the dialog box moves the tables that are selected into the Included Objects list. Click the Add Related Tables to add tables that have primary key/foreign key relationships with the tables you've selected. Click the Next button and the dialog box shows a summary view of the tables you've selected. Click the Finish button and the Data Source View Designer opens. Figure 10.35 shows the Data Source View Designer with the Data Source View menu open.

Figure 10.35. Data Source Views have their own designer

At this point, you should have a pretty good idea what DSVs are. You can explore the Data Source View main menu and the other features of Data Source Views for a better understanding, and so on. Also, Books Online covers Data Source Views in detail. Indeed, an entire book could be written on Data Source Views alone. Before leaving the subject, let's look at how to create a connection manager that uses a DSV in an Integration Services package.

If you've already created a DSV, create a new package with a Data Flow Task. Drop an OLE DB Source Adapter onto the data flow and open the editor. Figure 10.36 shows the available OLEDB Connection Manager selections.

Figure 10.36. OLE DB Source Adapters can use DSVs

There are two data sources named AdventureWorks and PLAYLAND. There is also an OLEDB Connection Manager named LocalHost.PLAYLAND available. Under the AdventureWorks Data Source, there are three DSVs available: AdventureWorksDSV, AWDW, and AdventureWorks DW. If you've created named queries in any of the DSVs, they will become available for selection in the Data Access mode. Figure 10.37 shows the OLE DB Source Editor after selecting the AdventureWorks DW DSV and then selected the named query named ProductCustomerProfile. The Named Query generates four columns: ProductKey, YearlyIncome, EnglishProductName, and EnglishOccupation that will be available to components that are downstream of the OLE DB Source Adapter in the data flow. With this setup, you can modify or rename columns, filter rows, and even create new columns without affecting the source system.