THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions.

  • Announcing SSIS Catalog Compare and CatCompare Version 2

    CatCompareI am pleased to announce the release of SSIS Catalog Compare and CatCompare version 2. You can purchase a bundle that includes both products (and saves you some money) or can purchase each product separately: SSIS Catalog Compare and CatCompare.

    If you’re interested in learning more about the functionality provided by the products you can download (or view) the documentation for free.

    A Couple CatCompare Examples

    Two of my favorite chunks of functionality are the CatCompare commands: Deploy Folder Differences and Deploy Catalog Differences.

    Deploy Folder Differences

    Deploy Folder Differences can be executed after you load a couple Catalogs into the CatalogBase (an object I built to model the SSIS Catalog) server objects. They must be compared to detect the differences, and then the differences in one Catalog Folder in a Catalog instance can be overwritten by the version in the other Catalog’s Folder. The following command line accomplishes that, redirects the output to a text file, and then exits:

    "C:\Program Files\DILMSuite\SSISCatalogCompare\CatCompare.exe" "-server0;vmSql16\Dev" "-server1;vmSql16\Test" "-comp" "-deployfolderdiffs;\vmSql16_Dev\IS\SSISDB\Folders\Demo\|0|\vmSql16_Test\IS\SSISDB\Folders\Demo>E:\Test\CatCompare_DeployFolderDiffs_Results.txt" "-exit"

    The command line above loads the Catalogs hosted on the vmSql16\Dev and vmSql16\Test SQL Server instances. The catalogs are compared, and then the differences identified by the compare operation between the Demo folder in each Catalog are deployed from the vmSql16\Dev Demo folder to the vmSql16\Test Demo folder.

    Why do I like this command? I can run it every night to collect the changes a data integration developer deployed to her Dev Catalog, deploying the updates (only) to an integration SSIS Catalog instance. You know, just like the C# developers when they practice DevOps.

    Deploy Catalog Differences

    The Deploy Catalog Differences command will help keep two Catalogs in sync:

    "C:\Program Files\DILMSuite\SSISCatalogCompare\CatCompare.exe" "-server0;vmSql16\Dev" "-server1;vmSql16\Test" "-comp" "-deploycatalogdiffs;0>E:\Test\CatCompare_DeployCatalogDiffs_Results.txt" "-exit"

    As before, two Catalogs are loaded and compared. Things that are different in server0 (vmSql16\Dev) are deployed to server1 (vmSql16\Test). The results are redirected to a file and the utility exits.

    Why do I like this command? I can use it to keep a “warm copy” of the Test SSIS Catalog somewhere else in the enterprise. This is handy if two (or more) data integration teams are developing updates to coupled projects.


    My long-term goal with DILM Suite products is to facilitate DevOps and Data Integration Lifecycle Management with SSIS.

    Kent Bradshaw and I will be demonstrating SSIS Catalog Compare, CatCompare, and more DILM Suite utilities in the upcoming (free!) webinars: SSIS Catalog Management (10 Jan) and Advanced SSIS Execution (24 Jan).


    Learn More:
    SSIS Catalog Management – 10 Jan 2017
    Advanced SSIS Execution – 24 Jan 2017

    Related Training:
    SSIS Lifecycle Management (free recording, registration required) 
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago

  • Save Time and Improve SSIS Quality with Biml, A Free Webinar

    Join me at 1:00 PM EST 31 Jan 2017 for Save Time and Improve SSIS Quality with Biml, another free webinar from Enterprise Data & Analytics!

    “Save Time and Improve SSIS Quality with Biml” introduces Business Intelligence Markup Language (Biml) to SSIS developers, DBAs, sysadmins, and others who want to learn more about automating SSIS development.

    Automation (or code generation) improves the quality of SSIS solutions while saving time. With Biml, you can build lots of SSIS packages faster than building them manually. Because Biml generates SSIS packages from a template human error is mitigated. Therefore, the quality of the SSIS packages improves.

    I look forward to seeing you there.

    Register today!


    Learn More:
    Biml Academy

    Related Training:
    From Zero to Biml - 19-22 Jun 2017, London
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago

  • Presenting “Using Biml as an SSIS Design Patterns Engine” at the (605) SQL Server User Group 10 Jan

    I am honored to present – albeit remotely – Using Biml as an SSIS Design Patterns Engine to the (605) SQL Server User Group in Sioux Falls, SD Tuesday 10 Jan 2017!

    Perhaps you’ve heard the buzz about Business Intelligence Markup Language (Biml) and wondered, “Why is everyone so excited about Biml?” Occasionally, a new technology emerges that changes everything. For SSIS developers, Biml is one such technology. Business Intelligence Markup Language (Biml) is a powerful solution for rapidly creating SSIS packages and solutions. SSIS Design Patterns support various load profiles. Combined, Biml and SSIS Design Patterns offer a compelling solution for automating enterprise data integration. In this session, you will learn: -How to build and execute your first Biml file! -How to design a single SSIS package using Biml -How to rapidly build multiple SSIS packages using Biml.


    You might like working with Enterprise Data & Analytics because we dig Biml.

    Learn More:
    Biml Academy 
    The Basics of Biml – the Execute SQL Task
    The Basic of Biml – Populating the Biml Relational Hierarchy

    Related Training:
    From Zero to Biml - 19-22 Jun 2017, London
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago

  • Free Webinar–Advanced SSIS Execution–24 Jan 2017

    Join Enterprise Data & Analytics for Advanced SSIS Execution, a free webinar starting at 1:00 PM EST 24 Jan 2017!

    Kent and I demonstrated how to use the SSIS Catalog in the SSIS Academy series (see links to the recordings in the Learn More section). In this webinar, we share several ways to execute SSIS packages.

    Register today!


    You might like working with Enterprise Data & Analytics because we love the SSIS Catalog.

    Learn More:
    SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy
    SSIS Academy: Using the SSIS Catalog Day 2 - Package Execution and Monitoring
    SSIS Academy: Using the SSIS Catalog Day 3 - SSIS Configuration

    Related Training:
    SSIS Lifecycle Management (free recording, registration required) 
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

  • Free Webinar–SSIS Catalog Management–10 Jan 2017

    Join Enterprise Data & Analytics for SSIS Catalog Management, a free webinar starting at 1:00 PM EST 10 Jan 2017!

    Kent and I demonstrated how to use the SSIS Catalog in the SSIS Academy series (see links to the recordings in the Learn More section). In this webinar, we share best practices, tips, and tools for using the SSIS Catalog in the real world.

    Each attendee will receive a free bit set-able to the value of their choice – available in four states. Seating is limited so register today!


    You might like working with Enterprise Data & Analytics because we love the SSIS Catalog.

    Learn More:
    SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy
    SSIS Academy: Using the SSIS Catalog Day 2 - Package Execution and Monitoring
    SSIS Academy: Using the SSIS Catalog Day 3 - SSIS Configuration

    Related Training:
    SSIS Lifecycle Management (free recording, registration required) 
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

  • An Example of Data Integration Lifecycle Management with SSIS, Part 0

    What is Data Integration Lifecycle Management (DILM)?

    Here’s one way to think about DILM:

    Data Integration Lifecycle Management (DILM) is applying software Application Lifecycle Management (ALM) best practices to Data Integration development and operations (DevOps), version control, release management, and configuration.

    I can hear you thinking, “But Andy, why would we apply software best practices to a data integration platform like SQL Server Integration Services (SSIS)?” I’m glad you asked. The answer is: “Because SSIS development is software development.”

    SQL Server Integration Services suffers from having the name of a popular relational database engine – SQL Server – baked into its name. Don’t let that throw you, SSIS development is software development. Yes, SSIS packages are tightly-coupled to data sources and destinations, and to the data the packages move.

    I Object!

    I hear from many people who wear several hats in small shops. Many of them object to my thoughts about DILM for SSIS. If you are one of those people who wear many hats and object to my thinking about DILM, I want to say two things to you:

    1. You ROCK! Wearing many hats is hard. I’ve been there, done that, and have the blood-, sweat-, and tear-stained t-shirt.
    2. Do whatever works for you.
    3. BONUS 3rd thing: You may find some benefit from DILM practices if you give them a shot.

    Who Needs to Practice DILM?

    It depends on  the problem(s) you are trying to solve. Here are a few questions to help you determine your need for implementing DILM:

    • Have you ever lost code?
    • Has a server ever crashed and part of the solution involved re-developing code?
    • Have you ever received a phone call from work while on vacation?

    If you answered, “Yes,” to any of these questions, implementing some flavor of DILM may help.

    This Series

    I don’t have all the answers, so I cannot possibly provide all the answers to you. What I can do, though, is share some things I’ve learned implementing SSIS solutions for the past decade. I’ve led a team of 40 ETL developers building multiple enterprise-class projects simultaneously. I’ve parachuted into enterprises on fire as a lone wolf consultant and helped douse the flames. I've joined teams and formed teams to solve enterprise data integration problems. This breadth of experience has taught me priorities that are different from the priorities of some of my compatriot SSIS professionals.

    You may read some of my thoughts and think, “That’s overkill.” To which I will respond, “Yeamaybe.” I understand. Really I do. As I wrote to the small-shop-people, do what works for you.

    I want to tell the story of a data integration project, to follow it through its lifecycle as it starts, matures, and grows. Although the project is interesting, we will focus on lifecycle. I hope everyone finds some value in this series for that is my goal.


  • Three Lessons Learned in 2016

    Some folks do not like to read posts of a more personal nature or posts that contain religious references. If you are one of those people, you have been forewarned.

    I’ve read lots of posts from people who are ready for 2016 to be over. I understand their reasons. Celebrities, friends, and family have passed away this year. The world economy stumbled through another four quarters. About 51% of US voters were disappointed by results of the 2016 US Presidential Election (which are still being updated at the time of this writing, by the way).

    I learned some things in 2016 and would like to join the chorus of sharing as 2016 draws to a close.

    New Businesses

    Isaiah 61:3

    In 2015 I left Linchpin People and founded Andy Leonard Consulting. Shortly thereafter I was having lunch with my friend Nick who said, “‘Andy Leonard Consulting’ sounds like a one-person operation.” “There’s a very good reason for that,” I replied. Before we finished lunch, Nick and I agreed to work together on a new venture called Enterprise Data & Analytics, or EDNA. EDNA officially launched in January 2016.

    I kept Andy Leonard Consulting around and repurposed it as a software development company. ALC built and manages the Data Integration Lifecycle Management Suite (or DILM Suite), a collection of mostly-free utilities that facilitate my vision of enterprise data integration DevOps and Continuous Integration (CI) for SSIS.

    If you’re playing along at home, I operate two businesses: Andy Leonard Consulting (ALC) and Enterprise Data & Analytics (EDNA). ALC has “consulting” in the name but does software, not consulting. EDNA does consulting. Confused? Me too…

    Lesson: Entropy happens. Without an anchor you’ll either drift – or be blown – away. My anchor is faith in Christ. I’m reminded of Isaiah 61:3 that states God gives “beauty for ashes.” Some translations render this passage “God makes beauty from ashes.” A chunk of my life burned down in 2015. I am astounded at, overwhelmed by, and thankful for what God is making from these ashes.

    Becoming a Better Listener

    On Being Wrong

    I started listening to audio books in 2016. I have listened to audio books in the past, but I focused on listening to them in 2016. I like Audible for a number of reasons:

    • I pay ~$15/month and get 1 credit that can be used to access one book.
    • I can accumulate 6 credits over 6 months if I do not use them, so if I go through a period of not accessing audio books I don’t start losing credits until the seventh month.
    • Audible’s cloud integration means I can listen on my laptop or phone, picking up right where I left off on the other device.

    My taste in audio books is different from my taste in books I read. I’ve tried to listen to books for entertainment but I cannot enjoy them. I don’t know why this is so. I enjoy listening to audio books about theology and business. While I enjoy reading books about theology and business and science fiction, I’ve mostly shifted to reading sci-fi and listening to theology and business books.

    I prefer audio books read by the authors. Why? To me, they’re more… real. The author knows where to inflect for emphasis. Sometimes the author will throw “extras” into the audio book that are not in the written edition. The best business book I heard in 2016 is an example: Grant Cardone injected dozens of thoughts into the audio book The 10X Rule that did not appear in the print version. All of them are great and some of them are hilarious! 10X is a book about success. I confess some confirmation bias in recommending this book; I’ve long believed and stated that there’s no substitute for hard work. I assigned this book to my older son, Stevie Ray, as a homeschool reading assignment. That’s how much I liked it.

    My friend and brother Frank La Vigne (blog | @tableteer) recommended this book – thanks Frank!

    Listening to audio books like 10X has improved my listening skills. It turns out that listening to the words that people who live with you and work with you are saying and writing to you is a good idea (who knew?). Listening is an especially good idea for me because I am a hard-headed and triflin’ redneck (Can I get an amen?). I mentioned confirmation bias earlier because I’m more guilty of it than most. Kathryn Schulz’ TED Talk On Being Wrong – and the book – smacked me upside the head. The TED Talk is just under 18 minutes. I encourage you to invest 18 minutes listening to her. She’s brilliant. Will it help if I link to her TED Talk again?

    Lesson: Listen. Listen more and listen well.

    There’s More to Learn


    I’m going to confess something. I’m going to be vulnerable (another great TED Talk by Dr. Brene Brown – 20 minutes – we’re up to 38 minutes of assigned TED Talks now…). SSIS Catalog Compare is the first product I’ve written. It’s also the first full application I’ve attempted writing in C#. I’ve been writing software since 1975 so I am familiar with the practice of developing code. Although I’ve built applications in the past, I’ve never built a soup-to-nuts product and I’ve never used C# to do so; I’ve only used C# to build code snippets.

    Catalog Compare grew out of an epiphany while attempting to write a GUI to manage an SSIS Framework. An SSIS Framework can simplify your enterprise data integration. You can start the execution of a collection of SSIS packages with a single command. Awesome, right? What’s not to love? Well… there’s no free lunch. In order to accomplish this magic, an SSIS Framework relies upon a boat load of metadata. SSIS execution, whether or not one uses a Framework, also relies on even more metadata stored in the SSIS Catalog. I was building this interface to manage Framework metadata when I realized there’s no easy way to manage most of the metadata stored in the SSIS Catalog.

    So I wrote an app for that. In C#.

    Why? I found a problem that I wanted to solve. And I wanted to learn C# – really learn it. So what did I do? I committed to building SSIS Catalog Compare in C#. I knew I would need help. Fortunately for me, I’m surrounded by awesome friends who are literally masters of software development and C#. Two friends in particular, Scott Currie (owner of Varigence, inventor of Biml, all-around nice guy) and Kevin Hazzard (blog | @KevinHazzard), listened to me and suggested improvements and next steps without laughing (to my face) at my code. With their help and help from Google and Pluralsight, I learned more C# in 2016 – enough to release SSIS Catalog Compare in August.

    Thank you, Scott and Kevin.

    Lesson: Keep learning.

    Post-note: As I type this, SSIS Catalog Compare v2 is nearing release. There’s a free “view-only” utility based on Catalog Compare functionality (it’s actually a subset of the Catalog Compare codebase) called SSIS Catalog Browser. After the Catalog Compare v2 release I return to developing that Framework Manager GUI I started coding in August 2015. There’s a “view-only” version of this yet-to-be-built application available today, for free. It’s called SSIS Framework Browser and it works with the SSIS Framework Community Edition which is not only free, it’s open source! </ShamelessPlug>


    2016 was a year of lessons learned for me. I am looking forward to 2017!

    Happy New Year!


  • My Top Blog Posts for 2016

    (with a nod to @KrispyKreme)

    I’d like to wish everyone reading this post a blessed, peace-filled, and happy 2017! I would also like to thank you for reading this post and any other posts you may have read.

    Some Blog Post Stats

    I published 87 blog posts this year at I published most in December (12) and least in October (2).

    These posts attracted more reads than the others (ordered by most reads in descending order):

    1. Installing SQL Server 2016 Developer Edition, One Example
    2. SQL Server 2016 Developer Edition is Free
    3. Announcing Biml Academy!
    4. PASS Board Elections–Voting is Open!
    5. SQL Server Developer Edition is FREE!
    6. Biml Academy 2 Webinar Recordings are Available!
    7. Microsoft is Listening
    8. A Couple-Three Thoughts and Questions About Swag at Community Events
    9. A New Version of SSDT is Available
    10. BI’s Not Dead

    Is This Accurate?

    But… Some of those posts have been around a lot longer than others. The oldest post in this list is 325 days old (at the time of this writing). The newest post is 85 days old, the average “age” of these posts is 210 and the median “age” is 215 days. 

    How does a Data Philosopher account for this? I computed the age of each post and then divided the read count by that number. The results now look like this:

    1. The Recordings for SSIS Academy: Using the SSIS Catalog are Available
    2. SQL Server vNext CTP 1.1 is Available!
    3. SQL Server Management Studio (SSMS) v16.5.1 Now Available
    4. Learn More About the SSIS Catalog
    5. Three Free Webinars About Using the SSIS Catalog
    6. An Interview With Me
    7. DLM (Database Lifecycle Management)
    8. SSIS Catalog Browser Update
    9. Broken References in the SSIS Catalog
    10. New Versions of SSMS and SSDT Available

    Is This Accurate?

    But… This second list is skewed towards the newer posts due to the lower denominator. The newest post is 2 days old (and it’s #1) while oldest post in this list was published 43 days ago (at the time of this writing). The median “age” of the posts in this list is 16 days and the average “age” is 18.


    I suspect both lists are valid because readership declines after the post scrolls off the front page at (which displays the latest 20 posts). I write “both lists are valid” because these results (like all results in data analytics) must be considered in context. Context is defined by answering the question, “What is the problem we are trying to solve?” There are other ways to analyze this data – lots of other questions we can ask of it. Data science is science and science (well, good science, anyway) involves experimentation.

    My theory: The first list contains the posts that experience some “longevity in appeal” (or SEO). The second list is “Hot Now.” Or not. I’d love to hear your thoughts.

    Happy New Year!


  • Predictions for 2017

    I’m hesitant to make predictions for 2017 because I’ve read the parts of the Old Testament that deal with prophets whose predictions did not come to pass. Perhaps a better title for this post is “Hopes for 2017” or “Thoughts for 2017”.

    The Cloud Will Grow

    (Filed under “DUH!”) 2016 saw a number of data services introduced and improved in the cloud. I hope the propagation of economies of scale accelerate in 2017; that the cost-savings will continue and continue to be passed onto the end-users. I heard some complaints in 2016 about the costs of some cloud-based services. While I concur that the costs of some services seem high with some bordering on extravagant and some crossing that border, I see pricing as an effective throttle while new services and offerings are maturing (see Supply and Demand).

    Automation Will Increase Operational Efficiency

    Automation allows one administrator or developer to do the work of many. One of the reasons the cloud will grow is automation. Having worked with computer technology for four decades, I’ve experienced firsthand the efficiency of automation. Automation includes tools that surface metadata and data collected by instrumentation. An entire industry selling support utilities exists and appears to be flourishing.

    This is a good thing.


    Consider the past: Hundreds of years ago many spent a significant portion of each day seeking food for that day. Contrast that with today (in first world countries), where we spend minutes each day seeking food. IN the US we spend more time actually eating than searching for food. Not having to search for food frees time for other endeavors. The same can be said of database, network, and systems administration. Automation doesn’t actually create time, it allows us to repurpose time. One way we repurpose time is by administering other databases, networks, and systems. That makes each of us more efficient.


    My friend and brother Brian Kelley (blog | @kbriankelley) preaches. To we geeks, he preaches about security. Another friend, Steve Jones (blog | @way0utwest) preaches the same message as Brian: Security matters. Data breaches remain too common. I expect more and worse, in part because people using bot-net attacks are ahead of the curve with automation. They are extremely efficient, using hundreds – sometimes thousands – of machines to do their bidding. As a matter of cosmic history, it has always been easier to destroy than to create. I would love to see these beautiful minds rise to the real challenge of creating.


  • The Recordings for SSIS Academy: Using the SSIS Catalog are Available

    The recordings for SSIS Academy: Using the SSIS Catalog, Day 1Day 2, and Day 3 are now available (registration required)!

    Day 1 focused on creating the SSIS Catalog and SSIS deployment. [Note: Video is missing for the first 4:15 of this recording.]
    Day 2 focused on SSIS Execution and Monitoring Wednesday.
    SSIS Configuration was the topic covered on Day 3.


  • Three Free Webinars About Using the SSIS Catalog

    Mr. Kent Bradshaw and Llian the Wonder PuppyKent Bradshaw and I are delivering a series of webinars 27-29 Dec 2016 called SSIS Academy: Using the SSIS Catalog.

    Why? We’ve been using the SSIS Catalog in Production since 2012. We’ve learned a lot about how it works. Some parts of the SSIS Catalog are complex and we want to share – as best we can – what we’ve learned.

    The daily agenda is:

    Day 1: SSIS Catalog Introduction and Deployment – We discuss and demo creating an SSIS Catalog and deploying SSIS Projects to the SSIS Catalog.
    Day 2: SSIS Execution and Monitoring – We cover executing SSIS packages using the Catalog, and monitoring execution reports. We will also demo alternatives to Catalog-based package execution.
    Day 3: SSIS Configuration – No matter which method or tool you select to manage externalization, configuration is complex. In this session, Kent and I discuss and demo SSIS Catalog Environments, References, and Reference Mappings to project and package parameters.

    Our intention is to remain “in the box:” We plan to only demonstrate and discuss how to use the SSIS Catalog as it ships. The purpose of this series is to help data integration professionals learn more about applying the SSIS Catalog to enterprise data integration.

    Sound interesting? Register today!


    Learn More:
    Data Integration Lifecycle Management (DILM) Suite – (mostly) free – some not-free – software to help you manage SSIS in the enterprise.
    Deploying SSIS Projects to a Restored SSIS Catalog (SSISDB)
    Coming Soon: A Command-Line Interface for Managing SSIS Catalogs
    Stairway to Integration Services

    Related Training:
    SSIS Lifecycle Management (free recording, registration required)
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago

    Need help implementing an SSIS solution?
    Contact Enterprise Data & Analytics today!

  • SQL Server vNext CTP 1.1 is Available!

    Click here for an overview of updates included in SQL Server vNext CTP 1.1 and click here to download the bits for Windows.


  • An Interview With Me

    This post is inspired by Kenneth Fisher’s [blog | @sqlstudent144] post, An interview with me. Ken suggested,

    “Now if any other bloggers read this and feel like answering questions too I’d love to read them so make sure you put a link to your blog down in the comments below.”

    I’m in!

    1. What are the various database job roles and respective hiring requirements at your company?
    Enterprise Data & Analytics (EDNA) is a consulting company. We hire people with different levels of experience ranging from intermediate skills to experts. Intermediate skills usually means 1-3 years experience, depending on the type of experience. Experts write books, write blog posts, and/or present at SQL Community events. One goal is to help people grow from their current skill level to senior or expert.

    2. Describe an entry level job and the hiring requirements at your company?
    Although EDNA hires experienced people, we will consider any applicant. I shared some advice about careers in this post.  Maybe it’ll help…

    3. What is your job role?
    My official title is Data Philosopher which I equate to Grand Poobah from The Flintstones (without the cool hat… although I could totally rock a Grand Poobah hat…). I own EDNA and a software development company named Andy Leonard Consulting. Why does the name of my software development company contain the word “Consulting” while my consulting company doesn’t? That’s another story…

    4. What is your background?
    Triflin’-kid-with-a-big-mouth, Long Haired Country Boy, farmer, student, tobacco puller, computer hobbyist, hay bailer, peach picker, sax player, guitar picker, truck driver, stockyard hand, soldier, electronics tech, TOW and Dragon (tank-killer) missile guidance systems tech, alarm and vault systems tech, electrician (manufacturing only, I don’t do houses), electrical contractor (ditto), engineer, entrepreneur, husband, dad, believer, instructor, divorcee, husband (again), dad (some more), granddad, software developer, author, blogger, technical community person, consultant, manager, ETL/SSIS architect, co-founder, leader, follower, failure, quitter, re-starter.

    5. Describe the path that led you to this job role.
    I started tinkering with Motorola machine code in 1975. I learned BASIC and computers were a hobby until I became an electrician at a manufacturing plant. In that role my hobby and day job started to merge. In the 90’s I owned a business that designed, built, programmed, and installed electrical control systems for manufacturing machines. Some of the human-machine interface software acquired data from the control systems and stored it in databases. I earned the Microsoft Certified Solutions Developer certification back in the day (before .Net). I started doing data integration before I knew what it was called. The secret to my success […whatever…] is:

    Make the problem you are trying to solve give up before you do.

    6. Give me an example of an interview question that you would ask an entry level applicant, and explain what you would look for in a response.
    I like to open an interview with, “Tell me about your greatest failure as a human being.” I’m kidding. I loathe questions like that almost as much as I dislike “Why are manhole covers round?” – unless I’m interviewing for a job that requires working underground via manhole access, and some of the alarm systems tech work did require that, but I digress… I fish for mistakes, errors, and slip-ups when interviewing consultants. Why? First, it’s an integrity test. I cannot work with people I can’t trust. Second, as a consultant (and human), you are going to fail. I’d like to hear that you’ve already failed (and lived through it) because I’d rather you already know how to deal with (and, Lord willing, recover from) failure. I’d also like to know you’re not too ashamed or too afraid to tell me about failure. I’ve made mistakes. I will make more. I’m not going to judge you for failing. Your failures aren’t going to scare me off, most likely. I’ve probably done worse. And if you’re working for me and make a mistake, I’m going to want to know about it so we can fix it.


  • DLM (Database Lifecycle Management)

    As I type, SQL in the City Streamed is running on another monitor, and Alex Yates [DLM Consultants | @_AlexYates_] is screaming (almost), “Don’t do that!”

    I know a lot of data professionals. I know some of you read this blog. And I know some of you who read this blog are concerned about Database Lifecycle Management or DLM.

    It’s ok for you to be concerned about DLM.

    Why is it ok to be concerned about DLM? Because it’s new and different and complex and difficult to learn. I want to encourage you to invest the time and brain cycles required to learn DLM.

    As my friend Mike Fal [blog | @Mike_Fal] says, “We’re all developers now.”


    You’ve Done a Good Job

    As database professionals, we’ve honed our craft over the years (decades, for some of us). We’ve designed and documented best practices to make certain NothingBadHappens® during deployments to Production environments. Our solutions are good; some are great and some, awesome. I have a few questions about your processes and procedures:

    • Are your processes and procedures repeatable? (Do you tweak the functionality in process and procedures for each deployment or is the functionality stable?)
    • Are your processes and procedures automated or “automate-able?” (Tweaking functionality is awesome if you are consistently reducing the amount of manual intervention required – that’s actually a great way to get started with automation.)
    • Can you shut off your phone when on vacation? (Or, do you plan vacations around deployments?)

    Automate (Please)

    If you answered “no” to those questions, you could have very good reasons. Or not. I’m writing this post because I want you to think about answers to those questions. The answer to all of these questions could be, “It depends.” What does it depend on? 

    I submit you consider automating your processes and procedures for the following reasons:

    1. Automation will save time. Automation means less tweaking and tweaking takes time.
    2. Automation will improve quality. Tweaking is a nice word for change and change creates an opportunity for something to fail.
    3. Automation will improve the odds of you enjoying a phone-call-free vacation. (Do I really need to write more here?)

    How To Start

    You can start by checking out RedGate’s DLM Dashboard and DLM Automation offerings. Both are part of the SQL Toolbelt and DLM Dashboard is free.


    The time’s they are a-changin’. I encourage data professionals to roll with ‘em.


  • SSIS Catalog Browser Update


    SSIS Catalog Browser has been updated! Catalog Browser is part of the DILM Suite of utilities designed to assist enterprises with Data Integration Lifecycle Management (DILM).

    And it’s free.

    Catalog Browser provides a rich view of SSIS Catalog contents, configuration, and metadata. The utility surfaces properties, environment variables, environment variable values, references, reference mappings, and literal overrides without the need to open additional dialogs.

    New Features

    Catalog Properties

    Catalog Properties now display catalog metadata including:

    • Maximum Project Versions
    • Operation and Version Cleanup Enabled
    • Retention Window
    • Default Server Logging Level
    • SSIS Catalog Schema Build
    • Catalog Version
    • And more!

    Catalog Version is a Catalog Base property added to the standard collection of SSIS Catalog properties. Catalog Base is a custom .Net library we built to represent the SSIS Catalog.

    Virtual Folders Sort Order

    The order of the Projects and Environments virtual folders now match that of the Integration Services Catalogs node in SQL Server Management Studio’s (SSMS’s) Object Explorer. Why is this important? Visual cues and defaults count. We don’t want to confuse anyone by doing things differently than they have come to expect.

    Package Properties

    Package Properties are an important addition to Catalog Browser (and SSIS Catalog Compare). Perhaps the most important property is Package Version which is displayed in the format <Major Version>.<Minor Version>.<Version Build>. I can hear you thinking, “Why is the Package Version property so important, Andy?” I’m glad you asked! Although all three values may be edited from the SSIS Package properties window, Version Build auto-increments each time an SSIS Package is saved in SQL Server Data Tools (SSDT). Therefore, it can be used as a (potential) flag to indicate differences in SSIS Package deployments.

    It’s possible to manually update the value of an SSIS Package’s Version Build property from SSDT, so comparing the Package Version property is not a completely reliable mechanism for determining if two different SSIS Package deployments are identical. Because editor metadata is also stored in SSIS Package XML, two deployments can be functionally identical and yet have different versions.

    TL;DR Regarding SSIS Package Version Build Property Values

    Why are we displaying Package Version in SSIS Catalog Browser (and comparing Package Version in SSIS Catalog Compare)? Because even though Version Build can be manually manipulated, we believe the Package Version property provides some indication that the packages match. We cannot think of a use case for changing an SSIS Package deployed to the SSIS Catalog and then manually manipulating the Version Build property so that it matches the Package Version of the old SSIS Package. But we could be wrong. Please let us know if you have such a use case.

    When executing SSIS Packages stored outside the SSIS Catalog using dtexec, dtexecui, or SQL Agent, one verification option is Verify Package Build (/VerifyBuild):


    It’s possible this option is set for SSIS package execution and that your enterprise chooses to maintain a consistent Version Build property value so that this verification succeeds and does not need to be updated.

    In my opinion, the Verify Package Build option should not be managed by maintaining the value of the SSIS Package’s Version Build property. Rather, the value stored in the execution engine should be updated to reflect the latest SSIS Package Version Build property value.

    I am not aware of a Version Build verification for SSIS Packages stored in the SSIS Catalog. SSIS Catalog Compare will compare the Package Version values between two SSIS Packages stored in different SSIS Catalogs. SSIS Catalog Compare does not detect a mismatch between two packages in the same Catalog Folder and Catalog Project with the same Package Version metadata. We (and others) have looked into comparing SSIS Package versions to detect the differences (or lack of differences). Because DTSX files are XML and because the XML in SSIS Packages can (and does) “move around physically” in the file without changing logical functionality, this would be a semantic comparison. That’s one reason (not the only reason) why reliably comparing SSIS Packages is hard.

    Enjoy the new version of SSIS Catalog Browser!


    Learn More:
    SSIS Academy: Using the SSIS Catalog – Three free webinars 27-29 Dec
    Data Integration Lifecycle Management (DILM) Suite – free (mostly) and not-free software to help you manage SSIS in the enterprise.
    Deploying SSIS Projects to a Restored SSIS Catalog (SSISDB)
    Coming Soon: A Command-Line Interface for Managing SSIS Catalogs
    Stairway to Integration Services

    Related Training:
    SSIS Lifecycle Management (free recording, registration required)
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago

    Need help implementing an SSIS solution?
    Contact Enterprise Data & Analytics today!

This Blog



My Latest Book:

Community Awards

Friend of Red Gate

Contact Me


Privacy Statement