Exercise 1: Debugging a Package
Scenario
You are developing an Integration Services package that retrieves employee data from multiple text files and inserts that data into the Employees table in the HumanResources database. You have developed the control flow and data flow, and you now plan to incorporate debugging and error handling functionality into the package.
This exercise's main tasks are:
1. Start the 6235A-NY-SQL-01 virtual machine, log on as Student, and set up the lab environment.
2. Open the SSIS_sol6 solution in Business Intelligence Development Studio, and then open the Employees package.
3. Add a breakpoint to the data flow task.
4. Add a breakpoint to the script.
5. Add a data viewer to the package.
6. Enable Integration Services logging, and display the Log Events pane.
7. Run the package and monitor events, row counts, data samples, and variable values as they change.
8. Remove the breakpoints and the data viewer.
Task 1: Start the 6235A-NY-SQL-01 virtual machine, 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:\MOD06\Labfiles\Starter\Setup.cmd file.
Task 2: Open the SSIS_sol6 solution in Business Intelligence Development Studio
• The SSIS_sol6.sln file is in the E:\MOD06\Labfiles\Starter\SSIS_sol6 folder.
• When you have opened the solution, open the Employees.dtsx package in SSIS Designer.
Task 3: Add a breakpoint to the Data Flow task
• Add the breakpoint to the DFT Retrieve employee data task on the Control Flow design surface.
• Use the Break when the container receives the OnPreExecute event break condition.
Task 4: Add a breakpoint to the script
• Add the breakpoint to the following line of code in the script:
Dim sw As StreamWriter
Task 5: Add a data viewer to the package
• Add the data viewer to the data flow path that connects the Flat File source to the Data Conversion transformation in the DFT Retrieve employee data data flow.
• Use a grid data viewer.
Task 6: Enable Integration Services logging, and display the Log Events pane
• Enable logging on the package object.
• Log OnError and OnPreExecute events.
• Click Log Events on the SSIS menu to display the Log Events pane.
Task 7: Run the package and monitor events, row counts, data samples, and variable values as they change
• Run the package in debug mode.
• When you reach a break in the control flow, add a watch on the EmployeeFile variable, and monitor its value in the Watch 1 debugging window.
• When you reach a break in the script, monitor the value of the EmployeeFile variable in the Locals debugging window.
• View row counts in the data flow when you break to view the data viewer window.
• View events in the Log Events pane when package execution is complete.
Task 8: Remove the breakpoints and the data viewer
• Delete the data viewer from the data flow path.
• Click Delete All Breakpoints on the Debug menu to remove all breakpoints.
Results: After this exercise, you should have successfully configured the breakpoints, enabled logging, configured logging, run the package, and verified that the data is viewable at each breakpoint.
Exercise 2: Implementing Error Handling
Scenario
To incorporate error handling, you plan to implement custom logging, catch errors in your script, and control failure behavior to test the error-handling functionality.
This exercise's main tasks are:
1. Create a table in the HumanResources database to log errors.
2. Add an event handler to capture errors generated by the Foreach Loop container and its child tasks.
3. Configure the event handler to prevent propagation.
4. Add an Execute SQL Task to the event handler.
5. Add a Try-Catch block to the script in the Script task.
6. Modify Integration Services logging to log only errors.
7. Configure the employee text files as read-only.
8. Run the package, and view the events in the Log Events pane.
9. View the data in the Employees and LogEvents tables.
Task 1: Create a table in the HumanResources database to log errors
• Run the LogEventsTable.sql query file.
• The file is located in the E:\MOD06\Labfiles\Starter\SetupFiles folder.
• You can use the sqlcmd utility or SQL Server Management Studio to run the query file.
Note: Type sqlcmd /? at a command prompt for help on sqlcmd syntax.
Task 2: Add an event handler to capture errors generated by the Foreach Loop container and its child tasks
• Use the OnError event.
Task 3: Configure the event handler to prevent propagation
• Set the Propagate system variable for the event handler to False.
Task 4: Add an Execute SQL Task to the event handler
• Use the following settings for the Execute SQL task:
• Name: SQL Log errors
• Description: Log error data and file name
• Connection type: OLE DB
• Connection manager: HumanResources_cm
• SQL source type: direct input
• Use the following INSERT statement to add a row to the RowCounts table:
INSERT INTO hr.LogEvents
(ExecGUID, PackageName, SourceName,
ErrorCode, ErrorDescription, EmployeeFile) VALUES (?,?,?,?,?,?)
• Create six input parameter mappings for the following variables:
• ExecutionInstanceGUID (data type: NVARCHAR; parameter name: 0)
• PackageName (data type: NVARCHAR; parameter name: 1)
• SourceName (data type: NVARCHAR; parameter name: 2)
• ErrorCode (data type: LONG; parameter name: 3)
• ErrorDescription (data type: NVARCHAR; parameter name: 4)
• EmployeeFile (data type: NVARCHAR; parameter name: 5)
Task 5: Add a Try-Catch block to the script in the Script task
• Add a Try statement before the first variable declaration.
• Add a Catch block after you close and flush the StreamWriter object. The Catch block should use the Dts.Events.FireError method to raise an error if an exception occurs. The error should include a message about not being able to write to the employee file. The Catch block should also inform runtime of the failure.
• Add an End Try statement after the Catch block.
Task 6: Modify Integration Services logging to log only errors
• Log the OnError event only. Remove the OnPreExecute event.
Task 7: Configure the employee text files as read-only
• The files are in the E:\MOD06\Labfiles\Starter\SetupFiles folder.
Task 8: Run the package, and view the events in the Log Events pane
• Run the package in debug mode.
• View events in the Log Events pane.
Task 9: View the data in the Employees and LogEvents tables
• The tables are in the hr schema of the HumanResources database.
• Use SQL Server Management Studio to view the table contents.
• The Employees table should contain 100 rows, and the LogEvents table should contain one row.
Results: After this exercise, you should have successfully added a try/catch block, configured the variable to log, run the package, and then confirmed the entries in the hr.LogEvents table.
Exercise 3: Controlling Failure Behavior
Scenario
In this exercise, you will configure the Foreach Loop container and the Script task to control the failure behavior of those tasks and the package. You will run the package each time you modify the settings to view the results of your changes.
This exercise's main tasks are:
1. Configure the MaximumErrorCount property of the Foreach Loop container.
2. Run the package, and view the events in the Log Events pane.
3. View the data in the Employees and LogEvents tables.
4. Configure the employee text files as read-write.
5. Configure the ForceExecutionResult property of the Script task.
6. Run the package, and view the events in the Log Events pane.
7. View the data in the Employees and LogEvents tables.
Task 1: Configure the MaximumErrorCount property of the Foreach Loop container
• Set the property value to 3.
Task 2: Run the package, and view the events in the Log Events pane
• Run the package in debug mode.
• View events in the Log Events pane.
Task 3: View the data in the Employees and LogEvents tables
• The Employees table should contain 290 rows, and the LogEvents table should contain multiple rows, from the previous package execution and from the most recent execution.
Task 4: Configure the employee text files as read-write
• The files are in E:\MOD06\Labfiles\Starter\SetupFiles.
• Highlight the three files, right-click the files, and click Properties. In the Properties dialog box, clear the Read-only check box.
Task 5: Configure the ForceExecutionResult property of the Script task
• Set the property value to Failure.
Task 6: Run the package, and view the events in the Log Events pane
• Run the package in debug mode.
• View events in the Log Events pane.
Task 7: View the data in the Employees and LogEvents tables
• The Employees table should contain 290 rows, and the LogEvents table should not contain any rows related to the forced failure.
Results: After this exercise, you should have successfully modified the package failure behavior, run the package, and verified that the correct rows appear in the database tables.
Lab Shutdown
After you complete the lab, you must shut down the 6235A-NY-SQL-01 virtual machine and discard any changes.
Module Review and Takeaways
Review Questions
1. What options should you consider for handling bad data?
2. How would you use a breakpoint and a data viewer to find out why an error is occurring?
3. How do you handle exceptions in a Script task?