Tuesday, June 19, 2012

SQL 2012 Upgrade Part 4: SSIS Testing

Read on to see how we planned to test our SSIS packages and components and what really happened.


How it was Supposed to Happen

Step 1: Figure out which packages to use for testing.

This can be a little bit tricky. We tend to be very granular in our ETL processes. Each SSIS package typically does one, and only one, thing. As a result, we have over 1,000 packages. Choosing just a few took a little leg work and a lot of conversations via email and in person. The goal was to come up with the smallest possible list of packages that covers the third-party components that we have in use.

The tricky part was sifting through the proverbial haystack of SSIS packages to find the few that would meet our needs. It took a little while to come up with a way to query the list of SSIS packages. Microsoft made it difficult to query the XML of the package by not storing it as XML. The query below shows an example of how to search the packages contents; it's slow, but it works.

WITH PackageList
AS (
  SELECT f.foldername AS FolderName,
         p.[name] AS PackageName
         ,CONVERT(xml,CONVERT(varbinary(max),PackageData)) AS PackageXmlData
    FROM [dbo].[sysssispackages] AS p
         JOIN dbo.sysssispackagefolders AS f
              ON f.folderid = p.folderid
)
SELECT FolderName,
       PackageName
  FROM PackageList
 WHERE PackageXmlData.exist('//property[contains(.,"DotNet.FullName")]') = 1


Step 2: Test a few packages on the cloned SSIS server.


OK, we've identified a handful of packages that we can use to test against. Testing them on the cloned servers is as easy as updating the configuration files and executing them one-by-one. This step assures us that the cloning process didn't break anything.

 

Step 3: Upgrade the third-party SSIS components and test again.

I thought it wouldn't hurt if we upgraded the third-party components in advance of the SQL Server upgrade. Another quick test of the SSIS packages after installing the new versions of the components will tell us if this will work or not.

 

Step 4: Upgrade SQL Server and see if the same packages still run.

Here's this big step. Following the SQL Server upgrade we need to make sure our packages and the third-party components still work.

Simple enough plan.

 

What Really Went Down

The way things actually worked out wasn't bad or wrong but it sure was confusing at first. It turns out that the SQL Server 2012 upgrade process doesn't remove the 2008 R2 instance of SSIS; instead, it installs the SQL 2012 instance side-by-side with the 2008 R2 instance.

My post-upgrade testing went fine, at least I thought it did. Now, to be honest I don't know exactly what I did. I think I ran the packages from SQL 2012 Management Studio but it's entirely possible that I ran them from the SQL 2008 R2 version. This is an important distinction because the SSIS instance you connect to depends on the version of SSMS that you're using. Running the packages from SSMS 2008 R2 runs the packages on the 2008 R2 instance and vice versa.

The important outcome of this whole lesson was figuring out that we end up with two instances of SSIS to work with. Which brings us to a rather abbreviated conclusion.

 

SSIS Testing End-Game

In the end, we finally decided not to upgrade the Integration Services packages and continue to run them on the SQL 2008 R2 instance. This afforded us several benefits.

One area it helped us out was with our job scheduler. We use a cross-platform job management system which allows us to coordinate jobs across all servers in the Enterprise regardless of the OS they are running on. The down side is that we have a job script for every package and, if we're going to run the packages on the SSIS 2012 instance, we'll have to update each and every one of them. Not having to update those scripts was really nice.

The best thing about having both versions of SSIS was that we can migrate to SQL Server 2012 gradually and without all the baggage of the "old-style" SSIS projects. We will be migrating our packages one project at a time and bringing them in line with our overall SQL Server 2012 strategy.

The end result of the SSIS testing certainly wasn't what we expected but it did turn out well. The upgrade plan changed significantly for the better as a result of what we found. Had we known that we weren't going to upgrade the SSIS packages as part of the upgrade we would still have needed to upgrade the components and test them accordingly. I guess a little confusion goes a long way.


How did your SSIS testing go? Tell us about your plans and how they worked out in the comments.

No comments:

Post a Comment