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:

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

TestSSISPerf_parameter

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.

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:

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

TestSSISPerf_controllflow

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.

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.

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:

The result:

TestSSISPerf_chart

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.

Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He’s also a speaker at various conferences.

2 thoughts to “Reblog: SSIS Performance Testing”

Leave a Reply

Your email address will not be published. Required fields are marked *