Professional SQL Server™ 2005 Integration Services

4.5. Data Flow Example

Now you can practice what you've learned in this chapter and pull together some of the transforms and connections to create a small ETL process. This process will pull transactional data from the AdventureWorks database and then massage the data by aggregating, sorting, and calculating new columns. This extract may be used by another vendor or an internal organization.

1. Create a new package and rename it AdventureWorksExtract.dtsx. Start by dragging a Data Flow task onto the control flow. Double-click on the task to go to the Data Flow tab.

2. In the Data Flow tab, drag an OLE DB Source onto the design pane. Right-click on the source and rename it TransactionHistory. Double-click on it to open the editor. The connection to the AdventureWorks database may already be in the Data Connections list on the left. If it is, select it, and click OK. If it's not there yet, click New to add a new connection to the AdventureWorks database on any server.

3. When you click OK, you'll be taken back to the OLE DB Source Editor. Ensure that the Data Access Mode option is set to "Table or View." Select the [Production].[TransactionHistoryArchive] table from the Name of the Table drop-down box as shown in Figure 4-32.

   Figure 4.32. Figure 4-32

   

4. Go to the Columns page (shown in Figure 4-33) and uncheck every column except for ProductID, Quantity, and ActualCost. Click OK to exit the editor. ...