Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This tutorial will help you to get started with SSIS (SQL Server Integration Services)

Getting started with SSIS

Getting started with SSIS - Part 10: Event Handling and Logging

Mar 6 2012 12:00AM by Sudeep Raj   

Let us now add some more features to our package. We would now add Event handling and Logging to our package created. Before doing that, let us see what do the two means.

Event Handling: As the name suggests, based on certain event, we would like to take some action (handle the event) the way we want. We may want to shoot an email in case of an error or failure in the package. Or we might want to truncate a table once the ETL is completed.

Logging: Again, as the name suggests, we need to log the events that are happening in our package. For example, we need to know which all tasks have executed successfully or have failed. In case of failure, what was the error message etc.

The difference between the two is that in Logging, we merely record the events and any message generated by the system to a log file, table etc. While in case of Event Handling, we may wish to take additional action based on the events.

Having said this, we shall start the demo on Event Handling in SSIS.

We will go back to our package created earlier. It has just a data flow task to move the data from Flat file to a database table. What we will do is to introduce an error in the package and then handle the event in a way we want.

Here, we have the package ready and the data flow task as mentioned above (see image below). Please look along and do as explained along with the images.

SSIS – Control flow containing a Data Flow Task in BIDS

In the above Data Flow Task, we have just a Flat File Source and an OLEDB Destination (see the image below).

SSIS Data Flow Task with source and destination

Now we start with the Event Handling. Take a look at the figure below. You will notice we have now moved to a new tab Event Handlers. You will see two dropdown boxes, one stating Executable and other stating Event Handler.

SSIS - Event Handler tab

Click on the 2 dropdowns and you would see the values as in the figure below. Executables are all the tasks that you see on the Control Flow. While Events are the possible events that can happen on the above executables. I select the Data Flow task in the Executable and for handling I will select OnError event.

Event Handler – Executable and Event handlers

Once we make the above selection, we will see the screen below:

SSIS - Event Handler tab disabled

Click on the hyperlink and the screen would look like the one in the figure below:

SSIS - Event Handler tab enabled

The point to be noted is that we can have all the tasks that we have in Control Flow in the event handler. We can have a kind of small package here, which does its own ETL. Event handlers are very powerful and useful tool available to the SSIS developers.

We have selected the task as Data Flow Task and the event we want to handle is onError. This means that when an error will occur this event will be fired and what task we drop in the above area (as in the figure above) will be executed if the Data Flow task fails.

To achieve this we need to introduce an error in the package. All you need to do to achieve this is to open the flat file connection we have and change the file name to a non-existing name. Now when the package executes, it will not find the file and the package will fail. Now, what we want is whenever this happens or other error occurs, an entry per error should be recorded in a log table we have.

Use the query below to create the log table

CREATE TABLE [dbo].[Log](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [PackageID] [uniqueidentifier] NULL,
    [Error] [nvarchar](max) NULL,
    [Source] [nvarchar](100) NULL,
    [PackageName] [nvarchar](100) NULL
) ON [PRIMARY]

Let is now get back to our package.

Double click the Execute SQL Task to open the Editor window as in figure below

Execute SQL Task editor

Set up the Connection String and use the below Insert Query in the SQL Statement.

INSERT INTO [dbo].[Log]
           ([PackageID]
           ,[Error]
           ,[Source]
           ,[PackageName])
     VALUES
           (?,?,?,?)

The question marks used above are the parameters we pass to the above query. Based on the mapping we do in the next step, these will get the value.

Next, go to the Parameter Mapping tab as shown in the next figure. I will not the talking on all the details you see on this tab. We will cover it in later section. As of now, I would ask you to set up your Parameter Mapping exactly as you see here. One point to note here is that we are using the system variables and not user variables to map the values for the parameters. Here you will see how useful the system variables are. Once you have set up parameter mapping, click OK. You are done.

Execute SQL Task editor: Parameter mapping

Now execute the package by clicking F5. You will see that the package fails and the Data Flow Task goes red (see figure below). If this does not happen and the package is executed successfully, ensure that you have changed the name of the file in the flat file connection manager to a non-existent file name.

Package fails with Data Flow Task failing

If you double click the Data Flow Task, you will notice that the Flat file Source has failed. I hope you know the reason.

Flat File source fails in the data flow task

If you now go to the Event Handler tab, you will notice that the Execute SQL Task that we had put here has executed successfully.

The Execute SQL Task in Event handler executes successfully

Go to the Data base where you created the Log table and select the records from the log table.

SELECT * FROM LOG

You should the following output:

table

Now instead of the execute SQL task, you could have a send mail task to notify the admin etc.

LOGGING

We will now see how to implement logging in SSIS Packages. This is rather simple to implement.

Go to the SSIS Menu and select logging from there.

Start SSIS Logging from menu

We have various options to save the SSIS Logging (see figure below). We can save the logs to Windows Event log, a Text File, XML File, SQL Server Table or SQL Profiler. The choice is yours, choose the one you feel perfect for your need. I will use SQL Server Table as the provider. Select the Provider as SQL Server and click the Add button next to it.

Configure SSIS Logs – Provider type

Select the SQL Connection where you want to log the events. I will use the same Database Connection I used in the Data Flow Task. You can have separate database for logging as practiced in real life scenarios. Also, check the checkbox on the left side else, the logging will not occur. You need to do the log for the entire package; you could select the executable for which you want to enable to logging. I have selected the entire package for logging.

Configure SSIS Logs – Connection setup

Once you are done with the above setting, go to the Details tab. Here, you will see the various events that you have for each executable. Select the events you wish to log. Generally we log the following events:

  • OnValidate
  • OnPreExecute
  • OnWarning
  • OnError
  • OnPostExecute

Configure SSIS Logs – Details tab

We are done!! Execute the package, go to the data base that you configured for logging above and fire this query:

SELECT * FROM dbo.sysssislog

This table is automatically created, you can change the table name but that will be shown in later articles. Check the output you get for the above.

In the next article we will take a look at the various ways to execute a package.


Sudeep Raj
12 · 13% · 4303
7 Readers Liked this
Neha Mewara Liked this on 3/5/2012 1:06:00 PM
Profile
Khyati Patel Liked this on 3/7/2012 12:02:00 AM
Profile · Facebook · Twitter
Kirti M.Darji Liked this on 3/11/2012 4:43:00 AM
Profile · Blog · Facebook · Twitter
Guru Samy Liked this on 3/23/2012 3:18:00 AM
Profile · Blog
Jacob Sebastian Liked this on 6/15/2012 3:24:00 AM
Profile · Blog · Facebook · Twitter
Nirav Gandhi Liked this on 6/15/2012 3:51:00 AM
Profile · Blog · Facebook · Twitter
Bala Krishna Liked this on 1/8/2013 2:30:00 AM
Profile
7
Liked



Submit

9  Comments  

  • Hi

    I got a problem completing the tutorial part 10 I get following error: Can please assist

    TITLE: Microsoft Visual Studio

    Failed to create the task.


    ADDITIONAL INFORMATION:

    Cannot create a task with the name "Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91". Verify that the name is correct. (OnError)

    commented on Jun 15 2012 3:10AM
    Goods
    2376 · 0% · 5
  • Are you facing this issue with only execute SQL Task or other tasks as well? Seems that the SSIS is not correctly installed. Options you could try are:

    • Remove the task from the tools list and try adding it again
    • Repair your
    • SQL server once Try reinstall of
    • BIDS and SSIS
    commented on Jun 15 2012 3:23AM
    Sudeep Raj
    12 · 13% · 4303
  • HI Sudeep,

    Really fantastic one.

    i just trying to add Package start time and Package end time to your LOG Table. table structure looks as follows.

    CREATE TABLE [dbo].Log NOT NULL, [PackageID] [uniqueidentifier] NULL, [PackageStartTime] nvarchar NULL, [Error] nvarchar NULL, [Source] nvarchar NULL, [PackageEndTime] nvarchar NULL, [PackageName] ) ON [PRIMARY]

    My Question is which system variables i need to map to insert script.

    commented on Jan 8 2013 2:36AM
    Bala Krishna
    83 · 2% · 676
  • For Package Start Time you can use the system variable StartTime and for the end time you can use GETDATE() function as thats the time when the package fails or you goto the event log.

    commented on Jan 8 2013 2:51AM
    Sudeep Raj
    12 · 13% · 4303
  • Thanks for your reply Sudeep,

    I am struggling to add GETDATE() function to the Package endtime. Can u give me the steps.

    commented on Jan 8 2013 4:08AM
    Bala Krishna
    83 · 2% · 676
  • You donot need to pass the parameter. You can directly have the code like:

    INSERT INTO [dbo].[Log]
             ([PackageID]
             ,[Error]
             ,[Source]
             ,[PackageName]
             ,PackageEndTime)
        VALUES
              (?,?,?,?,GETDATE())
    
    commented on Jan 8 2013 4:19AM
    Sudeep Raj
    12 · 13% · 4303
  • Thanks a lot, Its working now.

    commented on Jan 8 2013 4:23AM
    Bala Krishna
    83 · 2% · 676
  • Thanks Sudeep, here is a task that i'm planning to do please tell me if that is feasible using the even handler? I have a package with 5 containers A,B,C,D and E. When the container C fails i get an error that the package failed and now only the containers A and B are loaded not D and E. Is there a way using the event handler "on error" (when the container C fails) execute containers D and E

    commented on Mar 8 2013 2:15PM
    venkatb
    2838 · 0% · 3
  • That's simple, double click the precedence Constraint (green Arrow) change the condition from Success to Completion.

    commented on Mar 10 2013 9:06AM
    Sudeep Raj
    12 · 13% · 4303

Your Comment


Sign Up or Login to post a comment.

"Getting started with SSIS - Part 10: Event Handling and Logging" rated 5 out of 5 by 7 readers
Getting started with SSIS - Part 10: Event Handling and Logging , 5.0 out of 5 based on 7 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]