SSIS: Event Handlers

This post is an introduction of Event Handlers in SSIS.

Event handlers in SSIS are used to handle events. The events at SSIS level could be an error, a warning, information, a pre/post execution of a particular task, a variable value changes etc. SSIS provides flexibility to trap the events raised by executables at run time and do the processing as required by the user.

Event handlers tab is the place were the event handlers are defined.

To see the Events related to a specific control flow task, you can switch to Event Handler tab and select the particular Control flow task.

To demonstrate an example I selected the Data Flow Task, the events related with DFT were shown in the Event handler combo box.

We will see an example where the Data Flow Task Start Time and End Time can be captured at the Event Handler tab.

We can place a script task onpreExecute and onPostExecute events to show the Start and End Time of the DFT package.

MessageBox.Show("Package Start Time:" + DateTime.Now.ToString());

Inside the Script Task we are showing the date and time only:

MessageBox.Show("Package End Time:" + DateTime.Now.ToString());

Once we run the package we will get the message box which shows the start time and end time of DFT (Data flow task)

I hope you like the post.

For more information on Event Handlers you can visit this URL: http://msdn.microsoft.com/en-us/library/ms140223.aspx

SSIS: How to run your SQL Server 2000 DTS packages in SSMS 2005/2008?

This article focus on the concept that SQL Server 2000 DTS packages can be run in SQL Server Management Studio (SSMS) 2005/2008 without modification.

Until last week I do believe that SQL Server 2000 DTS packages can not be run on SSMS (SQL Server Management Studio) 2005/2008 directly. The only way was to use the Execute DTS 2000 Package Task in your control Flow.

We can run our SQL Server 2000 DTS packages with SSMS 2005/2008.

The very first thing required is your SQL Server 2005/2008 should have following components installed.

1. Microsoft SQL Server 2005 Backward compatibility Setup
2. Microsoft SQL Server 2000 DTS Designer Components

You can find the #1 in your SQL Server Setup DVD or download it from Microsoft site as per your current SQL environment.

You can find the #2 from Microsoft Download Center http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=11988

After obtaining the two components you need to install it on your SQL Server box

Once you installed the two components. You can open your SSMS (SQL Server Management Studio).

SSMS managed the DTS packages under Management->Legacy->Data Transformation Services

You can Open or Import an existing DTS package by right-clicking on Data Transformation Services

You can select an existing DTS package and it will appear under the Data Transformation Services tree. To open the DTS package right-click on the package name and select Open.

Your DTS package will open in the DTS 2000 Designer window.

You can run your DTS package and do any modification you like to do inside DTS 2000 Package Designer.

Note: Till the time your DTS 2000 Package Designer window is open you can not work with other SSMS windows. You need to close it first.

Moral of the story is you do not need to re-write your existing DTS packages into SSIS packages. You can run them from SSMS 2005/2008…but this do not mean you do not need SSIS further….SSIS is a different platform then the DTS and comes up with so many advantages which are difficult to ignore.

Please add your comments to improve this post or post your suggestions as you like.