Join me For Expert SSIS Training!

I’m honored to announce Expert SSIS – a course from Enterprise Data & Analytics!

The next delivery is 01-02 Apr 2019, 9:00 AM – 4:30 PM ET.

Data integration is the foundation of data science, business intelligence, and enterprise data warehousing. This instructor-led training class is specifically designed for SQL Server Integration Services (SSIS) professionals responsible for developing, deploying, and managing data integration at enterprise-scale.

You will learn to improve data integration with SSIS by:

  • Building faster data integration.
  • Making data integration execution more manageable.
  • Building data integration faster.


  1. SSIS Design Patterns for Performance – how to build SSIS packages that execute and load data faster by tuning SSIS data flows and implementing performance patterns.
  2. SSIS Deployment, Configuration, Execution, and Monitoring – the “Ops” part of DevOps with SSIS using out-of-the-box tools and the latest utilities.
  3. Automation – how to use Business Intelligence Markup Language (Biml) to improve SSIS quality and reduce development time.

I hope to see you there!

PS – Want to Learn More About Azure Data Factory?

Follow Andy Leonard’s SSIS Training page for more information.

PASS Summit 2018 Starts in 10 Weeks!

Can you believe the PASS Summit 2018 begins only 10 weeks from today (27 Aug 2018)? I confess, this is sneaking up on me fast!

I will be there. Will you?

Where Can You Find Andy at the PASS Summit 2018?


Monday 5 Nov 2018, I’m delivering a full-day pre-conference session titled Intelligent Data Integration with SSIS. I’m going to cover  everything listed at that link but there is an update about my precon content:

There will be Azure Data Factory content and demos!

Why this addition? Two reasons:

  1. My presentation titled Faster SSIS was selected. I usually include the three Faster SSIS demos in my precon. This time, you can just view the Faster SSIS session to see those demos.
  2. may have something cool and new to share about Azure Data Factory that is currently under NDA! Stay tuned…

Enterprise Data & Analytics is Exhibiting!

That’s right, you can find me in the Exhibition Hall! Enterprise Data & Analytics is exhibiting at the PASS Summit 2018!

Have an SSIS or Biml or ADF question? Stop by our booth!
Want to grab a selfie with me or Nick? Stop by our booth!
Want me to autograph your book? Stop by our booth!
Need some consulting or training help? Stop by our booth!

I’m so excited about this – I can hardly wait. We’ll have more information about specific dates and times when I will be manning the booth in coming weeks.

Presenting Faster SSIS

At the time of this writing, the session schedule has not yet been published. PASS has published a general schedule. Keep checking for details!


I am looking forward to the PASS Summit 2018. I hope to see you there.


100 Dumb Little Things

Software development is hard. It takes time, yes. But more than that, software development takes patience and thought and blood and sweat and love and tears.

My friends at Varigence recently released an update to their Business Intelligence Markup Language (Biml) products. If you’re into business intelligence or data science, integration, or engineering, you should check out Biml.

The release took longer than some would have liked.
Varigence didn’t provide regular updates on progress.
Some became… antsy.

I understand. Really, I do. As a BimlHero I get just a little more access behind the curtain compared to the average bear. Would I like to know more? Yep. Does it bother me when I don’t hear more? Nope. Why?

Software Development is Hard

I know how difficult it is to develop software because I decided back in the early 20-teens that I wanted to develop some software. (And I did it! Check out DILM Suite!) In the early 20-teens, I encountered… resistance… to the idea. Make no mistake, the resistance was well-founded and may ultimately prove to have been correct. But resistance didn’t do anything to curb my beliefs that:

  1. Software should always participate in a lifecycle that is managed, preferably by a process akin to DevOps;
  2. All software is tested. Some intentionally; and
  3. SSIS development is software development.

SSIS Rocks

The SSIS team at Microsoft has given us some incredible out-of-the-box functionality. I love the SSIS Catalog! It’s a great enterprise framework for managing data engineering execution, logging, and externalization (configuration). I believe that strongly-enough to have included similar statements in my last book: Data Integration Life Cycle Management with SSIS:

I can hear you thinking, “If you’re convinced the SSIS Catalog is so awesome, Andy, why did you build DILM Suite?” That’s a fair question. I actually answer this question in the book in chapter 6 titled Catalog Browser. You don’t have to buy the book to learn my answer; I published Chapter 6 here on this blog in a post with the obscure title, Why I Built DILM Suite, by Andy Leonard.

Was I Right?

I don’t know.
Time will tell.

There have been thousands of downloads since I built DILM Suite. I view the number of downloads as indicative of interest. Does everyone who downloads a product – especially a free product – use that product? Goodness no. Does everyone who uses SSIS or the SSIS Catalog need to download DILM Suite components? Goodness no.

If you’re trying to practice lifecycle management (or DevOps) with SSIS, though, DILM Suite can help.

100 Dumb Little Things

Software development is a lot like being a parent in that it consists of getting 100 dumb little things right. Are the dumb little things important? Some are, some are not, and some are vital. Does anyone get all 100 dumb little things right in parenting? in software development? No and no.

At the end of the day, every day in fact, I am extremely proud of what I’ve built.

SSIS Catalog Compare is the first product I’ve ever attempted to develop. Perhaps that shows. My competition certainly thinks so and has made much hay out of this fact. Do I shy away from telling folks because my competition uses it against me?

Nope. At the end of the day, every day in fact, I am extremely proud of what I’ve built. I get regular feedback from customers sharing how much the product helps them manage SSIS in their enterprise. The feedback greatly overshadows the… statements… of the competition. (Sidebar: I sometimes wonder how my competition sleeps at night…)

Getting software right is all about getting everything right including the best wording for feedback and error messages (like that shown at the top of this post).
Getting everything right is almost impossible, and certainly cost-prohibitive, but it should absolutely be the goal of any software development endeavor.

Getting 100 dumb little things right is my goal.


How I Build an SSIS Test Virtual Machine

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?”


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: