Automating UK Bank Holiday Data Population in SQL Server Database using PowerShell

 

In today's blog post, we'll explore how to automate the population of UK holiday data into a SQL Server database using PowerShell. We'll walk through the code and discuss how it retrieves holiday information from a JSON source (www.gov.uk) and inserts it into a SQL Server table. By leveraging PowerShell, you can simplify the process of keeping your holiday data up-to-date and ensure accurate records in your database.

Create a table for storing holiday information.

 

Copied!

USE [Holiday]
GO

CREATE TABLE [dbo].[Holiday](
	[Division] [varchar](20) NULL,
	[Title] [varchar](max) NULL,
	[Date] [date] NULL,
	[Notes] [varchar](max) NULL
) 
GO

Below is the PowerShell code that fetches holiday data from a JSON source and inserts it into a SQL Server table which created above:

Copied!

# https://www.dbascrolls.com 

# This script is designed to retrieve JSON data from a URL and insert it into a SQL Server database table.

# Define the connection details for the SQL Server, including the server name, database name, and table name.
$serverName = "localhost" 
$databaseName = "Holiday"
$tableName = "Holiday"

# The connection string is then created using the server and database details.
$connectionString = "Server=$serverName;Database=$databaseName;Integrated Security=True"

# The script then retrieves JSON data from a URL using Invoke-RestMethod cmdlet.
$url = "https://www.gov.uk/bank-holidays.json"

# Retrieve the JSON data
$jsonData = Invoke-RestMethod -Uri $url

# input year
$currentYear = (Get-Date).Year + 1

# Create a SQL connection
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$connection.Open()

# Prepare the SQL insert statement
$insertQuery = "INSERT INTO $tableName (Division,Title, [Date], Notes) VALUES (@Division,@Title, @Date, @Notes)"

# Create a SQL command object
$command = $connection.CreateCommand()
$command.CommandText = $insertQuery

# Add parameters to the SQL command
$command.Parameters.Add("@Division", [System.Data.SqlDbType]::VarChar)
$command.Parameters.Add("@Title", [System.Data.SqlDbType]::VarChar)
$command.Parameters.Add("@Date", [System.Data.SqlDbType]::Date)
$command.Parameters.Add("@Notes", [System.Data.SqlDbType]::VarChar)

Foreach ($Division in ($jsonData | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty Name)) {
    $englandHolidays = $jsonData.$Division.events


    # Filter the events for the current year
    $currentYearHolidays = $englandHolidays | Where-Object { $_.date -like "*$currentYear*" }

    # Loop through the holidays and insert them into the SQL table
    foreach ($holiday in $currentYearHolidays) {

        # It extracts title, date and notes information from each holiday event
        $title = $holiday.title
        $date = Get-Date $holiday.date -Format "yyyy-MM-dd"
        $notes = $holiday.notes

        # Set parameter values
        $command.Parameters["@Division"].Value = $Division
        $command.Parameters["@Title"].Value = $title
        $command.Parameters["@Date"].Value = $date
        $command.Parameters["@Notes"].Value = $notes

        # Execute the SQL command
        $command.ExecuteNonQuery()

    }

}

$connection.Close()

Create a SQL job run the PowerShell script above in the manner described below. The script above automatically updates the current year + 1 holiday list. 

Automating the population of holiday data in a SQL Server database using PowerShell can significantly simplify the process. By incorporating the provided code into your workflow, you can ensure accurate and up-to-date holiday records within your database. This automation saves time and effort by eliminating manual data entry and ensures that your applications and reports reflect the latest holiday information.

Remember to customize the code according to your specific environment and requirements.