I use VirtualBox for my hypervisor engine. I started using it years ago when it was the first free hypervisor I found that supported 64-bit guests. In this post I share some thoughts about testing SSIS using a test virtual machine (VM).

There are other awesome hypervisor platforms out there. In setting up a virtual machine (VM) for SSIS testing, I do nothing specific to VirtualBox. You can use Hyper-V or VMWare or AWS or Azure or some other platform.

What you use is not nearly as important as using it.

After creating a virtual machine and installing an operating system, I set up test instances of SQL Server:

Which instances? I set up Dev, Test, QA, and Prod.

A Note About Dev

“SSIS developers require sysadmin or near-sysadmin permissions in the Data Integration Development environment.”
– Andy Leonard, circa 2009

Every time I make that statement in a presentation or class I see the DBAs in the room react. Usually they cringe. Bold DBAs respond with questions – legitimate questions such as, “What if the developers destroy the Data Integration Development environment?” “What if they do something wrong”? “What if they do something stupid?”

These are legitimate questions that every DBA should be asking.

The answer – as uncomfortable as it is – is: “The DBA should fix it for them.” Why? Because…

SSIS Development is Software Development

Enough said.

Code Promotion

Developers should not deploy their own code. This is a best practice for a good reason. SSIS developers should package SSIS projects for deployment and hand off said packaging to DBAs or a Release Management Team for deployment – especially to Production.

This the first reason my SSIS Test VM includes four tiers: Dev, Test, QA, and Prod.

“Not the First…”

Ideally we want the person deploying SSIS to Production to practice the deployment prior to deploying to Production. A DBA or Release Team person deploying SSIS to Production without practice is a recipe for rollback. The Pareto Principle applies and adding just a single deployment test increases the efficiency of the deployment from 80% to 96% (80% added to the original 80%).

“Not the First…” (Not a Copy/Paste Error)

The SSIS developer needs to practice deployment using the deployment package. I just checked my FitBit and it’s 2018. We are well beyond the time of “It works on my machine” being an acceptable excuse. It needs to work on the customer’s machine.

“Who’s our customer, Andy?”

“The business.”

As an SSIS developer, you should also be testing deployments. This is the reason there’s more than a single instance – a single tier – on my SSIS test VM. It’s why your SSIS test VM should also have more than one tier and why you need to be a sysadmin in your Dev tier.

If you are not free to fail you are also not free to succeed.

All software is tested. Some intentionally.

Failure is going to happen. Is it going to be you failing? Or will it be your customer or the business?

Test. Create the deployment package. Deploy it. Then test the deployment. Fix any errors. Then test the deployment again. Then fix any errors again. Then test the deployment again. Then test the deployment again. (Again, not a typo…)

“Is It Possible to Test Too Much, Andy?”

No.

SSIS Catalog Compare is designed to assist DBAs and Release Management Teams with SSIS code promotion. Check out this short (1:20) to learn more:

:{>