SSIS: Don't run the process on a bank Holiday

Sadly, as people don’t make sense, we have to make compromises in our systems and processes. I recently had a requirement, in an SSIS package, to be able to identify which days were Bank Holidays and take a different course of action, e.g. not run the main process. Here’s an illustration of the approach I took using a simple lookup table, containing holiday dates, and SSIS precedence constraints.

The TSQL (SQL Server 2008) below will create a table called BankHolidays and populate the table with public holidays up to the end of 2010. The Bank Holiday dates have been taken from the DirectGov site and are for England & Wales only. Depending on your requirements you may want to consider dates, like Christmas, that fall on a weekend. These technically aren’t Bank Holidays so haven’t been included here, but you may wish to add them if you don’t want to run on these days.

-- Create a table to contain Bank Holidays
CREATE TABLE dbo.BankHolidays
(
	BankHoliday DATE NOT NULL PRIMARY KEY CLUSTERED,
	ActiVe BIT NOT NULL DEFAULT 1
);
GO

INSERT INTO dbo.BankHolidays
(
	BankHoliday
)
VALUES
(
	'2009-12-25'
),
(
	'2009-12-28'
),
(
	'2010-01-01'
),
(
	'2010-04-02'
),
(
	'2010-04-05'
),
(
	'2010-05-03'
),
(
	'2010-05-31'
),
(
	'2010-08-30'
),
(
	'2010-12-27'
),
(
	'2010-12-28'
);
GO

Note the Active flag in the table. This is just in case we need to deactivate a Bank Holiday for some some reason. Added flexibility is always useful.

Launch BIDS and create a new SSIS project. The first thing we need to do is add a variable called BankHoliday. We will use this in determining whether the day is a Bank Holiday or not. Add an Int32 variable in the SSIS variables window, as illustrated below.

Add an OLE DB connection that points at the database containing the BankHolidays table. Drop an Execute SQL task from the toolbox onto the design canvas. Right click the task and click edit to configure the task properties. Add the OLE DB connection manager to Connection and the below TSQL to SQLStatement.

SELECT COUNT(*)
FROM dbo.BankHolidays
WHERE BankHoliday = CONVERT(DATE, GETDATE())
AND Active = 1;

ResultSet should be changed to “Single row”.

UPDATE: (Thanks to Dr Drew in the comments for pointing this omission out.)

Click on the Result Set tab and map the variable BankHoliday as shown below. This variable mapping will be used to determine if the execution date is a bank holiday.

Now drop two Script Task components onto the design canvas and connect them from the Execute SQL Task. Call one “Display “Not Bank Holiday” message” and the other “Display “It’s a Bank Holiday” message”. This should look something like this.

Next we need to edit the constraints connecting our Script tasks to implement the logic to determine if it is a Bank Holiday or not. Right click the constraint connecting the script task called “Display “Not Bank Holiday” message” and choose Edit. Change “Evaluation Operation” to ‘Expression and Constraint’ and enter the following expression into the appropriate text box; @BankHoliday == 0. Finally click the radio button labelled “Logical OR”. This should look like this…

Click OK to save your changes. Now we need to edit the constraint connected to the Script Task called “Display “It’s a Bank Holiday” message”. The setup here is exactly the same except for the Expression which should be entered as @BankHoliday == 1.

By now your design canvas should look something like below.

Finally we’re just going to add a little code to each script task to display a message box. In the script task called “Display “Not Bank Holiday” message” click edit, go to the script tab, and click the “Design Script button”. Add the below code.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

    Public Sub Main()
        MsgBox("Hi, today is not a Bank Holiday, get back to work!")
        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class

In the script task called “Display “It’s a Bank Holiday” message” add the below code.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

	Public Sub Main()
        MsgBox("Hi, today is a Bank Holiday, put your feet up!")
		Dts.TaskResult = Dts.Results.Success
	End Sub

End Class

Now we should be ready to execute the package. Click run and you should see the following message (assuming it’s not a bank holiday!).

Now, either wait until a bank holiday occurs, or change the date on your computer to one contained in the BankHolidays table. I changed mine to 2009-12-28 and here’s what I saw when I executed the package.