Creating Event Handlers


The Event Handlers design surface is similar to the Control Flow design surface. In fact, creating an event handler is similar to building a package; an event handler has tasks and containers, which are sequenced into a control flow, and an event handler can also include data flows.

In SSIS, each event has its own event handler design surface for implementing compound workflows, including, but not limited to:

  • Tasks.

  • Precedences.

  • Expressions.

  • Viewers.

You create event handlers by using the design surface of the Event Handlers tab in SSIS Designer. When the Event Handlers tab is active, the Control Flow Items and Maintenance Plan Tasks groups of the Toolbox in SSIS Designer contain the task and containers for building the control flow in the event handler. To build an event handler, drag objects onto the designer from the Control Flow Items group in Toolbox.

The Data Flow Sources, Transformations, and Data Flow Destinations groups contain the data sources, transformations, and destinations for building the data flows in the event handler.

The Event Handlers tab also includes the Connection Managers pane, where you can create and modify the connection managers that event handlers use to connect to servers and data sources.

image from book
Creating the QuickStartODS Database

The procedures in this chapter assume that the QuickStartODS database exists on your computer in Microsoft SQL Server. The QuickStartODS database was created in Chapter 4, "Using Data Flow Transformations." If you haven't done the exercises in Chapter 4, the database might not exist on your computer.

image from book

Determine Whether the QuickStartODS Database is on Your Computer
  1. In SQL Server Management Studio, in Object Explorer, right-click the Databases folder, and then click Refresh.

  2. Expand the Databases folder and see whether QuickStartODS is listed. If it is not listed in the Databases folder, you must create the database by using the following instructions.

Create the QuickStartODS Database
  1. In SQL Server Management Studio, create a new database named QuickStartODS:

    1. In Object Explorer, right-click the Databases folder, and then click New Database.

    2. In the New Database dialog box, in the Database Name text box, type QuickStartODS.

    3. Click OK.

Accessing the SSIS Design Environment

You must create an event handler before you can use it to respond to an event. The first step is to open the solution so that you can access the designer in which the event handler is created.

Open the NewProducts.dtsx Package
  1. Using Windows Explorer, navigate to the C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap09\ folder.

  2. Double-click image from book Chap09.sln to open the project. The Chap09 project opens in SQL Server Business Intelligence Development Studio (BIDS).

  3. In BIDS, locate the Solution Explorer pane on the right side of the design environment. Make sure this pane is visible and not set to Auto-Hide, so you can see all of the files in the project.

  4. In the SSIS Packages folder, double-click image from book NewProducts.dtsx. The package opens in the designer.

    Your screen should look similar to this:

    image from book

Creating an OnPreExecute Event Handler

The OnPreExecute event handler is used to indicate that an object is about to start executing.

In this procedure, you'll open the Event Handlers design environment in which you create event handlers and then create the OnPreExecute event handler.

Create an OnPreExecute Event Handler
  1. In the design environment, on the image from book NewProducts.dtsx [Design]* tab, click the Event Handlers tab. The Event Handlers design environment opens.

  2. In the Executable drop-down list, select NewProducts, and then click OK.

  3. In the Event Handler drop-down list, select OnPreExecute.

  4. In the Event Handlers design environment, click the blue text that reads "Click here to create an OnPreExecute event handler for executable NewProducts."

    The Event Handlers control flow design environment appears and contains text that now reads "Event handlers are containers of workflow. They are executed when the selected event occurs. To build an event handler, drag objects here from the Control Flow Items toolbox."

    Your screen should look similar to this:

    image from book

Adding a Task to an Event Handler

Now that you have created an event handler, you must add the Execute SQL task to it. The Execute SQL task runs SQL statements or stored procedures from a package. The task can contain either a single SQL statement or multiple SQL statements that run sequentially. There are a variety of purposes for using the Execute SQL task, including using it to insert log records.

In this procedure, you'll add the Execute SQL task to the event handler you created in the previous procedure.

Add an Execute SQL Task to the OnPreExecute Event Handler
  1. On the left side of the screen, click Toolbox. The Toolbox menu appears.

  2. On the Toolbox menu, in the Control Flow Items group, drag Execute SQL Task onto the Event Handlers design environment. The Execute SQL Task box appears on the Event Handlers designer.

    Your screen should look similar to this:

    image from book

Configuring the Task

After you add the Execute SQL task to the event handler, you must configure the task. This procedure shows you how to access the SQL Task Editor, where you can modify the task properties.

Configure the Execute SQL Task
  1. In the design environment, double-click Execute SQL Task to open its editor. The Execute SQL Task Editor opens.

    image from book

  2. In the left pane, click General.

  3. In the right pane, under General, click Name, and then, in the right column, type Log Start.

  4. Under SQL Statement, click Connection, and then, in the right column drop-down list, select localhost.QuickStartODS.

  5. Under SQL Statement, click SQL Statement, and then, in the right column, click the ellipses button.

    The Enter SQL Query dialog box appears.

  6. Copy the following query:

     Insert into [dbo].[CustomSSISLog] ([PackageName] ,[SourceName] ,[SourceDescription] ,[Event] ,[EventDate]) VALUES (? ,? ,? ,'Start' ,GetDate()) 

  7. In the Enter SQL Query dialog box, paste the copied query.

    Note 

    Initially, the dbo.CustomSSISLog table doesn't exist in the QuickStartODS database. The ClearSSISCustomLog task drops and then re-creates the table each time the package is executed.

    Your screen should look like this:

    image from book

    Click OK to close the Enter SQL Query dialog box.

  8. Leave the Execute SQL Task Editor open.

Mapping SSIS Variables to SQL Statement Parameters

You can use parameter mapping to map parameters to a SQL INSERT statement or stored procedure. You use the Parameter Mapping page of the Execute SQL Task Editor to map variables to parameters in the SQL statement.

The following steps show you how to configure the Execute SQL task to run parameterized SQL statements. The parameters in the SQL statement are populated by system variables that are auto-generated when a package, container, or task is executed. When you assign a system variable to a parameter, you must configure it as an input or output parameter, select the data type, and give the parameter a name. When using an OLE DB connection, because the parameters are designated by their ordinal position in the SQL statement, they must be named 0, 1, 2, and so on.

Configure the Execute SQL Task to Run Parameterized SQL Statements
  1. In Execute SQL Task Editor, in the left pane, click Parameter Mapping.

  2. Click Add.

    A new variable is added to the editor. You will add three variables to the editor.

    For the first variable:

    1. In the right pane, in the Variable Name column drop-down list, select System::PackageName.

    2. In the Direction column drop-down list, select Input.

    3. In the Data Type column drop-down list, select NVARCHAR.

    4. In the Parameter Name column, type 0.

  3. Click Add to add a new variable.

  4. For the second variable:

    1. In the Variable Name column drop-down list, select System::SourceName.

    2. In the Direction column drop-down list, select Input.

    3. In the Data Type column drop-down list, select NVARCHAR.

    4. In the Parameter Name column, type 1.

  5. Click Add to add a new variable.

  6. For the third variable:

    1. In the Variable Name column drop-down list, select System::SourceDescription.

    2. In the Direction column drop-down list, select Input.

    3. In the Data Type column drop-down list, select NVARCHAR.

    4. In the Parameter Name column, type 2.

    Your screen should look like this:

    image from book

  7. You have now modified the properties of the Execute SQL task.

  8. Click OK to save your changes and close Execute SQL Task Editor.

Creating a Log Finish Event Handler

In the previous exercise, you created an OnPreExecute event handler that writes log entries immediately before the executable runs, and then you added a Log Start Execute SQL task to it. Now you'll create an OnPostExecute event that writes log entries immediately after the executable has finished running, and you'll add a Log Finish Execute SQL task to it.

In this exercise, you'll copy the Log Start Execute SQL task and then create an OnPostExecute event handler to which you'll add the Log Finish Execute SQL task.

Add a Log Finish Execute SQL Task to an OnPostExecute Event Handler
  1. In the Event Handlers design environment, right-click Log Start and click Copy to copy the Log Start Execute SQL task.

  2. Create a new event handler:

    1. In the Executable drop-down list, select NewProducts, and then click OK.

    2. In the Event Handler drop-down list, select OnPostExecute.

  3. Click the blue text that reads "Click here to create an OnPostExecute event handler for executable NewProducts."

  4. In the Event Handlers design environment, right-click and paste the Log Start Execute SQL task that you copied in the previous step. The Log Start task appears in the design environment.

  5. Right-click Log Start and rename the task Log Finish.

  6. Double-click Log Finish to open its properties. The Execute SQL Task Editor opens.

  7. In the right pane, under General, click Name, and then ensure that the name was changed to Log Finish.

  8. Under SQL Statement, click SQL Statement, and then, in the right column, click the ellipses button.

    The Enter SQL Query dialog box appears.

  9. In the Enter SQL Query dialog box, change the SQL Statement from Start to Finish (located toward the bottom of the code).

    Your screen looks like this:

    image from book

  10. Click OK to close the Enter SQL Query dialog box.

  11. Click OK to close the Execute SQL Task Editor.

Creating a Log Error Event Handler

In the previous exercises, you created an OnPreExecute event handler and an OnPostExecute event handler. Now you'll create an OnError event handler that writes log entries when an error occurs. After you have done this, you'll add a Log Error Execute SQL task to it.

In this exercise, you'll copy the Log Start Execute SQL task and then create an OnError event handler to which you'll add the Log Error Execute SQL task.

Add a Log Error Execute SQL Task to an OnError Event Handler
  1. In the Event Handlers design environment, in the Event Handler drop-down list, select OnPreExecute.

    Log Start Execute SQL Task appears in the design environment.

  2. Right-click Log Start and click Copy to copy Log Start Execute SQL Task.

  3. Create a new event handler:

    1. In the Executable drop-down list, select NewProducts, and then click OK.

    2. In the Event Handler drop-down list, select OnError.

  4. Click the blue text that reads "Click here to create an ‘OnError' event handler for executable NewProducts."

  5. In the Event Handlers design environment, right-click and paste the Log Start Execute SQL task that you copied in the previous step. Log Start Task appears in the design environment.

  6. Right-click Log Start and rename the task Log Error.

  7. Double-click Log Error to open its properties. The Execute SQL Task Editor opens.

  8. Under SQL Statement, click SQL Statement, and then, in the right column, click the ellipses button.

    The Enter SQL Query dialog box appears.

  9. In the Enter SQL Query box, change the SQL Statement from Start to Error (located toward the bottom of the code).

    The screen looks like this:

    image from book

  10. Click OK to close the Enter SQL Query dialog box.

  11. Click OK to close the Execute SQL Task Editor.

Executing the Package

Now that you have created the three event handlers that will be raised when errors occur, you can execute the package. You double-click image from book NewProducts.dtsx to execute the package in BIDS, and then you switch to SQL Server Management Studio to view the records in the log table.

Execute the NewProducts.dtsx Package and View the Results in Management Studio
  1. In BIDS, in the design environment, on the image from book NewProducts.dtsx [Design]* tab, click the Control Flow tab.

    You are now in the Control Flow design environment.

  2. On the right side of the screen, in Solution Explorer, right-click image from book NewProducts.dtsx and click Execute Package.

    Wait until the package has executed successfully before starting the next step. The package has successfully executed when, at the bottom of the screen, it reads "Package execution completed. Click here to switch to design mode, or select Stop Debugging from the Debug menu."

    Your screen should look similar to this:

    image from book

  3. On the Debug menu, click Stop Debugging.

  4. Click Start, click SQL Server Management Studio, and then, in the Connect to Server dialog box, click Connect to open SQL Server Management Studio.

  5. In SQL Server Management Studio, in the left pane, expand the Databases folder, expand QuickStartODS, expand the Tables folder, and then right-click dbo.Custom-SSISLog and select Open Table. The log records contained in dbo.CustomSSISLog are displayed.

  6. If dbo.CustomSSISLog is not listed in the Tables folder, right-click the Tables folder, and then click Refresh. The dbo.CustomSSISLog table now appears in the Tables folder list.

  7. Compare the log entries to the containers and tasks in the NewProducts package. In the log table, the SourceName column contains the name of the task or container, and the SourceDescription column contains the name of the event.

    When the package starts, the Clear SSISCustomLog task executes and clears the custom log table. This deletes the start event for the package and the start event for the Clear SSISCustomLog task. This means that the first event in the log table is the Clear SSISCustomLog finish event. Under normal circumstances, you would not have the first task in your package clear the log, so the first event in the log would be the package start event.

    The second and third records in the log table are the start and finish events for the Check Products List script task. These records are followed by the File Doesn't Exist sequence container start event and then the start and finish events for the Clear Product Names execute SQL task and Import Products data flow task. Then the finish event for the File Doesn't Exist record appears. If you look farther down in the table, you will see four sets of start and finish events for the Lookup Names data flow task. These are generated when the Foreach Loop container iterates over the FuzzyInput*.txt files in the Data folder. The last record is the finish event for the NewProducts package.

Testing the Package with Invalid Data

Now that you have created event handlers and executed the package, you are ready to test the package with invalid data. One of the files (FuzzyInputC.txt) includes incorrectly formatted data, which causes your package to fail when executed.

Test the NewProducts.dtsx Package with Invalid Data
  1. In Windows Explorer, navigate to the C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap09\Data\ folder.

  2. Rename the file image from book FuzzyInputC.txt.error image from book FuzzyInputC.txt (delete the .error extension).

  3. Double-click image from book FuzzyInputC.txt to view its contents in Notepad.

    Your screen should look similar to this:

    image from book

    Note 

    Instead of one product name per line, product names are separated by commas. This will cause an error.

  4. Close Notepad.

  5. In BIDS, in Solution Explorer, ensure that you are in the Control Flow designer. (Click the Control Flow tab.)

  6. Right-click image from book NewProducts.dtsx, and then click Execute Package.

    Your screen should look similar to this:

    image from book

  7. On the Debug menu, click Stop Debugging.

    On the Control Flow tab, notice that the Lookup Names error has escalated up to the Foreach Loop container and the File Exists container. (The Output window also shows that the package has failed.)

  8. Switch to SQL Server Management Studio.

  9. In SQL Server Management Studio, in the right pane, click the Table - dbo.CustomSSIS-Log tab to ensure that the log data is displayed.

  10. On the Query Designer toolbar, click the Execute SQL button (exclamation point) to refresh the displayed data.

    Note 

    If the data in CustomSSISLogKey is not displayed in ascending order, click the Show SQL Pane button, change the SQL query to SELECT * FROM CustomSSISLog ORDER BY 1, and then click the Execute SQL button (exclamation mark). You can then click the Show SQL Pane button to hide the SQL statement.

    Note 

    In the CustomSSISLog table, Lookup Names has two start and finish events and a third start event followed by multiple failure events caused by the incorrectly formatted data in image from book FuzzyInputC.txt. This is followed by a Lookup Names finish event. The image from book NewProducts.dtsx package doesn't attempt to open image from book FuzzyInputD.txt and Fuzzy-InputE.txt, so there are no more Lookup Names events. The Foreach Loop and File-Exists containers finish, and then the NewProducts package finishes.

Creating an Event Handler to Fix the Problem

In the previous exercise, incorrectly formatted data in the image from book FuzzyInputC.txt file caused the image from book NewProducts.dtsx package to fail when it was executed. In this exercise, you'll create a new event handler with the following properties:

  • Executable: Lookup Names

  • EventHandler: OnTaskFailed

Create an OnTaskFailed Event Handler for Executable Lookup Names
  1. In BIDS, click the Event Handlers tab.

  2. In the Executable drop-down list, expand NewProducts, expand Executables, expand File Exists, expand Executables, expand Foreach Loop Container, expand Executables, and then select Lookup Names.

    Your screen looks like this:

    image from book

  3. Click OK.

  4. In the Event Handler drop-down list, select OnTaskFailed.

    You are going to create an event handler that, when the Lookup Names task fails, the current input file is copied to the Errors folder. When the OnError event occurs because the Lookup Names task still has a lock on the file, preventing it from being copied, you'll use the OnTaskFailed event handler.

Creating a Task to Move the File with Invalid Data

After you create the OnTaskFailed event handler for executable Lookup Names, you'll create a File System task to move the invalid data to an Errors folder.

In this procedure, you'll move the file that caused the errors to a different folder. Finally, you'll create a file connection manager named Error.

Create a File System Task and a New Connection Manager
  1. In BIDS, in the Event Handlers design environment, click the blue text that reads "Click here to create an OnTaskFailed event handler for executable Lookup Names."

  2. On the left side of the screen, click Toolbox.

    Tip 

    If the toolbox has disappeared from your screen, click the View menu, and then click Toolbox.

  3. In the Toolbox, select File System Task and drag it onto the Event Handler designer.

  4. Double-click the File System Task box. The File System Task Editor opens.

  5. In the right pane, under General, click Name, and then, in the right column, type Copy to Errors Folder.

  6. Under Source Connection, click Source Connection, and then, in the right column drop-down list, click Product Input Names.

  7. Under Destination Connection, click Destination Connection, and then, in the right column drop-down list, select <New Connection>. The File Connection Manager Editor appears.

  8. In the File Connection Manager Editor, in the Usage Type drop-down list, select Existing folder.

  9. In the Folder box, browse to C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap09\Errors, and then click OK.

  10. Click OK to close the File Connection Manager Editor.

    This will create a new connection manager named Errors.

    Your screen looks like this:

    image from book

  11. Click OK to close the File System Task Editor.

    Your screen now looks like this:

    image from book

Setting Connection Manager Settings

After you create the new connection manager named Errors, you need to configure it.

In this procedure, you'll configure the settings for the Errors folder.

Set Connection Manager Settings
  1. In BIDS, in the Connection Managers pane (located on the bottom of the screen), right-click the new file connection manager named Errors and click Properties.

  2. In the Properties pane, located on the right side of the screen, click Alphabetical (the AZ button) to sort the properties alphabetically.

  3. In the Properties list, expand Expressions, and then, in the right column, click the ellipses button.

    The Property Expressions Editor opens.

  4. In the Property Expressions Editor, in the Property column drop-down list, select ConnectionString.

  5. In the Expression column drop-down list, click the ellipses button.

    The Expression Builder opens.

  6. In the Expression Builder, in the Expression pane located near the bottom of the window, type "C:\\Documents and Settings\\<username>\\My Documents\\Microsoft Press\\is2005sbs\\Chap09\\Errors\\"+@[User::InputFile].

  7. Click Evaluate Expression.

    The value you typed in the Expression pane is evaluated by SSIS and displayed in the Evaluated value pane. It's OK that there isn't a file name at the end of the file path-the variable User::InputFile is not populated until the Foreach Loop container executes.

    Your screen looks like this:

    image from book

  8. Click OK to close the Expression Builder.

  9. Click OK to close the Property Expressions Editor.

Preventing Events from Escalating to Containers and Packages

You have configured an error handler to copy the invalid file to the Errors folder. Because this event is "handled," you want the package to move on to the next file and continue executing. To do this, you need to prevent the error from escalating to the Foreach Loop container, the File Exists container, and the NewProducts package. You also need to configure the package to allow a limited number of errors to occur before the package fails.

Prevent the OnTaskFailed Event from Escalating from the Lookup Names Task to the Foreach Loop Container
  1. In BIDS, click the Event Handlers tab, then, on the SSIS menu, click Variables.

  2. On the upper-left side of the screen, click the Show System Variables button (the shaded X). The Variables list appears.

  3. Extend the window so that you can see all four columns.

  4. Click the Name column to sort the variables alphabetically.

  5. Find the Propagate variable.

  6. In the Value column drop-down list, change the Propagate variable value from True to False.

    Note 

    This prevents the OnTaskFailed event from escalating from the Lookup Names task to the Foreach Loop container.

    Your screen now looks like this:

    image from book

  7. Close the Variables list.

Changing Error Count Properties

After you have prevented the OnTaskFailed event from escalating from the Lookup Names task to the Foreach Loop container, you need to configure the package to allow a limited number of errors to occur before the package fails.

In this procedure, you'll change the maximum number of errors before the executable fails from 1 to 100.

Change the MaximumErrorCount Property from 1 to 100
  1. In BIDS, on the Control Flow tab, right-click anywhere in the design area, and then click Properties.

  2. In the Properties pane, on the right side of the screen, make sure that NewProducts Package is displayed in the drop-down list.

  3. In the Properties list, locate MaximumErrorCount, and then change the value from 1 to 100. (When the value is changed, the number becomes bold.)

    Note 

    The MaximumErrorCount property specifies the maximum number of errors before the executable fails.

    Your screen looks like this:

    image from book

  4. In the package designer, click File Exists, and then, in the Properties list, locate MaximumErrorCount and change the value from 1 to 100.

  5. In the package designer, click Foreach Loop Container, and then, in the Properties list, locate MaximumErrorCount and change the value from 1 to 100.

    Note 

    This prevents the Lookup Names task error from causing the containers and package to fail.

Executing the Package

Now that you have created a method for sending files with incorrectly formatted data to an Error folder without disrupting the package, you are ready to execute the image from book NewProducts.dtsx package once again.

Execute the NewProducts.dtsx Package
  1. In Solution Explorer, right-click image from book NewProducts.dtsx, and then click Execute Package.

  2. Notice that all the tasks in the package successfully execute except for the Lookup Names task, which fails.

    Your screen should look like this:

    image from book

  3. On the Debug menu, click Stop Debugging.

  4. Switch to SQL Server Management Studio.

  5. In SQL Server Management Studio, in the right pane, click the Table - dbo.CustomSSIS-Log tab to ensure that the log data is displayed.

  6. In the left pane, above Object Explorer, click the Execute SQL button (exclamation point) to refresh the displayed data.

    Note 

    The previous two steps assumed that you had the Table - dbo.CustomSSISLog table open. If the table wasn't open, in Object Explorer, right-click QuickStartODS, and then click Refresh. Right-click dbo.CustomSSISLog, and then click Open Table.

    Your screen looks like this:

    image from book

    Note 

    In the CustomSSISLog table, the Lookup Names task has two sets of start and finish event records as it processes image from book FuzzyInputA.txt and image from book FuzzyInputB.txt, and a third start event record (FuzzyInputC.txt) followed by multiple error events. The Copy to Errors Folder task has a start and finish event (the events associated with the error handler copying image from book FuzzyInputC.txt to the errors folder), and the Lookup Names task has two start and finish events as it processes image from book FuzzyInputD.txt and image from book FuzzyInputE.txt. The error handler allowed the package to handle the error and continue executing.

  7. In Windows Explorer, navigate to the C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap09\Errors folder and ensure that the image from book FuzzyInputC.txt file has been copied to this location.

  8. In BIDS, on the File menu, click Close to close the New Products package, and then close BIDS.

  9. In SQL Server Management Studio, on the File menu, click Close to close the Custom-SSISLog table, and then close Management Studio.




Microsoft SQL Server 2005 Integration Services Step by Step
MicrosoftВ® SQL Server(TM) 2005 Integration Services Step by Step
ISBN: 0735624054
EAN: 2147483647
Year: 2007
Pages: 152

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net