I’m currently building a lot of SSIS packages that are primarily date driven. Many of these involve periods of 100 days, to several years, so I wanted to automate the testing of these packages. I’d previously automated the testing of stored procedures over date ranges but wanted a solution for testing the system as a whole. The solution I came up with involves the use of Powershell. Essentially this script increments the date, by one day, before executing a package with dtexec.

First lets create a simple SSIS package that the Powershell script will call. Open BIDS and create a new project. Add a string variable to the project called sql_date like below.

sql_date variable

We’re going to use this variable to get the date as SQL Server recognises it so we know it’s the same as the system date. During my testing I discovered that SQL Server would take a few seconds pickup a system date change. Obviously this could distort your results so we need to ensure these are the same.

Next add an Execute SQL Task, from the toolbox, onto the designer. Call it “Get SQL Server Date”. Add a connection to the SQL Server your package will be executing against and change the ResultSet property to “Single Row”. Add the following TSQL to SQLStatement.

SELECT CONVERT(VARCHAR, GETDATE(), 103);

execute_sql_task_date

Next add a Script Task onto the designer and call it MsgBoxDate. Right click the task, click edit, add sql_date to ReadOnlyVariables on the script tab.

script_task

Click “Design Script” and add the below code into the ide.

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

Public Class ScriptMain

    Public Sub Main()
        Dim sql_date As String = Dts.Variables("sql_date").Value.ToString
        Dim msg As String = "SQL Server Date = " & sql_date & Environment.NewLine & "System Date = " & Format(Date.Now, "dd/MM/yyyy")
        MsgBox(msg)
        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class

Connect the tasks together with a precedence constraint. The package should look something like below.

date_package

Return to the SSIS Designer, click File > Save Copy of Package.dtsx As. Save this package in the file system and remember the path as you’ll need it later. Below is the Powershell script that will change the current day, by one day, and then execute the package we just made. The while loop will verify that SQL Server has picked up the system date change before executing the package. Change the settings for $sqlserver and the package path for dtexec as appropriate. The $days variable should be changed to the number of days you want to run the package for. In this example it’s just ten. The script will set your systems date back once it has successfully completed..

# SQL server and database settings
$sqlserver = "localhost\sql2005";
$database = "master";
# Record the current date so we can set it back at the end of the script
# You may need to change this depending on your regional settings
$date = Get-Date -Format "dd/MM/yyyy";

# Connection string used for verifying sql server has registered the date change
$connection_string = ("Data Source=$sqlserver;Initial Catalog=$database;Integrated Security=SSPI")
$days = 10;

# Setup the database connection
$conn = New-Object System.Data.SqlClient.SqlConnection($connection_string);
$conn.Open();
$cmd = $conn.CreateCommand()
$cmd.CommandText = "SELECT DATEPART(dd, GETDATE())";

for($i = 0; $i -lt $days; $i++)
{
	# Change the date by one day
	Set-Date (Get-Date).AddDays(1);
	# SQL Server can take a few seconds to register the change
	# so we need to loop until the day matches before
	# executing our ssis package
	$reader = $cmd.ExecuteReader();
	$reader.Read();
	$sql_day = $reader.GetValue(0);
	$reader.Close();
	while(($sql_day) -ne [DateTime]::Now.Day)
	{
		Start-Sleep -Seconds 2;
		# Check the sql day again
		$reader = $cmd.ExecuteReader();
		$reader.Read();
		$sql_day = $reader.GetValue(0);
		$reader.Close();
	}
	# Execute the package for this date
	dtexec /f "C:\Users\Rhys\Desktop\Package.dtsx"
}

# Clean up
$reader.Close();
$conn.Close();

# Put the date back with the current time
$time = [DateTime]::Now.TimeOfDay;
Set-Date "$date $time";

The script will work through each day displaying a message box for each iteration.

start_date

end_date

Obviously this particular package needs human intervention to get it to complete. Your real-life packages wouldn’t have this limitation so this script could be used to test over a large date range. Hopefully this script will allow me to get out of the office a bit earlier when I need to test my packages!