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 CSO of Linchpin People and SQLPeople, an SSIS Trainer, Consultant, and developer; a Business Intelligence Markup Language (Biml) developer; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server 2012 Integration Services Design Patterns. His background includes web application architecture and development, VB, and ASP. Andy loves the SQL Server Community!
Note: Comments are moderated. Spam shall not pass! </GandalfVoice>

  • Deployment and Execution – Step 18 of the Stairway to Integration Services is Live at SQLServerCentral!

  • Presenting Enterprise Information Management in SQL 2012 and SQL 2014 in Reston 16 May!

    I am honored to participate in the Reston SQL Server 2014 Launch Update event – a half-day event held 16 May 2014 at the Microsoft Reston Office. You can learn more and register here.

    Art Rask will kick-off the event with an overview of new features of SQL Server 2014, including:

    • Integrated In-memory technology
    • AlwaysOn HA / DR for mission-critical workloads
    • Cloud and on-premises scenarios support
    • Resource governance improvements
    • Advanced security enhancements

    After this introduction, two tracks are available for attendees: the Engine track and the BI track.

    BI Track

    Tim Mitchell (blog | @Tim_Mitchell) – co-author of SSIS Design Patterns and Group Principal at Linchpin People – and I will be presenting on new capabilities in SQL Server Integration Services (SSIS), including:

    • Upgrading from SSIS 2008
    • New designer features
    • Deployment
    • SSIS Catalog
    • Data Warehousing
    • Data quality services
    • Enterprise data integration design patterns
    • Master data services

    Reeves Smith will present on Analysis Services, Reporting Services, and Power BI, including:

    • Upgrading from SSAS 2008
    • Tabular model improvements
    • Implementing an enterprise BI strategy

    Engine Track

    Darryll Petrancuri will present “lessons from the field” featuring early-adoption stories of two new features of SQL Server 2014:

    • In-memory OLTP
    • Clustered columnstore indexes

    Casey Loranger will share significant improvements in SQL Failover Clustering, AlwaysOn Availability Groups, and cool new disaster recovery options using Microsoft Azure.

    I hope to see you there 16 May! Register today!


  • Presenting SSIS 2014 Data Flow Tuning Tips and Tricks in Boston 14 May!

    I am honored to announce I will be presenting SSIS 2014 Data Flow Tuning Tips and Tricks at the New England SQL Server User Group Wednesday, 14 May 2014!

    SSIS 2014 Data Flow Tuning Tips and Tricks
    Want to go fast? This session is for you! Attend and learn techniques for developing, instrumenting, monitoring, and managing SSIS 2014 Data Flow performance in your data integration enterprise.

    If you will be in the Boston area 14 May, join us! Details here.


  • Presenting Using Biml as an SSIS Design Patterns Engine at NoVaSQL in Washington DC Monday!

    I am honored to present Using Biml as an SSIS Design Patterns Engine Monday 24 Mar 2014 at the Northern Virginia SQL Server User Group (NoVaSQL)!

    I hope to see you there. Details…


  • Do You Learn?

    Silly question? Maybe. Hang with me, please. This will be short – a couple / three minutes, tops.

    Do you know more than you did six months ago? A year back? Yep, we both know you do. How did you feel back then, before you knew what you know now? Did you sense there was something missing? Did you know you didn’t know? No, you did not.

    That thing that you recently learned, did it just become true? Or was it true all along and you didn't know or accept it? What about the stuff you believe right now? Are there things you believe are true that you will later learn are not true? So what makes you so sure you have it all figured out now?

    Do you learn?


  • Step 17 of the Stairway to Integration Services is Live!

  • Flexible Source Locations - Level 16 of the Stairway to Integration Services is Live at SQL Server Central!

  • Me and the Man

    Part 1

    I grew up poor – the US version of poor. We never missed a meal but a few times it was only because we had a successful hunt. Suffice it to say that when we interacted with others, they always had more than us. More stuff, nicer houses and cars and toys. We weren’t unhappy kids – my brothers and I – but we were unable to do some things we wanted to do, things others took for granted. Perhaps the best description of how I felt about our childhood is a southern US expression: we made do.

    My first job was pulling tobacco at age 11 for $1/hour (I was probably overpaid). It was hard work. A few years later, in my mid-teens, I got a couple jobs that were less-physically taxing. I played sax in a country and bluegrass band on Saturday night and worked on a farm / orchard during warmer months. It worked out because most of the people who came to the dances on wintry Saturdays did not show up for the Saturday dance when it was warm out.

    I made decent money for a teenager. But never enough to get everything I wanted and definitely not enough to support myself.

    And then my girlfriend got pregnant. And my Mom and Dad separated. And I turned 18. During the same 10 day period in July 1981. Six weeks after I had graduated high school.

    I tried to get a job and found one working in a textile mill. I was still able to play music on Saturday nights to supplement our income. My new wife was still a senior in high school. Three days after I joined the National Guard I was fired from my job at the mill. I wasn’t scheduled to leave for Basic Training until summer, after the baby was due. I tried to get a job driving a feed truck for a local granary, but one of the guys in my National Guard unit worked there and told them I had just signed up and would be leaving for Basic Training soon, so they didn’t hire me. I understand. The law said (and still says) they had to hold my position open for me while I was gone and give it back to me when I returned. They didn’t want to go through the disruption of hiring me, then hiring someone else for a few months while I was away at training, and then letting that person go when I returned. The law meant well, but it kept me from getting a job I really needed.

    I ended up working at the stockyard mucking stalls. You may have seen an episode of Dirty Jobs featuring this kind of work. I would clean out the stalls with a Bobcat, push the manure to the end of the market, load it with a larger bucket loader onto a manure spreader. Then I would drive the spreader out to the fields and pastures and use it to fertilize the fields. I started during the winter months, but as Spring sprung pollen filled the air. My allergies made me miserable every day at work.  I couldn’t quit work because our daughter was born and there were even more expenses.

    Things picked up some when I went to Basic Training. I was getting paid more and all my meals were provided by the Army. I sent almost my entire paycheck home. But then I came back from Basic Training and had to find more work for six months before I went to Advanced Individual Training (AIT). I went back to playing music and working at the stockyard until that time. When I got out of AIT it was summertime. I found some work doing construction and still played music on the weekends to make ends meet. When the construction work slowed, I looked for a new job and found one – but again, it paid barely enough to survive and there was no money to do fun stuff.

    I once applied for a job at the post office. I’d heard they would hire veterans and people with military backgrounds. I didn’t even get a letter telling me “better luck next time.” Maybe it got lost in the mail.

    You might read this and think “Wow, things were hard for you.” They were, but that wasn’t the worst thing. The worst thing was that people were always saying stupid things to me like, “You should go to college” and “Keep working hard, it will pay off one day.” That was the last thing I needed to hear. I didn’t have money to go to college, so why waste my time going to the admissions office to see if I could attend? That was worse than a waste of my time, it would take up time the admissions office people would use helping people with some kind of chance in life. And working hard? I’d been doing that half my life. A lot of good that did. It wasn’t making the food stamps go away. I would have been happy to make $10,000 / year. These jerks were talking like I could make $25,000. Yeah… right.

    It was me against the man. My score was a big ol’ goose egg and the man kept wracking up points he’d never even use. I was never going to get ahead. Life sucked.

    Part 2

    Everything I wrote in Part 1 is accurate. But it is not true. First, it is written from an extremely narrow, selfish perspective – it’s all me, my, I. Second, all the responsibility is pushed to others. It was never my fault. “My girlfriend got pregnant” is probably the most telling indicator. “I couldn’t quit work because our daughter was born and there were even more expenses.” Accurate? Yep. True? Not by a long shot.  Third, The advice I dismissed was not only accurate, it was true.

    So what was true?

    • I started in life with a couple strikes against me, this is true. But it was nothing like real and actual poverty. I’ve seen poverty and I was living the dream by comparison. Our father tried his best (he started with a few more strikes against him). Our mother surrounded us with love and understanding. We had a good childhood.
    • I got my girlfriend pregnant.
    • I didn’t learn to manage money.
    • I refused to listen to good advice.

    There was “a man” messing with my life, alright. His name was Andy Leonard. And he wasn’t much of a man as he was a punk disguised as a man. 

    Part 3

    I went to community college. It took me six years to complete an Associates of Applied Science degree in Electronics Engineering Technology. But months before I completed that degree, I had started to realize my problem was me. I started my own business within months. It was hard because I still didn’t understand how money worked. But that business grew and kept us fed and watered for several years.

    When the economy shifted, I went back to work – but not at the stockyard. This time I was actually recruited by people who wanted to pay me to be a consultant. I struggled mightily while running my first business. But I survived and I learned hard lessons – mostly about myself, but also about how to communicate and sell and ship.

    It turns out those skills are worth something in every field of endeavor. Especially consulting.

    I changed jobs a few more times, learning new things as I did. One important thing I learned: At a minimum, work is me trading my time, energy, and knowledge for money. I bring something to the table – something to trade. My employer brings something to the table – something to trade. I am not selling my soul, I am trading time for money. I am not a slave, I am an employee. I stopped seeing employment as a contest and started seeing it as a balance-scale. I was willing to give on this point if I could get that point. Often, the thing I wanted didn’t even matter to my employer and they were happy to make the trade.

    Truth is, I’m still learning to manage money. It’s like learning anything else – it takes time, patience, and practice.

    Part 4

    Please think.

    Think about the stuff you do not like – the obstacles – in your life and career. Everyone has obstacles in their lives (some of them are real). It’s rare that you find yourself in possession of the means and opportunity to swiftly destroy an obstacle in your life; most of them must be worn down by time and persistence (Exhibit A: Many lottery winners are bankrupt within a few years of winning…). Ask yourself: How much of that stuff requires my acceptance, agreement, and  / or participation to continue? You may be surprised at the answer to that question.

    Think about who “the man” is in your life and career. How are you responding to the obstacles you identified earlier? If some of the obstacles require your “help” (acceptance, agreement, and  / or participation) to continue, you know how to manage that, right? Stop helping them! What about the other obstacles? One of two things is going to happen:

    1. The obstacles will stop you.
    2. You will overcome the obstacles.

    There’s no middle. There’s no stasis. Things are going to change, and they will either get better or worse. For things to get better, you have to overcome entropy. You are not on a level playing field. Life is not fair.

    Are you going to continue to blame others or are you going to start taking responsibility for your actions?
    Are you going to ignore good advice or are you adult enough to admit you are sometimes wrong and do not know everything?
    Are you going to keep losing points to “the man” or are you going to change?

    Part 5

  • An Overview of SSIS Parameters - Level 15 of the Stairway to Integration Services is Live at SQL Server Central!

  • SQL Saturday 277–Richmond Precons Are 21 Mar 2014!

    SQL Saturday #277 is going to be held 22 Mar 2014 in Richmond Virginia!

    There will be three day-long pre-conference sessions on Friday 21 Mar:

    You can find details of the precons here and you can register here!

    I hope to see you there.


  • Presenting Using BIML as an SSIS Design Patterns Engine at SQL Saturday 241–Cleveland!

    I am excited to be presenting Using BIML as an SSIS Design Patterns Engine at SQL Saturday #241 – Cleveland on Saturday 8 Feb 2014!

    If you’re going to be in the Cleveland area, check out these smart presenters (plus me!) and consider attending! Register here.

    I hope to see you there!


  • Presenting “I See A Control Flow. Now What?” at SQL Saturday Nashville!

    I am honored to present “I See A Control Flow. Now What?” at SQL Saturday Nashville on Saturday 18 Jan 2014! There are a bunch of really cool presentation scheduled, including a few Linchpins!

    I hope to see you there! Register here.


  • A Mistake, Responding Well, and a Public Call for a Public Apology

    I am about to break one of the rules of this blog. Years ago, I decided to never write about things that happen with clients until at least one year had passed. I am going to make an exception and write about something that happened very recently in the first part of this post. I confess I have been writing the second and third parts of this post for a couple years. I have waited to publish those parts for much the same reason I wait to write about client interaction: There is no substitute for the perspective given by time.

    Part 1: A Mistake

    “Nothing Has Changed”

    It was the last possible day to load the Operational Data Store (ODS) with data that would be used for 2013 year-end reporting. Automation was weeks away. The team had been working for weeks on a manual checklist for the load. Since some of the team had planned vacation time approaching, others were cross-trained to execute their portions of the checklist. It was a clean hand-off of work between professionals.

    The end-of-year load was scheduled for a Saturday. Early in the week before the Production load, I backed up the Production ODS, restored it to the Development server, and practiced. My part of the checklist contained over 100 manual steps. I ran through them three times, with analysis following each load. Tweaks were made each time. By Friday afternoon all were confident the load would succeed without a hitch.

    I began walking through my checklist, executing the load in Production, at 4:00 AM Saturday morning. The Production database was largely static, since the loads were occurring manually and infrequently. I had a backup that was a few days old. I distinctly remember thinking, “Nothing has changed; if I need it, I’ll just use that backup.” My engineering-spidey-sense tingled a little but I ignored it. “Nothing has changed,” I told myself again. Sitting here now, typing this, I know what you know as you sit there reading this: That was an assumption. A rather stupid assumption, at that.

    I have excuses.

    1. No one told me additional data had been loaded into the Production ODS. As a data professional, I am the data’s keeper. People should not have to tell me about changes. I should operate as if no one tells me anything. I should execute defensively. What am I defending? The integrity of the data. As excuses go, this is weak.
    2. Our team made exceptions to our own rules. On larger consulting gigs, we work in pairs. On smaller gigs, we work alone but share our thoughts and plans with other team members. There’s no substitute for a second set of eyes. Why did we deviate? Vacations and holidays conspired with an immovable deadline; the end of the year could not be postponed. This excuse falls into the combination-of-tolerances / comedy-of-errors category. Somewhat avoidable with better planning, but mostly inevitable once the second end of the candle was lit.
    3. I was exhausted. As a data professional and as a consultant, I owe it to my clients to work only when I am capable of performing at my very best. It must be “A Game” or “no game.” That’s one of the reasons the aforementioned vacations were not cancelled – even though an important deadline loomed. It’s the reason we strive to travel during the week and not on weekends. And that’s the way it is most of the time. This was that one exception brought about by the perfect storm of the amount and nature of the work remaining and a deadline that could not be moved. This excuse has some validity.

    Excuses stink. The short version of a long story is: The Production ODS load didn’t work like the three loads I practiced in Development. Hindsight is 20/20 and looking back, I realize the reason my practice loads worked differently from the Production load was data had been added to the Production ODS since my earlier backup. This prompted the execution of “Plan B.” When I executed Plan B and restored from the earlier backup, I effectively deleted the data I didn’t know had been added and then re-executed my 100+ steps. Not surprisingly, this load went much better.

    Something had changed.

    “What Do I Do Now?”

    I was made aware of the missing data during a meeting. The VP who owned the system that was expecting the data I wiped out was in attendance. It wasn’t an ambush – merely a coincidence. The team is just that small. I put the pieces together quickly in my mind and thought, “What do I do now?” I wanted to remedy the mistake, but I didn’t have a time machine that would allow me to go back and smack myself in the head at 4:00 AM Saturday. So I spoke up, “I know exactly how that happened. It was me. I restored from an older backup. Durnit! That was stupid of me. I apologize.” No one was happy I had deleted data from the Production system. But they acknowledged my apology and we moved forward with the meeting.

    After the meeting, still motivated to remedy my mistake, I contacted the developer who had loaded the now-missing data. I apologized to her and offered to help reload the data. As is often the case in our line of work, the hard part was figuring out which data to load, how to find and validate and cleanse it. The actual data-loading part is minor by comparison. Plus, she had saved her work. Reloading would take a few minutes. She appreciated my apology and offer, though.

    Happy Ending

    By sheer serendipity, my error worked in our favor. The ODS is a source for downstream systems, and it proved a lot easier to load one of those systems without the additional data present. With less effort we loaded that system, reloaded the data I deleted, and moved forward. All’s well that ends well, right?


    All’s well that we manage with integrity and intelligence.

    Part 2: Responding Well

    Several apology-generating incidents (AGIs) occurred around the same time. You can read about a couple of them here:

    Responding to Plagiarism

    In the first instance, a content author plagiarized Itzik Ben-Gan’s content from his book, Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions. Once the plagiarism was identified, SQL Server Central issued a public apology and held a contest which gave away 10 copies of the book – generating 10 sales for the book.

    Why did SQL Server Central apologize? I think that’s a great question. SQL Server Central apologized because they are the organization – the website backed by a corporation – that published the plagiarized material. Did SSC intentionally publish the stolen ideas? Goodness no. It was a mistake.

    Everyone makes mistakes.

    Just like my mistake with the backup, they didn’t realize it was a mistake until after the damage was done. And, just like my mistake, they took immediate action to rectify the matter. First, they acknowledged their error and apologized. Second, they did what they could to correct the negative impact by purchasing copies of Mr. Ben-Gan’s book for the contest. Both responses were appropriate. Kudos to SQL Server Central for stepping up.

    Buffer Overflow

    Tim Mitchell wrote an SSC guest editorial about Buffer’s response to hacking. In October, Buffer was hacked. Spammy messages, appearing as though sent by Buffer users, began appearing on social media profiles of Buffer users. As Tim notes: To Buffer’s credit, they opted for transparency before they really knew what was happening.

    In and of itself, this is simply awesome. But that’s not all. Joel Gascoigne, Buffer’s CEO, wrote the post himself, and he opened with this line:

    I wanted to post a quick update and apologize for the awful experience we’ve caused many of you on your weekend.

    The ninth word in Joel’s initial update on the topic is “apologize.”

    Everyone makes mistakes.

    I make mistakes like the incident described in Part 1. SQL Server Central didn’t catch the plagiarism until after the article was published. Buffer detected a breach soon after it occurred. In each case an apology was issued early.

    Apologies are most effective when they are delivered immediately. But given the choice between late and never, late works. Which brings me to …

    Part 3: A Public Call for a Public Apology

    For the reasons I outlined in this post, I believe the Professional Association for SQL Server (PASS) owes Steve Jones a public apology.

    Not everyone agrees with my original thoughts and I do not expect everyone to agree with my public call for a public apology. Before scrolling to the comment text area, please read and consider these next thoughts.

    Do Apologies Convey Weakness?

    I know some believe apologies convey weakness and I disagree. In fact, the opposite is true. Everyone makes mistakes. By definition, mistakes are not intentional. Admitting a mistake and apologizing demonstrates an awareness of this reality. Conversely, not apologizing could be interpreted to mean, “We never make mistakes,” and that is not a healthy or helpful way to think.

    Do Apologies Admit Malice?

    Goodness no. A wise person recently wrote we should always assume good intentions. I concur. I didn’t intentionally wipe data from that database. SQL Server Central didn’t intentionally publish plagiarized material. And Buffer didn’t leave a back door open for hackers.

    Everyone makes mistakes. Stuff happens. Even when we mean well. Apologizing is a way to admit, “We know you didn’t like the way this happened and we’re sorry you were hurt.”

    Why Are You Asking for a Public Apology Publicly?

    I’ve made this request to members of PASS leadership privately. Repeatedly. I’ve pleaded, in fact. Why? I believe this is a blemish on PASS’s relationship with the SQL Server Community. PASS leadership alternately positions itself as community leaders and as the community. I will not address the merits of either argument in this post, but I will point out that the 2010 elections episode is pivotal in the history of the relationship between PASS and the SQL Server Community.

    Why Are You Doing This Now?

    “It has been 3.5 years. Why now, Andy?” That’s an excellent question.

    Zerost, I love the SQL Server Community. And I love PASS. I felt – and still feel – the 2010 PASS elections were a dark hour for both. Truthfully, most SQL Server Community members are not aware of details. Most of those who are aware of the details stopped worrying about them long ago – some because they felt it futile to “fight city hall;” some because they didn’t see an issue with the decision itself, only with the response / reaction to the decision; some for other reasons; and some just because. I respect everyone’s decision. I firmly believe people do the things they do and believe the things they believe for reasons that make sense to them.

    I will never win a PASSion Award, but it won’t be for lack of passion for both the PASS organization and the SQL Server Community. I care deeply about both. I perceive a divide between PASS leadership and the Community and I would like for that wall to be removed. I believe this dark hour is the strongest part of that wall. I know PASS and the Community are stronger and better together.

    First, I have changed. My initial reaction to the decision to not allow Steve Jones on the 2010 PASS Board of Directors ballot was not positive. The decision by PASS leadership hurt my friend and I was ticked. At the top of this post, I mention my policy of not blogging about incidents until at least one year has passed because time brings perspective. I needed time to move from a place of not-wanting-to-help to a place of wanting-to-help. It took a while. A long while. I am there now. And I want to help.

    Second, PASS leadership has changed. The makeup of the Board of Directors, Executive Committee – even Corporate Founders – has changed. Kevin Kline points out many ramifications of CA leaving the PASS Board of Directors in his excellent post, What’s the Hidden Issue in the Recent PASS Bylaw Changes? But it goes deeper than that: Thomas LaRock is now President of PASS. I know Tom and, like most people who know him, hold him in high regard. I respect Tom. I like Tom. I expect him to lead PASS well, and differently. I am not so naïve as to think Tom can single-handedly solve every challenge PASS faces. But I am confident he can continue to nudge the organization in the best direction.

    I believe PASS has been moving in a better direction since 2010, but I do not believe PASS leadership has crossed the middle point between where it was and where it needs to be.

    That last statement is completely subjective. At this time and in this post, I choose not to discuss the reasons I feel this way. As I stated earlier, people believe things for a reason. I have many reasons for believing what I believe about this. For now, I will engage with anyone privately to discuss what I believe and why – there’s an Email link in the “This Blog” section of this page – up and to the right.

    In my opinion, the real shame would be for us to look back 3.5 years hence with this issue still unaddressed.


    I believe PASS, the organization, should apologize to Steve. Publicly. I think the way PASS treated Steve was a mistake. I think everyone realizes that. I think an apology is the appropriate response and I believe an apology can be made with integrity.

  • Presenting Using Biml as a Design Patterns Engine to Midlands PASS 9 Jan 2014!

    imageI am honored to present Using Biml as a Design Patterns Engine to the Midlands PASS Chapter Thursday 9 Jan 2014!

    Summary: Business Intelligence Markup Language provides a powerful solution for creating and managing SSIS Design Patterns. Andy Leonard, one of the authors of SSIS Design Patterns, demonstrates the flexibility of BIML in this session.

    Register here!

    If you will be near Columbia, SC one week from Thursday and read this blog, stop by and introduce yourself. I’m the fat guy with a fu.

    I hope to see you there!


  • RichmondSQL Meeting Tonight! (12 Dec 2013) - Az Arunachalam on Extended Events

    When: Thursday, December 12th, 2013, 6:30 PM - 8:00 PM
    Social Time starts at 6:00 PM!

    SQL Server Extended Events

    Microsoft is deprecating Trace and Profiler, a staple for monitoring SQL server since forever, in favor of Extended Events, introduced in SQL 2008. Monitoring of some of the new features like AlwaysOn Availability Groups in SQL 2012, and a lot of additional features/options that hitherto weren't easily accessible with Trace and Profiler are only available through XEvents. Come see the new functionality and how you can take advantage.

    Speaker: Az Arunachalam

    Az Arunachalam has been working with SQL server since v4.21/SQLNT. He started as a network engineer managing Novell NetWare networks, graduated into managing Windows NT, moved onto development, and is currently working as a database architect with the VA Dept of Corrections leading a small team of smart integration specialists, report developers/analysts, and database administrators. He is also an organizer of the Richmond SQL Saturday event. In his spare time, he likes to tinker with Arduino/Netduino development, and looks forward to getting his hands on EV3.

    Location: Markel Plaza
    4600 Cox Road
    Glen Allen, VA 23060

    This month’s sponsor:

    Register now!


This Blog


My Company

Other Blog

Check out my personal blog...

Contact Me

Twitter: @AndyLeonard

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement