Reblog: SSIS Performance Testing

I had to do some performance testing for an MSSQLTips article and I thought I’d share the framework I used in a blog post.

First of all we have to log start and end dates of the package to a table so we can easily calculate the duration a package took to finish. This can probably be calculated from the SSIS catalog as well, but I was a bit too busy lazy to find out how to do this. Anyway, the logging table is created using the following statement:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PackageLogging]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[PackageLogging](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [RunID] [int] NOT NULL,
        [PackageName] [varchar](50) NOT NULL,
        [StartDate] [datetime2](7) NOT NULL,
        [EndDate] [datetime2](7) NULL
    );
END
GO

The RunID column is populated by a package parameter; I will come back to this later on.

The package starts with an Execute SQL Task to log the start. I use the following SSIS expression to construct the SQL statement on the fly, allowing you to easy copy-paste the logging task between packages.

“INSERT INTO dbo.PackageLogging(RunID, PackageName,StartDate) VALUES (” +  (DT_STR,10,1252)@[$Package::RunID] + “,’” +  @[System::PackageName] + “‘,SYSDATETIME());”

At the end of the control flow, there is an Execute SQL Task that updates the EndDate of the previously inserted row. The expression looks like this:

“UPDATE dbo.PackageLogging SET [EndDate] = SYSDATETIME() WHERE RunID = ” + (DT_STR,10,1252)  @[$Package::RunID] + ” AND PackageName = ‘” +  @[System::PackageName] + “‘;”

The RunID parameter is important to link those two Execute SQL Tasks together. A typical control flow looks like this:

Logging is the first step, now we have to run the package of course. I created a stored procedure that allows me to easily start a package in the SSIS catalog.

CREATE PROC [dbo].[RunPackage]
    (@RunID         INT
    ,@PackageName   VARCHAR(50)
    ,@FolderName    VARCHAR(50)
    ,@ProjectName   VARCHAR(50)
    ,@Synchronized  BIT = 1 -- run synchronously by default
    )
AS
DECLARE @execution_id BIGINT;
 
EXEC [SSISDB].[catalog].[create_execution]
     @package_name      = @PackageName
    ,@execution_id      = @execution_id OUTPUT
    ,@folder_name       = @FolderName
    ,@project_name      = @ProjectName
    ,@use32bitruntime   = False
    ,@reference_id      = NULL;
 
--SELECT @execution_id;
 
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
     @execution_id
    ,@object_type       = 30
    ,@parameter_name    = N'RunID'
    ,@parameter_value   = @RunID;
 
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
     @execution_id
    ,@object_type       = 50
    ,@parameter_name    = N'SYNCHRONIZED'
    ,@parameter_value   = @Synchronized;
 
EXEC [SSISDB].[catalog].[start_execution] @execution_id;
 
GO

The proc passes the @RunID parameter to the package, as well as other usual suspects, such as the package name, folder name and project name. You can also choose if a package is run synchronously or asynchronously. When run synchronously, the stored procedure doesn’t finish until the package is finished as well.

Using this stored procedure, it is easy to run a package multiple times in a row using a WHILE loop.

DECLARE @RunID INT = 1;
 
WHILE (@RunID <= 10)
BEGIN
    EXEC dbo.RunPackage @RunID, 'myPackage.dtsx', 'myFolder', 'myProject', 1;
    SET @RunID += 1;
END

The package is run synchronously, so that multiple instances do not run at the same time. This eliminates resource contention and gives us a clearer result of the performance of the individual package.

Using the following query, it is easy to build a nice chart in SSRS:

SELECT
     [RunID]
    ,[PackageName]
    ,[Duration] = DATEDIFF(MILLISECOND,StartDate,EndDate) / 1000.0
    ,[Mean] = PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY DATEDIFF(MILLISECOND,StartDate,EndDate) / 1000.0)
                    OVER (PARTITION BY PackageName)
FROM [dbo].[PackageLogging]
ORDER BY ID;

The result:

I used RunID as category, PackageName as the series and the Duration/Mean as the values. I created a custom palette where I forced the Duration measure and the Mean to have the same color.