Event handlers in SSIS?

Events of various types occur one after another from the time you start to execute a package till the time it completes successfully or otherwise. The OnError event, which is raised when an error occurs, is the default event in SSIS. In addition to the package, various components such as for loop, task host containers, etc. can raise events. Events can be raised by other executables such as For loop, and Task host containers. You can create a custom event handler that traps these errors for some useful purpose. The below are the available event handlers which are visible in the Package Design Event handlers Page.

Containers in a package follow a hierarchy. If the event in a container is not handled, it can escalate the handling of the event to a container higher up in the hierarchy, quite like events on a web page which also have a container hierarchy. If there are no event handlers configured at any of the containers, the event never gets handled.

Like other objects in SSIS, event handlers need a name and a description. You may need to indicate whether or not the event handler runs and also indicate whether the package fails if the event handler fails; you may also indicate an execution result to return instead of the actual execution result for the event handler at run time.

The Event Handler page can be displayed by clicking on the Event Handler tab. The top object for this page is the Package itself as shown with the default event, OnError. Of course there are other event handlers to choose from the drop-down list.

A package is not the only object that can raise events. Objects contained in the package can also raise events. This can be seen by clicking on the drop-down for Executable in the above screenshot. In the next screenshot, Execute SQL Task, and Data Flow Task are both executables and have Event handlers associated with them, with which you can trap events.

How to access the Package Level Variables in the Script Task Code?

Dts.Variables.Item(”<>”).Value

How to use a File System Task with a Source/Destination Variable?

Suppose there is a flat file named “MyFile.Year.Month.Day.txt”, with the Year, Month and Day portions of it changing constantly, that I want to move from a one folder to an another folder. This task is easy except when you take into consideration that every day the file name is different.

1. Use the Script Task before the actual data transformation to locate the name of the file.
   First create the three package level variables with string type
   • ToFilename
   • TrueFileName
   • Unprocessed_location
2. Enter the value of Unprocessed_location as the UNC path of the Unprocessed files. e.g \\MyServer\MyFolder\. make sure that last slash.
3. Drag and drop the script task to the control flow window.
4. Give a friendly name, and on the script tab, put in TrueFileName,Unprocessed_location as Readonly variables.
5. Enter the ToFilename as the ReadWrite Variable.
6. Click the Design Script and the following is the script code.

7. Imports System
   Imports System.Data
   Imports System.Math
   Imports Microsoft.SqlServer.Dts.Runtime
   
   Public Class ScriptMain
   
       Public Sub Main()
   
           Dim ToFileName As String
   
           ToFileName = CStr(Dts.Variables.Item("Unprocessed_location").Value) & LTrim(CStr(Dts.Variables.Item("TrueFileName").Value))
   
           Dts.Variables.Item("ToFileName").Value = ToFileName
   
           Dts.TaskResult = Dts.Results.Success
       End Sub
   
   End Class
   

8. Once Script task is done , close it and create the Flat File Connection manager.
9. You will have to put the literal path in the box titled “File name”.
10. Make sure a file should be there in the folder, otherwise create a file as per instruction in below.
    type in a line of text for the column headers separated by commas, as shown in the next paragraph
    CustomerID, CompanyName, Address, City, PostalCode
11. Go to the properties of the Flat File Connection Manager, Click the Expressions Box and open the pop up window with ellipses.
12. Choose the connection string under the property and then click the ellipses in the new expression column. Choose the “@[User::ToFileName]“.
13. Then Click OK again.
14. Take the File System Task.
15. Click Edit in the right Click Menu.
16. Make sure IsSourcePathVariable = False and set the SourceConnection to your Flat File Connection.
17. Unfortunately, setting it to another variable doesn’t work unless the variable is a constant, never changing, and the value is filled in on the Variables tab.
18. Fortunately, the Destination doesn’t require the actual FileName, so assuming your “Processed” folder is always going to be in the same place, you just fill in your fully qualified UNC path in the value slot on the Variables tab.
19. Choose your method (Move File, Copy File, etc.) and click “Ok”.