1. Right-click anywhere on the Data Flow surface, click Execute Task, and then observe the task as it runs, noting how many rows are transferred.
2. On the Debug menu, click Stop Debugging. 3. Close Visual Studio.
MCT USE ONL
Y. STUDENT USE PR
OHIBITED
L4-20 Module 4: Creating an ETL Solution with SSIS
Exercise 3: Using Transformations in a Data Flow
X Task 1: Examine an existing data flow
1. View the contents of the D:\10777A\Labfiles\Lab04\Starter folder, and then double-click Ex3 to view the contents of the Ex3 folder.
2. Double-click the AdventureWorksETL.sln Visual Studio solution file to open it in Visual Studio. 3. In the Solution Explorer pane, double-click Extract Reseller Data.dtsx.
4. On the Data Flow tab, in the Data Flow Task drop-down list, click Extract Reseller Sales. Note that this data flow includes a data source, two transformations, and two destinations.
5. Double-click the Reseller Sales data source, in the OLE DB Source Editor dialog box, note the following details, and then click Cancel:
• On the Connection Manager page, the data source is configured to use an OLE DB connection manager named localhost.ResellerSales and extract data using a Transact-SQL command that queries in the SalesOrderHeader, SalesOrderDetail, and PaymentTypes tables. The query includes an ISNULL function to check for a payment type. If none is specified, the value “other” is used.
• On the Columns tab, the data source is configured to retrieve several columns including
ProductKey, OrderQuantity, and UnitPrice.
6. Double-click the Calculate Sales Amount transformation, in the Derived Column Transformation
Editor dialog box, note the following details, and then click Cancel:
• The transformation creates a derived column named SalesAmount. • The derived column is added as a new column to the data flow.
• The column value is calculated by multiplying the UnitPrice column value by the OrderQuantity column value.
7. Double-click the Lookup Product Details transformation, in the Lookup Transformation Editor dialog box, note the following details, and then click Cancel:
• On the General tab, the Lookup transformation is configured to use full cache mode and an OLE DB connection manager, and to redirect rows with no matching entries.
• On the Connection tab, the Lookup transformation is configured to return the results of a Transact-SQL query using the localhost.Products OLE DB connection manager. The query returns a table that contains product data from the Products database.
• On the Columns tab, the Lookup transformation is configured to match rows in the data flow with products data based on the ProductKey column value, and add all of the other columns in the products data as new columns in the data flow.
8. Right-click the arrow between the Lookup Product Details transformation and the Staging DB destination, and then click Properties. Note that this arrow represents the lookup match output, so rows where a matching product record was found will follow this data flow path.
9. Right-click the arrow between the Lookup Product Details transformation and the Orphaned Sales destination, and then click Properties. Note that this arrow represents the lookup no match output, so rows where no matching product record was found will follow this data flow path.
MCT USE ONL
Y. STUDENT USE PR
OHIBITED
Lab 4: Implementing Data Flow in an SSIS Package L4-21
10. Double-click the Staging DB data destination, note the following details, and then click Cancel: • On the Connection Manager page, the data destination is configured to use an OLE DB
connection manager named localhost.Staging and to use a Table or view - fast load access mode to insert data into the [dbo].[ResellersSales] table.
• On the Mappings tab, the order of the columns in the destination is different from the column order in the source, and the ProductKey and ResellerKey source columns are mapped to the
ProductBusinessKey and ResellerBusinessKey destination columns.
11. Double-click the Orphaned Sales destination, note that it uses a flat file connection manager named
Orphaned Reseller Sales, and then click Cancel.
12. In the Connection Managers pane at the bottom of the design surface, double-click the Orphaned
Reseller Sales connection manager, note that the rows for sales with no matching product record are
redirected to the Orphaned Reseller Sales.csv text file in the D:\10777A\ETL folder, and then click
Cancel.
13. Right-click anywhere on the Data Flow surface, click Execute Task, and then observe the task as it runs, noting how many rows are transferred. There should be no orphaned sales records.
14. On the Debug menu, click Stop Debugging. 15. Keep Visual Studio open for the next task.
X Task 2: Create a Data Flow task
1. In the Solution Explorer pane, double-click Extract Internet Sales Data.dtsx.
2. View the Control Flow tab, and then in the SSIS Toolbox pane, in the Favorites section, double-click
Data Flow Task.
3. Drag the new Data Flow task under the existing Extract Customers task.
4. Right-click the new Data Flow task, click Rename, and then change the name to Extract Internet
Sales.
5. Click the Extract Customers Data Flow task, and then drag the arrow from the Extract Customers task to the Extract Internet Sales task.
6. Double-click the Extract Internet Sales task to view the Data Flow surface.
X Task 3: Add a data source to a data flow
1. In the SSIS Toolbox pane, in the Favorites section, double-click Source Assistant.
2. In the Source Assistant - Add New Source dialog box, in the Select source type list, select SQL
Server, in the Select connection manager list, select localhost.InternetSales, and then click OK.
3. Drag the new OLE DB Source data source to the center of the Data Flow surface, right-click it, click
Rename, and then change the name of the data source to Internet Sales.
4. Double-click the Internet Sales source, set the following configuration values, and then click OK: • On the Connection Manager page, ensure that the localhost.InternetSales OLE DB connection
manager is selected, in the Data access mode list, click SQL command, click Browse, and then import the InternetSales.sql query file from the D:\10777A\Labfiles\Lab04\Starter\Ex3 folder. • On the Columns tab, ensure that every column from the query is selected, and that the output
MCT USE ONL
Y. STUDENT USE PR
OHIBITED
L4-22 Module 4: Creating an ETL Solution with SSIS