TD4: Implementing Data Flow Exercise 1:Transferring Data Scenario The first file should contain sales data about orders for sales over $10,000 per line item. The file should include the ID of the sales person who created the order. This 1. 2. 3. 4. 5. 6. 7.
exercise’s main tasks are: Start the 6235A-NY-SQL-01 virtual machine and log on as Student. Open the SSIS_sol4 solution. Create a connection manager for the AdventureWorks2008 database. Add an OLE DB Source to the DFT Create sales file data flow. Add a Flat File Destination to the DFT Create sales file data flow. Run the Employees package. View the SalesData.csv file.
Task 1: Start the 6235A-NY-SQL-01 virtual machine and log on as Student, and set up the lab environment •
Start 6235A-NY-SQL-01, and log on as Student with the password of Pa$$w0rd.
•
Run the E:\MOD04\Labfiles\Starter\Setup.cmd file to create the HumanResources database and necessary folder structure
Task 2: Open the SSIS_sol4 solution •
The SSIS_sol4.sln file is in the E:\MOD04\Labfiles\Starter\SSIS_sol4 folder.
•
On the File menu, click New, and then click Project.
•
When you have opened the solution, open the Employees.dtsx package in the SSIS Designer.
Task 3: Create a connection manager for the AdventureWorks2008 database •
Use the following settings for the connection manager: o
Provider: SQL Native Client 10.0
o
Server name: NY-SQL-01
o
Authentication: Windows Authentication
o
Database: AdventureWorks2008
•
Test the connection.
•
Name the connection manager AdventureWorks2008_cm..
Task 4: Add an OLE DB Source to the DFT Create sales file data flow •
Use the following settings for the source:
A. ELBYED
o
Name: OLE Retrieve sales data
o
Description: Retrieve data from SalesOrderHeader and SalesOrderDetail tables in AdventureWorks
o
Connection manager: AdventureWorks_cm
Page 1
TD4: Implementing Data Flow o •
Data access mode: SQL command
Use the following SQL statement for the SQL command.
SELECT h.SalesOrderID, d.OrderQty, d.ProductID, d.UnitPriceDiscount, FROM Sales.SalesOrderHeader Sales.SalesOrderDetail ON h.SalesOrderID WHERE d.LineTotal > 10000 •
h =
h.SalesPersonID, d.UnitPrice, d.LineTotal JOIN d d.SalesOrderID
Preview the data.
Task 5: Add a Flat File Destination to the DFT Create sales file data flow • •
•
Connect the data flow output from the OLE DB source to the Flat File destination. Use the following settings for the Flat File destination: o
Name: FFD Load sales data
o
Description: Add data to the SalesData.csv file
o
Connection manager: create a new connection manager
Use the following settings for the new connection manager: o
Format: delimited
o
Name: SalesData_cm
o
Description: Connects to the SalesData.csv file
o
File name: E:\MOD04\LabFiles\Starter\Employees\SalesData.csv
o
Use the default column mappings.
Task 6: Run the Employees package. • •
Run the package in debug mode. Verify that all components run successfully and that 766 rows pass through the DFT Create sales file data flow.
Task 7: View the SalesData.csv file. • •
The file should be in the E:\MOD04\LabFiles\Starter\Employees folder. Verify that the file contains the sales data
Results: After this exercise, you should have successfully added a flat file destination and verified that the SalesData.csv file contains the correct data.
Exercise 2: Implementing Transformations Scenario You should also generate two text files based on employee data. Employee e-mail addresses are stored separately from employee details, so your data flow must include a lookup to retrieve e-mail addresses based on the employee ID. You should then sort the data by the employees’ last names and then first names. The first text file should include only employees who are sales reps. The second text file should include all other employees.
A. ELBYED
Page 2
TD4: Implementing Data Flow This exercise's main tasks are: 1. Create a connection manager for the HumanResources database. 2. Add an OLE DB Source to the DFT Create employee files data flow. 3. Add a Lookup transformation to the DFT Create employee files data flow. 4. Add a Sort transformation to the DFT Create employee files data flow. 5. Add a Conditional Split transformation to the DFT Create employee files data flow. 6. Add a Flat File Destination for sales reps to the DFT Create employee files data flow. 7. Add a Flat File Destination for non-sales reps to the DFT Create employee files data flow. 8. Run the Employees package. 9. View the SalesReps.txt and NonSalesReps.txt files.
Task 1: Create a connection manager for the HumanResources database •
Use the following settings for the connection manager: o
Provider: SQL Native Client 10.0
o
Server name: NY-SQL-01
o
Authentication: Windows Authentication
o
Database: HumanResources
•
Test the connection.
•
Name the connection manager HumanResources_cm
Task 2: Add an OLE DB Source to the DFT Create employee files data flow •
Use the following settings for the source: o
Name: OLE Retrieve employee data
o
Description: Retrieve data from Employees table in HumanResources
o
Connection manager: HumanResources_cm
o
Data access mode: Table or view
o
Table: hr.Employees
•
Preview the data.
•
Add an annotation to the data flow to describe the data extraction process.
Task 3: Add a Lookup transformation to the DFT Create employee files data flow • •
•
Connect the data flow output from the OLE DB source to the Lookup transformation. Use the following settings for the transformation: o
Name: LKP Look up e-mail addresses
o
Description: Retrieve e-mail address from EmailAddresses table
o
Connection manager: HumanResources_cm
o
Table: hr.EmailAddresses
o
Joined column: EmployeeID
o
Lookup columns: EmailAddress
o
Lookup operation: add as new column
Add an annotation to the data flow to describe this transformation process.
A. ELBYED
Page 3
TD4: Implementing Data Flow Task 4: Add a Sort transformation to the DFT Create employee files data flow •
•
Connect the data flow output from the Lookup transformation to the Sort transformation. o
Use the following settings for the transformation:
o
Name: SRT Sort names
o
Description: Sort by last name, then first name
o
Sort columns: LastName, FirstName
o
Sort order: LastName, FirstName
o
Sort type: ascending
Add an annotation to the data flow to describe this transformation process
Task 5: Add a Conditional Split transformation to the DFT Create employee files data flow •
Connect the data flow output from the Sort transformation to the Conditional Split transformation.
•
Use the following settings for the transformation:
•
o
Name: SPL Separate employees
o
Description: Separate sales reps from other employees
o
Output names: SalesReps, NonSalesReps
o
SalesReps condition: JobTitle == "Sales Representative"
o
NonSalesReps condition: JobTitle != "Sales Representative"
Add an annotation to the data flow to describe this transformation process
Task 6: Add a Flat File Destination for sales reps to the DFT Create employee files data flow. •
•
Connect the data flow output for sales reps from the Conditional Split transformation to the Flat File destination. o
Use the following settings for the Flat File destination:
o
Name: FFD Load sales reps file
o
Description: Add data to the SalesReps.txt file
o
Connection manager: Create a new connection manager.
o
Use the default column mappings.
o
Use the following settings for the new connection manager:
Format: delimited
Name: SalesReps_cm
Description: Connects to the SalesReps.txt file
File name: E:\MOD04\LabFiles\Starter\Employees\SalesReps.txt
Add an annotation to the data flow to describe this transformation process
Task 7: Add a Flat File Destination for non-sales reps to the DFT Create employee files data flow.
A. ELBYED
Page 4
TD4: Implementing Data Flow • •
•
Connect the data flow output for non-sales reps from the Conditional Split transformation to the Flat File destination. Use the following settings for the Flat File destination: o
Name: FFD Load non-sales reps file
o
Description: Add data to the NonSalesReps.txt file
o
Connection manager: Create a new connection manager.
o
Use the default column mappings.
o
Use the following settings for the new connection manager:
Format: delimited
Name: NonSalesReps_cm
Description: Connects to the NonSalesReps.txt file
File name: E:\MOD04\LabFiles\Starter\Employees\NonSalesReps.txt
Add an annotation to the data flow to describe the data load process
Task 8: Run the Employees package. •
Verify that all components run successfully and that 290 rows pass through the data flow—14 sales reps and 276 other employees.
Task 9: View the SalesReps.txt and NonSalesReps.txt files. • •
The files should be in the E:\MOD04\LabFiles\Starter\Employees folder. Verify that the files contain the employee data.
Results: After this exercise, you should have successfully created a data flow with the proper transformations and verified that the SalesReps.txt and NonSalesReps.txt files contain the correct records.
Exercise 3: Using Data Viewers Scenario Now that you have created your data flows, you need to verify and test their functionality using data viewers.
This 1. 2. 3. 4. 5. 6.
exercise's main tasks are: Add a grid data viewer to the DFT Create sales file data flow. Add a histogram data viewer. Add a scatter plot data viewer. Add a column chart data viewer. Run the package, and view the data viewers. Remove the data viewers.
Task 1:Add a grid data viewer to the DFT Create sales file data flow • •
Add the data viewer to the data flow path between the OLE DB source and the Flat File destination. Name the data viewer Grid: sales data.
A. ELBYED
Page 5
TD4: Implementing Data Flow •
Include all columns except the SalesPersonID column.
Task 2: Add a histogram data viewer • •
Add the data viewer to the data flow path between the OLE DB source and the Flat File destination. Name the data viewer Histogram: sales data.
•
Use the OrderQty column.
Task 3: Add a scatter plot data viewer • • •
Add the data viewer to the data flow path between the OLE DB source and the Flat File destination. Name the data viewer Scatter Plot: sales data. Use the OrderQty column as the X-axis column.
•
Use the UnitPrice column as the Y-axis column.
Task 4: Add a column chart data viewer • •
Add the data viewer to the data flow path between the OLE DB source and the Flat File destination. Name the data viewer Column Chart: sales data.
•
Use the OrderQty column..
Task 5: Run the package, and view the data viewers • • •
Run the package in debug mode. View the grid data viewer, and then detach it without closing it. View the other data views, and then close them.
•
Close the grid data viewer after the package stops running
Task 6: Remove the data viewers •
Delete all of the data viewers from the data flow path.
Results: After this exercise, you should have successfully added a data viewer of each type, viewed the data in each data viewer, and then removed the data viewers from the data flow.
Exercise 4: Configuring Error Output Scenario Now, you need to configure your data flow to handle failed rows so that you can look at them later. This exercise’s main tasks are: 1. Add a row to the Employees table in the HumanResouces database. 2. Add a Flat File Destination for failed rows to the DFT Create employee files data flow. 3. Run the Employees package. 4. View the LookupErrors.txt file.
A. ELBYED
Page 6
TD4: Implementing Data Flow Task 1: Add a row to the Employees table in the HumanResouces database •
Run the E:\MOD04\Labfiles\Starter\AddEmployee.cmd file to insert a row into the hr.Employees table with no matching record in the hr.EmailAddresses table.
Task 2:Add a Flat File Destination for failed rows to the DFT Create employee files data flow •
Connect the error output from the Lookup transformation to the Flat File destination.
•
Configure the Lookup transformation to redirect rows for all errors and truncations.
•
Use the following settings for the Flat File destination: o
Name: FFD failed rows
o
Description: Add failed rows to the LookupErrors.txt file
o
Connection manager: Create a new connection manager.
o
Use the default column mappings.
o
Use the following settings for the new connection manager:
Format: delimited
Name: LookupErrors_cm
Description: Connects to the LookupErrors.txt file
File name: E:\MOD04\LabFiles\Starter\Employees\LookupErrors.txt.
Task 3: Run the Employees package •
Verify that all components run successfully and that 291 rows pass through the data flow—1 error output row, 14 sales reps, and 276 other employees.
Task 4: View the LookupErrors.txt file • •
The file should be in the E:\MOD04\LabFiles\Starter\Employees folder. Verify that the file contains the error data for the employee with an EmployeeID value of 9999y.
Results: After this exercise, you should have successfully added a flat file destination for error output, run the package, and verified that the unmatched record appears in the LookupError.txt file.
A. ELBYED
Page 7
