Extracting and Loading Data in Microsoft SQL Server 2005 Integration Services

Using Data Sources and Data Source Views

A data source is a connection that represents a simple connection to a data store; it includes all tables and views in the data store. 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 and then referenced by connection managers in multiple packages. This makes it easy to update all connection managers that use that data source. A project can have multiple data sources, just as it can have multiple connection managers.

Although a data source includes all tables and views, a data source view selects specific database objects (such as tables and views) or adds new relationships between objects. You can extend a data source view by adding calculated columns that are populated by custom expressions, adding new relationships between tables, replacing tables in the data source view with queries, and adding related tables. You can also apply a filter to a data source view to specify a subset of the data selected.

The objective of the next exercise is to load data from a new table, Products, to a flat file. You will create the product’s table by defining a named query in a data source view. In addition, you will create a new data source, as source for the data source view, in the connection manager.

Creating a Data Source

In this step, you make your decision about how to define the connection string for your data source. You can create a new connection, a data source based on an existing connection, a data source based on another object, such as an existing data source in your solution, or an Analysis Services project.

In this procedure, you’ll create a data source based on a new connection.

Create a data source

1. In Solution Explorer, right-click the Data Sources folder, and then click New Data Source.

2. On the Welcome To The Data Source Wizard page, click Next.

3. On the Select How To Define the Connection page, verify that Create A Data Source Based On An Existing Or New Connection is selected, and then click New.

4. The connection manager dialog box appears with Native OLE DB\SQL Native Client selected in the Provider drop-down list.

5. Leave the Native OLE DB\SQL Native Client provider selected.

6. Type localhost in the Server Name box.

7. Select Use Windows Authentication.

8. Select is2005sbs as the database from the drop-down list. Your screen looks like this:

   

9. Click the Test Connection button and verify that it is successful. Then click OK twice.

   NOTE

   

   The New Connection localhost.is2005sbs should now appear in the Data Connections pane.

10. Click Next. The Completing The Wizard page will appear, and a default data source name is displayed in the Data Source Name box.

11. Click Finish. The new data source will appear in the Data Sources folder in Solution Explorer.

    

Creating a Data Source View

In this step, you select objects from the relational database to be included in the data source view. You can also include system objects or select one table and automatically add related tables to that one.

In this procedure, you’ll specify a data source and select tables to define a new data source view.

Create a data source view

1. In Solution Explorer, right-click the Data Source Views folder, and then click New Data Source View.

2. On the Welcome To The Data Source View Wizard page, click Next.

3. On the Select A Data Source page, in the Relational Data Sources list, click the existing data source Is2005sbs as the primary data source for the data source view. The properties of the selected data source appear in the Data Source Properties pane.

4. Click Next.

5. On the Select Tables And Views page, select:

   • dbo.Product.

   • dbo.ProductCategory.

   • dbo.ProductSubCategory.

6. Click the right arrow to include them in the Included Objects.

   

7. Click Next. Leave Is2005sbs as a name for this data source view. This is the default data source view name, which is the name of the data source for which you are creating the data source view. The Preview pane displays a tree view of the objects in your new data source view.

8. Click Finish. The new data source view will appear in the Data Source Views folder in Solution Explorer.

   

Creating a New Named Query

A named query is a table based on a SQL Expression. In this SQL Expression, you can specify columns and rows from more than one table even from different data sources. You can expand a relational schema by using named queries without modifying the original data source. You can split tables or join tables into a single data source views table.

Create a named query

1. In Solution Explorer, expand the Data Source Views folder, and then open the .dsv file in Data Source View Designer by doing one of the following:

   1. Double-click the .dsv file.

   2. Right-click the .dsv file and click Open.

   3. Select the .dsv file, and then, on the View menu, click Open.

2. In the Tables pane, right-click an open area, and then click New Named Query.

   

3. In the Create Named Query dialog box, do the following:

   1. In the Name text box, type Products.

   2. In the Data Source drop-down list, verify that Is2005sbs (primary) is selected.

   3. Type or copy the next query in the bottom pane. Replace the current statement.

   SELECT * FROM Product
   INNER JOIN ProductSubCategory ON
   Product.ProductSubCategoryID =
   ProductSubCategory.ProductSubCategoryID
   INNER JOIN ProductCategory       ON
   ProductSubCategory.ProductCategoryID = ProductCategory.ProductCategoryID

   

4. Under Query Definition, click the Run icon.

   

5. Click OK.

6. Click OK. A new table will appear in the design pane with the name Products.

   

Copying Data from a Named Query to a Flat File

Once you have created a new table by defining a named query, you are ready to use it in a data flow. Then, the next steps are to create a new data flow, create source and destination data adapters, and map the flow of data.

In this next procedure, you’ll create a new data flow task, create and configure an OLE DB Source adapter using the named query created in the previous step, and create and configure a destination flat file data adapter.

Copy data from a named query products table to a flat file

1. In Solution Explorer, right-click SSIS Packages, and then select New SSIS Package.

2. Right-click Package1.dtsx, select Rename, and name the new package Products.

3. Click Yes to also rename the package object.

4. In the designer, drag a Data Flow Task from the Control Flow Items group of the Toolbox to the Control Flow design area.

5. In the Properties pane, change the Name property to Data Flow Task – Copy Products.

6. In the designer, double-click in the Data Flow Task component to open the Data Flow design area.

7. In the designer, drag an OLE DB Source from the Data Flow Sources group of the Toolbox to the Data Flow design area.

8. In the Properties pane, change the Name property to OLE DB Source – Products.

   TIP

   

   Note the warning icon that appears in the OLE DB Source. You can hover your mouse over it to read the text of the warning.

9. In the Connection Managers pane, right-click an open area, and then click New Connection From Data Source.

   

10. In the Select Data Source dialog box, ensure that Is2005sbs is selected, and then click OK.

11. Note that a new connection manager icon appears in the Connection Managers pane.

    

12. Double-click the OLE DB Source – Products component. Select Connection Manager, and then expand Is2005sbs from the OLE DB Connection Manager drop-down list. Select Is2005sbs Data Source View from the tree and click OK.

    

13. Now, in the Data Access Mode drop-down list, select Named Query. The named query products will be displayed. Click the Preview button to check the data.

14. Click the Close button, and then click OK to finish.

    

Connect to a flat file destination

1. In the designer, drag a Flat File Destination from the Data Flow Destinations group of the Toolbox to the Data Flow design area.

2. In the Properties pane, change the Name property to Flat File Destination – Products.

   NOTE

   

   Note the warning icon that appears in the Flat File Destination. You can hover your mouse over it to read the text of the warning.

3. Link OLE DB Source – Products and Flat File Destination – Products by dragging the green arrow from OLE DB Source – Products to Flat File Destination – Products.

4. Double-click Flat File Destination – Products to open the Flat File Destination Editor.

5. Ensure that Connection Manager is selected. Click the New button in the Flat File Connection Manager to open the Flat File Format window. Select Delimited and click OK.

   

6. In the Connection Manager Name, change the Name property to Products.

7. In the Flat File Connection Manager Editor, click the Browse button and type Products in the File Name text box. Click Open.

   Be sure that the folder is C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap03\Data.

8. In the Flat File Connection Manager Editor, click OK.

   NOTE

   

   Note that the OK button is disabled in the Flat File Destination Editor. It is because mappings columns have not yet been set.

   

9. In the Flat File Destination Editor, click Mappings in the left pane.

   Verify that the columns are mapped correctly and click OK.

   

10. Now you are ready to execute your package (Products.dtsx). Your package should look like this:

    

Executing the Package

This package is a very simple one that includes only one data flow. You have configured an OLE DB source based in a named query created in a data source view. When this package is executing, the data flow reads a buffer of data from the data source view and loads the data defined to the Named Query Products to a Products.txt file.

To execute this package, you can go to the Debug menu and select the Start Debugging button, press the F5 key, or right-click the package and choose Execute Package.

When the Data Flow is complete, all the components in the Data Flow change color from yellow to green. It means that they have all completed successfully. The last view will look like this:

Stop Debugging

1. Click the Stop Debugging button on the Debug toolbar.

2. Using Windows Explorer, navigate to the C:\Documents and Settings\<username>\My Documents\Microsoft Press\IS2005SBS\Chap03\Data\folder.

3. Open the Products.txt file to confirm data appears in the file.

4. Save the solution.