THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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 Integration Services Design Patterns and Managing Geeks - A Journey of Leading by Doing, and author of the Stairway to Integration Services.

  • Andy-Frickin-Leonard

    A few years ago at a conference, a young man approached me and asked if I was ready for my presentation. I squared with him (for emphasis) and replied, “No, sir. In fact, I’m worried about it.” He looked a little stunned and said, “Why?” “I’ve never done this presentation before, I don’t like the flow of the material yet, and I’m concerned it’s going to fall flat when I deliver it.” Looking aghast, he said, “But… you’re Andy-frickin-Leonard.” “I’ve been trapped in here with me for several decades and I am not impressed,” was my reply.

    I’m always nervous before presenting, but that’s not what I want to write about this fine morning. I want to write about...

    Pride

    I’ve been dealing with my pride for a while now. I didn’t see it as that big of a problem until recently, though. Like yeast working its way through the entire lump of dough, a little pride never remains little for long.

    Two months ago I wrote Social Media and Me. As with every story, there’s more to that post than I’ve currently shared. I’m not going to share all of it here, and I may never share all of the story. Suffice it to say that the story began some months before that post, and that it continues after this post.

    I like learning stuff. Why? I’m not entirely sure of all the reasons – at least not now. But I can tell you part of the reason is that I like to understand how things really work. It’s a quest for truth for me, and one of the reasons I’ve started referring to myself as a Data Philosopher (I’m not smart enough to be called a Scientist). I’ve recently discovered another part of the reason is that I like being right. That doesn’t mean I hate being wrong – I see being wrong as necessary to learning what’s right. Mistakes and failures are part of life and engineering and philosophy, so I promise I never hated being wrong.

    I really enjoy engineering because it provides a framework (the scientific method) for learning what’s right. One can still make mistakes applying the scientific method – like asserting a premise – but, the scientific method is a good way to discover more accuracy. I believe accuracy is underrated but that’s another post…

    Empirical vs. Imperial

    My decrease in social media participation was driven by a realization that I didn’t care what others thought about what I wrote. That’s ok when I’m writing about engineering because (hopefully) I have evidence to back up any premise I assert. It’s not ok when writing about politics, however, because people have reasons for believing what they believe. I have no convincing evidence that I’m right and they’re wrong. But to help, I was spending social capital gained from sharing experience in the pursuit of knowledge in some wild attempt to prove the unprove-able (without the lens of objective history and hindsight). What was I trying to accomplish? At the time of this writing, I cannot honestly answer that question.

    I’m thankful some friends helped me see what I was doing. Am I done yet? Am I better? I am not done. Not by a longshot. I am maybe a little better. I’m definitely more aware of my pride and its role in my past behavior.

    A New Beginning

    One aspect of my faith (Christianity) is the concept of new beginnings. Lamentations 3:23 states: “The steadfast love of the Lord never ceases; his mercies never come to an end; they are new every morning; great is your faithfulness.” An interesting thought, especially in a book titled Lamentations.

    In an effort to address my pride, I promised to never again comment on political posts on social media. Do I have political opinions? Yes. Do my opinions align with the worldview of most? Nope. Is that going to change? I don’t expect it will. Will it change because I wrote something on Facebook? Definitely not. If anything, Facebook entrenches opposing opinion, it doesn’t change it. “I read your comment / post and decided you’re right and I’ve been wrong all this time,” wrote no one. Ever.

    So I’ve stopped posting on politics.

    Not So New

    Having a few months perspective on this decision provides the insight that this really isn’t such a new direction. The past few years, I’ve been led steadily and unswervingly away from doing stuff that doesn’t produce change to doing stuff that does. I had a pithy and intentionally-offensive term I used to describe non-productive behavior that merely feels good. It applies to my previous behavior but I won’t repeat it here. I will simply state, “Guilty,” and move forward.

    Unoffendable

    This book, Unoffendable, helped me a lot. It’s a book written from a Christian worldview perspective. It doesn’t even try to address change outside of the perspective of Christian faith. Ironically, that may offend some.

    I already knew I didn’t have a right to judge others. I did not realize I do not have the right to be angry. I knew pride is a killer. I did not realize how my pride was feeding my right-to-my-rights.

    What Am I Doing?

    One thing I’m doing that will help is serving people in Honduras. I’m looking forward to another opportunity to help. I’m thankful for the opportunity and the ability – neither originates with me; both are a gift. You can help, too. That link will take you to a GoFundMe page. All the money we collect will be used to send our team from Farmville to Tegucigalpa. Any money raised beyond that amount will be left in Honduras to serve those in need.

    Andy-Frickin-Leonard?

    I don’t think so. While I am humble about what I’ve learned and share regarding technology, I was anything but humble about my political beliefs. I was doing social media wrong.

    I still believe in doing business personally (and disagree with those who say, “It’s not personal, it’s business.”). That means I’ll continue to mix my faith and business, and I’ll continue to believe that’s a good thing in any field – but especially in the field of data where integrity is important.

    And the young man who said that to me? He knows better now. I’m not anything special – with technology or anything. I make mistakes, I learn stuff every day, and I’m growing right along with everyone else. I have such a long way to go.

    :{>

  • On Kindness

    Do you remember that time kindness backfired? Do you remember kindness letting you down? Me neither.

    Kindness never fails.

    I can hear you thinking, “Yeah, Andy? Well there was this time I was kind and…” The thing you’re going to finish that sentence with, the thing that didn’t happen? That wasn’t going to happen anyway. But here’s what did happen. Because you were kind, the person to whom you were speaking was left with nothing but your request to think about as they later reflected on the conversation.

    What if you’d been unkind? Then, upon reflection, the person to whom you were speaking will reflect on your unkindness. How do I know? I’ve been unkind. I’ve experienced unkindness from others. Kindness may improve the odds that your request will be granted. Kindness always improves the odds of you being heard. Unkindness produces the opposite in effect and in being heard.

    Some will misinterpret your kindness as weakness. That’s simply inaccurate. Many kind people are meek, but meekness is not the same as weakness (even though they rhyme). One trait of the people I consider wisest is: they are meek. In Receptive Human Virtues (2011), E. A. Cochran writes, “Meekness has been contrasted with humility as referring to behaviour towards others, where humbleness refers to an attitude towards oneself.”

    Meek in Action

    Have you ever looked at the work of someone else and said, “They did this wrong.”? Or, “They didn’t know what they were doing”? or “Who wrote this crap!”? I say that last one a lot… when reviewing my older code, but I digress… When speaking to an authority or client who doesn’t understand our craft, it’s easy to make ourselves look and sound knowledgeable and important by denigrating the work of others.

    There’s risk here, though.

    If you’re operating in this fashion – especially as a consultant – you’re conditioning your client to accept the latest word from the latest “expert” when that word is critical. Are you the only person who operates in this way? Nope, you are not. If you want to grab the money and run, poo-pooing the work of others is effective.

    “But what if the work is crappy, Andy?” Find a better way to say it. “I would not have designed this solution this way.” “There are updated patterns for solving this issue.” “I found an error.” Are any of those statements derogatory towards the original developer? Nope. Does the client walk away thinking, “I have someone who knows how to solve my business problem!”? Yep. And, at no extra charge, you’ve insulated yourself from the next cut-and-run “expert” who looks at your code with the client. You’ve built a new impression in the mind of your client: Real experts are kind.

    Conclusion

    Why am I blogging about this? For the same reason I blog about many things: I’ve failed and learned from my mistake. My hope is that someone will learn from my mistake and not fail, and that our community will be the better for it.

    :{>

    Learn more:

    Managing-Geeks-Cover-2003

  • Real World SSIS: A Survival Guide, 8 May, Baltimore

    My friend and fellow Linchpin, Tim Mitchell (blog | @Tim_Mitchell) will deliver a day-long seminar titled Real World SSIS: A Survival Guide the day before SQL Saturday – Baltimore (9 May). Attend and learn more about:

    • Handling warnings, errors, and other bad stuff
    • Data cleansing using SSIS, T-SQL, and DQS
    • More than just a green box: validating the results
    • Managing variables, parameters, expressions, and scripts
    • Identifying and resolving performance bottlenecks
    • Evaluating the new features of SSIS 2012-2014
    • Choosing and using the right package deployment model
    • Automating SSIS development with Biml
    • When SSIS might *not* be the best choice

    Tim Mitchell is a friend and co-author of both editions of the SSIS Design Patterns books. Plus, he’s an awesome instructor! I encourage anyone interested in learning more about SSIS to attend Tim’s seminar.

    :{>

    Learn more: 
    Stairway to Biml
    Linchpin People Blog: SSIS
    Stairway to Integration Services

    SSIS2014DesignPatterns200

  • Software Economics and Testing

    There are two types of developers: those who test their software and those who will.” – Andy, circa 2015

    It’s April Fool’s Day in the US, but I’m going to act like it’s Halloween. Software testing is no joke, and not testing should scare you.

    In 1996 (yes kids, years used to begin with a “1”), in the sixth issue of Fast Company magazine, Charles Fishman wrote They Write the Right Stuff – an article about Lockheed Martin’s On-Board Shuttle Group and the work they did writing and maintaining software for NASA’s Space Shuttle program. Some interesting “nummers” from the article:

    • 420,000 lines of code.
    • 1 error in each of the previous (at the time of writing) versions.
    • The previous 11 versions of the software contained a total of 17 errors.
    • $35,000,000 / year budget.

    I did the math: maintaining each line of code was $83.33/year. Plus 1/3rd of a penny. In 1996 money. According to the US Inflation Calculator, that’s equivalent to $124.12 per line-of-code per year today.

    I mention the economics because I’m a consultant. I know how much I charge for my time. I have a pretty good idea what others charge for their time. I co-own Linchpin People, and we subcontract software developers for ourselves and other companies. I know what we pay software developers and how much we charge others for their services. At the risk of disillusioning those who aspire to co-own their own technical consulting firm, we have precisely 0 clients paying us $35,000,000 per year – even in 2015 money ($35 million in 1996 would be north of $52 million in 2015).

    Why not? Because that’s a lot of money to pay for software. When lives, safety, and the pride of a nation are on the line, you’re paying for more than “just software.” The value of maintaining the Space Shuttle software was worth those costs. The cost/benefit analysis was sound.

    What’s the value of your software?

    Some Important Questions

    As you ponder the “nummers,” you are likely thinking about the software in your enterprise. Or maybe just the programming-for-fun project you’re working on in your spare time. It may be prudent for you to spend some time thinking about the costs and benefits of your software. Here are some questions to get you started:

    • How important is your software?
    • What’s at stake if your software fails?
    • What are the risks?
    • Can your enterprise tolerate your software being offline for some period of time?
      • If so, for how long?
      • Are some times worse – riskier, higher stakes – than other times?
    • What’s your backup plan if your software dies and cannot be brought back?
      • What happens if you lose Production data?
        • Even all of it?

    You may read that list and think, “Andy, you’re just trying to scare me.”

    Guilty.

    You durned right I’m trying to scare you. If fear is what it takes to wake you up to the realities of your situation, I’m not above scaring you. (In my mind, I just wrote, “I am an engineer.” But I digress…)

    Process

    If we were an agricultural society, it would be prudent to educate you about pests, soil care, and crop rotation. If we were manufacturers (and please understand, software is not manufactured…), it would be good to advise you to maintain a safe and healthy work environment for those who keep the machines running and keep your machinery in working order. But we make software.

    What are the corollaries? One good way to mitigate risk is to possess and practice a process.

    Before I go too far in this discussion about process, I want to reiterate my belief that people build processes to help people, processes are living and subject to maturity and change, therefore, people should always trump process. If you find a process harming a person’s life, liberty, or pursuit of happiness; you should re-examine the process. Processes should serve people, not the other way around. That’s ground rule #1 and it’s non-negotiable.

    The Lockheed Martin team practiced a process. It is enumerated in the article (did you read the article yet? You should. Right now, if you haven’t already. It’ll only take a few minutes…):

    1. The product is only as good as the plan for the product.
    2. The best teamwork is a healthy rivalry.
    3. The database is the software base.
    4. Don't just fix the mistakes — fix whatever permitted the mistake in the first place.

    This list is rich. But you’ve read a lot already so I’m going to bring this post home by unpacking #4.

    Mistakes Are Normal

    Even at $124 per line of code per year, mistakes happen. If you believe you can pay enough people enough money to eliminate errors in software, you are mistaken (see what I did there?). And if one of the risks you identified in the Some Important Questions section above is, “People could get hurt,” you need to do all the engineering you can to see that people do not get hurt. But even if you do everything humanly possible to prevent and mitigate mistakes, you will never eliminate mistakes. So the very first step is to realize – and act like you realize – mistakes are going to happen.

    Want to manufacture stress? Create a culture that does not tolerate mistakes. How? Rant, rave, yell, reprimand, and fire people when they fail.

    Want to deliver great software? Create a culture that tolerates mistakes. Even better, create a culture where failing fast is celebrated. Why? People learn from mistakes. People who make more mistakes learn more. People who fail faster learn faster. People who learn faster make great software.

    Mitigate the Negative Effects of Mistakes

    Failing safely is the best way to mitigate the negative effects of mistakes, that’s why martial arts students first learn how to fall. Why? Because they’re going to fall! So are you. Identifying software errors quickly (failing fast) is a profitable practice. Testing software is the best way to identify software errors. There are some rules to software testing and the rules are important:

    1. Test in a safe environment.
    2. Test realistically.
    3. Do not allow developers to test their own code.

    Test in a Safe Environment

    Set up a virtual machine. Something. Anything – except the Production server. There are conditions that need to be tested on the Production server. But, please, do not let “we need to test this in Production” be your first thought. Exhaust every other option first. Please.

    Test Realistically

    The reason you sometimes need to test software in Production is that there simply isn’t another server or environment in the enterprise that looks and acts like Production. There can be sound business and economic and timing reasons for such a condition. If you cannot test realistically in a safe environment, test as realistically as you can in a safe environment before testing in Production.

    Do Not Allow Developers to Test Their Own Code

    Do you want to read some stories of heartache? Some rants? Some helpful advice? Search for “Developers test their own code.” It will bum you out. And it may also help. Let me state up front that I develop software (SSIS is software even though contains “SQL Server” in its name). I miss things when I test my own code. Some of the links from the search above will contain stories about how and why this happens. Trust me. It happens.

    Get someone other than the developer to test the code. Prepare a test plan – even if the plan is “run this script on this test server.” That’s a test plan. It’s good enough for someone who understands what the code is supposed to do, or for anyone capable of interpreting a green/red indicator.

    Developers should conduct unit tests and functional tests. They should execute the code in a development environment to make sure it runs, does what they think it should do, and returns the expected results. Then someone else should run their code somewhere else to make sure all those things happen over there – in some location other than the environment where the software was built.

    Conclusion

    Remember: All software is tested; some, intentionally. What does that mean? That means that untested code is going to be tested in Production. Sometimes, the people testing your software in Production are your soon-to-be former largest customer. Think about it. Please. If this scares you, then good: I’ve accomplished what I set out to do.

    :{>

    Learn more: 
    Stairway to Biml 
    Linchpin People Blog: SSIS 
    Stairway to Integration Services 

    SSIS2014DesignPatterns200 

  • Varigence Announces Biml Hero Certification

    Biml Hero Certification Program

    “Do we get a super suit?” was Kent Bradshaw’s first response after reading the Biml Hero announcement from Varigence. Short answer: Yes. Well, not an entire suit, “a complementary Biml Hero t-shirt, polo, and jacket.” (Sorry Kent, no capes… yet…) But cool!

    From Varigence’s press release:

    Earning the Biml Hero certification will not be easy; however, it will be well worth the effort. Below are just a few of the many benefits of being a Biml Hero:

    • Biml Heroes will receive exclusive training as part of the certification process.
    • Varigence will refer Biml Heroes to organizations that request assistance with Biml or are looking to hire someone who is proficient in Biml.
    • Biml Heroes will be featured on both the Bimlscript.com and Varigence.com websites. They can add a picture, a short bio, and links to their social networking pages.
    • Biml Heroes can display the Biml Hero badge and Biml Hero Certified expert logo for professional use on their blogs, business cards, social networking sites and other marketing material.
    • Varigence will provide access to online Biml content and communities created exclusively for Biml Heroes.
    • Upon initial certification you will receive a complimentary Biml Hero t-shirt, polo, and jacket.

    To learn more about becoming a Biml Hero please download the Biml Hero program guide.

    Our first Biml Hero Training class will be in Copenhagen, Denmark on April 29, 2015. To register and enroll in the Biml Hero training program please contact training@varigence.com.

    :{>

    Learn more:
    Stairway to Biml
    Linchpin People Blog: SSIS
    Stairway to Integration Services

    SSIS2014DesignPatterns200

  • If Your Name Were a Verb

    Like my maternal grandfather, Buck Mayhew, did for his grandchildren; my friend Brian Kelley (blog | @kbriankelley) gives his children nicknames.

    A member of an SSIS team where I once worked was known for his tendency to rewrite SSIS packages. Any package, partially complete, assigned to him would be summarily replaced. It didn’t matter how complete the original package, it was done for. We (lovingly, I promise) used his name as a verb to describe whacking an SSIS package and starting over.

    How about you? If your name were a verb, what would it mean? Which behavior would your verb-ed (verb-ified?) name describe?

    :{>

  • Happy Pi Day 2015!

    It’s Pi Day! Happy Pi Day everyone!

    :{>

  • Biml: An Official Topic for PASS Summit 2015 Presentations

    I have waited (again) until the last minute to submit presentations for the PASS Summit. I’m submitting “Using Biml as an SSIS Design Patterns Engine” – a presentation based on a chapter in SSIS Design Patterns and Levels 2-4 of the Stairway to Biml – and I am pleasantly surprised to see Biml included in the top selections. w00t!

    BimlTopicPASSSummit2015

    :{>

    PS – The PASS Summit 2015 Call for Speakers closes at 9:00 PM PDT Sunday, 15 Mar 2015 (04:00 GMT 16 Mar 2015). There’s still time, but hurry!

    Learn more:
    Linchpin People Blog: SSIS
    Stairway to Biml
    Stairway to Integration Services

    SSIS2014DesignPatterns200

  • Announcing SSIS Design Patterns Training in London 7-10 Sep 2015!

    I am honored to work alongside TechniTrain to deliver SQL Server Integration Services Design Patterns in London 7-10 Sep 2015!

    Register before 31 Mar to save £100.

    Who is it for?

    The target audience for this course is intermediate SQL Server Integration Services developers (or quick learners) who wish to learn best practices and design patterns, and those who wish upgrade their existing SSIS skills to 2012 or 2014.

    Course Highlights

    SSIS 2012 Catalog Execution

    • A look “under the hood” of the SSIS 2012 Catalog and SSISDB database. Learn where the SSIS Catalog metadata resides in SSISDB, see examples of custom reporting, and examine ways to extend functionality with custom objects.

    Scripting in SSIS

    • Leveraging the Script Task to perform operations inside SSIS.
    • Using the Script Task and .Net to interface with external operations.

    Designing Custom Tasks

    • Take SSIS scripting to the next level by building custom SSIS tasks in .Net.

    Advanced Parameter and Variable Management

    • Using the new Project and Package Parameters.
    • Plumb the depths of the SSIS Expression Language.

    SSIS Connections and Configurations Management

    • Using Environments in the SSIS 2012 Catalog.
    • Custom Connections Management.

    Advanced Loop Containers

    • Typical (and atypical) uses of For Loop and Foreach Loop Containers.

    Enterprise Data Integration Lifecycle Management

    • SSIS Project and Package Deployment Models.
    • Security, Deployment, Execution, Monitoring, and Maintenance.

    Advanced SSIS Messaging

    • Impact of Deployment Model on project and package messaging.
    • Events and Event Handlers.
    • Logging, Reports, Configurations, and Environments.

    ETL Design Patterns

    • Execution Patterns.
    • Custom Logging Patterns.

    ETL Instrumentation Patterns.

    • Data Warehouse ETL Patterns
    • Dimension Loads.
    • Fact Loads.
    • Incremental Loads.
    • Change Detection methods.
    • Lookup Patterns.

    Data Integration Automation

    • Business Intelligence Markup Language (Biml).

    Key benefits

    At the conclusion of the training, attendees will have been exposed to:

    • New features in SSIS 2012/2014
    • Advanced patterns for loading data warehouses
    • Error handling
    • The Project Deployment Model
    • Scripting in SSIS
    • Designing Custom Tasks
    • Managing, monitoring, and administering SSIS in the enterprise

    I hope to see you in London!

    :{>

    Learn more:
    Linchpin People Blog: SSIS
    Stairway to Integration Services

    SSIS2014DesignPatterns200

  • Presenting at Midlands PASS 16 Apr 2015!

    I am honored to present SSIS Data Flow Tuning Tips and Tricks at Midlands PASS 16 Apr 2015!

    SSIS 2014 Data Flow Tuning Tips and Tricks

    Do you want SSIS 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’re in the Columbia, SC area and read this blog, come out and introduce yourself. I’m the fat guy with a fu.

    :{>

    Learn more:
    Linchpin People Blog: SSIS
    Stairway to Integration Services

    SSIS2014DesignPatterns200

  • Customer Service Done Well

    Fri, Mar 6, 2015 at 8:44 AM

    Hi Trello People,

    I think our organization has Trello Gold, but I cannot access features I think I should be able to access. I cannot change backgrounds, I cannot attach emails to cards.

    Help,
    Andy

    Fri, Mar 6, 2015 at 11:26 AM

    Hi Andy,

    Thanks for writing to us!

    Is [my work email address] the email address associated with your account? If so, you're correct- as part of Trello Business Class, your account should have Trello Gold.

    Could you create specific steps to help us reproduce this bug? Please see http://help.trello.com/customer/portal/articles/990621-reporting-trello-bugs. There's a detailed example in the "Submitting a bug report" section. Screenshots and detailed, specific steps are a huge help when we're trying to reproduce a bug on our end.

    Once we have those, we’d be happy to take a look.

    All the best,

    Michelle
    The Trello Team

    Fri, Mar 6, 2015 at 11:56 AM

    Hi Michelle,

    I cannot reproduce the error I experienced because settings have changed to allow me access to Gold features.

    I appreciate your help. I'm not sure what change was made or where, but I did not have access to Gold features earlier and now I do.

    I promise I'm not trying to be difficult, but I request more information. We are evaluating Trello for enterprise usage at Linchpin People. Can you tell me what change was made? I ask only because if it was something I did (or should have done), I want to make sure we document the necessary steps for others in our enterprise to access Gold features.

    If something needed to be changed by Trello Support, I would like to know so we can make that request for other users if they experience similar symptoms.

    I am extremely impressed with Trello! I've been looking for a tool like this for a long time. Although we're currently in "trial mode," I want to make sure we're evaluating it effectively. Your feedback is greatly appreciated.

    Thank you,
    Andy

    Fri, Mar 06, 2015 at 4:05 PM

    Hi Andy,

    I'm glad to hear that the issue has been resolved!

    I'm not sure why that issue occurred, but I suspect that it was a problem on our end- our engineers were testing something with Gold activation yesterday and it may have led to an error with your account accessing Gold features. I don't anticipate that the issue should arise again, but we'll document this instance in case other users run into the same problem.

    We're happy to know you enjoy using Trello so far. If this raises any additional concerns, or you have other questions, please let us know, and we'd be happy to help.

    All the best,

    Michelle
    The Trello Team

    Fri, Mar 06, 2015 at 4:35 PM

    Hi Michelle,

       You just passed my transparent customer service support tests – both of them. You said:
    1.    “I don’t know.”
    2.    “We were working on stuff that may have impacted your experience.”
    ...

    Excellent customer service shouldn’t be so rare that I was inspired to blog about it. But, sadly, it is.

    Kudos to Trello for making a cool Kanban / Scrum interface and for attracting awesome support people.

    :{>

    Learn more:

    Managing-Geeks-Cover-2003

  • Secure Connections Management in SSIS, Part 2

    SQL Server Integration Services (SSIS) is built to move data. SSIS ships with features to support the secure transmission of data. In this series, my goal is to promote awareness of these features and advocate for their use.

    SSIS Connection Managers

    In Secure Connections Management in SSIS, Part 1, I demonstrated configuring an SSIS Connection Manager to us Windows Authentication, as shown in Figure 1:

    SSISCM_2_1
    Figure 1

    Using Windows Authentication in SSIS Connection Managers is a best practice. But what if you need to use a SQL Login to connect to the database? The OLE DB Connection Manager Editor provides an option labeled, “Use SQL Server Authentication” in the “Log on to the server” groupbox. After selecting the “Use SQL Server Authentication” option, enter a user name and password in the appropriate textboxes, as shown in Figure 2:

    SSISCM_2_2
    Figure 2

    Save my Password

    There is a “Save my password” checkbox beneath the Password textbox, but checking this checkbox will not automatically save your password. Conversely, not checking the checkbox will not remove the password configuration from the OLE DB Connection Manager.

    I can hear you thinking, “What determines whether the password will be saved as part of the Connection Manager configuration?” That is an excellent question. Let’s examine some properties and use cases that determine whether the password will be stored as part of the Connection Manager configuration.

    Do Nothing

    If you created an SSIS package with design-time defaults and you do not check the “Save my password” checkbox and click the OK button on the OLE DB Connection Manager Editor, the password will be stored as part of the Connection Manager configuration.

    How can you check? After closing the OLE DB Connection Manager Editor, right-click the OLE DB Connection Manager in the Connection Managers tab at the bottom of the SSIS package Control Flow and click “Test Connectivity” as shown in Figure 3:

    SSISCM_2_3
    Figure 3

    Connectivity is automatically tested when click the OK button on the OLE DB Connection Manager Editor. But you can manually test connectivity at any time via the Connection Manager’s context (right-click) menu. If SSIS is unable to acquire a connection with the current configuration, the OLE DB Connection manager will appear as shown in Figure 4:

    SSISCM_2_5
    Figure 4

    In this case, SSIS is able to acquire a connection with the current configuration, so the OLE DB Connection manager will appear as shown in Figure 5:

    SSISCM_2_4
    Figure 5

    Note what happens when you re-open the OLE DB Connection Manager Editor, though: the Password textbox is empty as shown in Figure 6:

    SSISCM_2_6
    Figure 6

    If you click the “Test Connection” button the test will fail and you will a dialog similar to that displayed in Figure 7:

    SSISCM_2_7
    Figure 7

    If you dismiss the dialog and click the OK button to close the OLE DB Connection Manager Editor, the OLE DB Connection Manager will indicate the connection is not connected as shown in Figure 8:

    SSISCM_2_8
    Figure 8

    In addition, an error will display in the Error List window (View—> Error List) as shown in Figure 9:

    SSISCM_2_9
    Figure 9

    The error will be similar to:

    An error has occurred while connecting vmSQL14.TestDB: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'testUser'.".

    Why did this happen? Was the OLE DB Connection Manager ever really connected?

    Let’s answer that second question first. Yep, the OLE DB Connection Manager was really connected.

    Why Does The OLE DB Connection Manager Behave Like This?

    To answer why the OLE DB Connection Manager behaved in the way it did, we have to examine some other properties in the SSIS package and project. Those properties are:

    • SSIS package ProtectionLevel
    • SSIS project Deployment Model
    • SSIS project ProtectionLevel

    The SSIS Package ProtectionLevel Property

    The first property to examine is the SSIS package ProtectionLevel property shown in Figure 10:

    SSISCM_2_10
    Figure 10

    The default setting for the SSIS package ProtectionLevel property is EncryptSensitiveWithUserKey (shown in Figure 10). You can learn more about the SSIS package ProtectionLevel property at MSDN. The EncryptSensitiveWithUserKey package ProtectionLevel setting configures the SSIS package to encrypt the OLE DB Connection Manager password using “a key that is based on the current user profile.”

    The SSIS Project Deployment Model

    In SSIS 2012 and later, the default deployment model for SSIS packages and projects is “Project Deployment Model.” If you created an SSIS solution in SQL Server Data Tools – Business Intelligence (SSDT-BI) and haven’t made any changes to the default Deployment Model, you are developing in Project Deployment Model. There is currently only one other Deployment Model available in SSIS: Package Deployment Model. Package Deployment Model is included in SSIS 2012 and SSIS 2014 to provide backwards compatibility with SSIS 2005, SSIS 2008, and SSIS 2008 R2. In pre-2012 versions of SSIS, all SSIS packages used the same deployment model, and that model was the Package Deployment Model.

    How can you tell if your SSIS project is configured to use the Project Deployment Model or the Package Deployment Model?

    If the SSIS project is configured to use the Package Deployment Model, Solution Explorer will indicate this with the text, “(package deployment model)” beside the name of the project, as shown in Figure 11:

    SSISCM_2_11
    Figure 11

    If the SSIS project is configured to use the Project Deployment Model, there will be no text following the project name in Solution Explorer, as shown in Figure 12:

    SSISCM_2_12
    Figure 12

    The SSIS Project ProtectionLevel Property

    In Project Deployment Model, you must make sure the SSIS project ProtectionLevel property setting matches the ProtectionLevel setting for each SSIS package contained in the project. To set the SSIS project ProtectionLevel property, right-click the project in Solution Explorer and click “Properties” as shown in Figure 13:

    SSISCM_2_13
    Figure 13

    Clicking Properties opens the SSIS project’s Property Pages. the SSIS Project ProtectionLevel property is located on the Common Properties\Project page, as shown in Figure 14:

    SSISCM_2_14
    Figure 14

    Again, the SSIS Project ProtectionLevel property must match the SSIS Package ProtectionLevel property setting for every SSIS package included in the SSIS project.

    By default, the SSIS Project ProtectionLevel property is set to EncryptSensitiveWithUserKey and the SSIS package ProtectionLevel property is set to EncryptSensitiveWithUserKey. So – by default – these settings match.

    The Behavior, Explained

    When we enter a password into the OLE DB Connection Manager Editor and click the OK button, the value of the password is encrypted “a key that is based on the current user profile” (a quote from the page regarding SSIS package ProtectionLevel property at MSDN). Where is it encrypted? In the SSIS package XML. You can view the SSIS package XML by right-clicking the package name in Solution Explorer and clicking “View Code” as shown in Figure 15:

    SSISCM_2_15
    Figure 15

    The encrypted password is stored in the definition of the OLE DB Connection Manager. You can see it in the code displayed when you click “View Code” as shown in Figure 16:

     

    SSISCM_2_16
    Figure 16

    Closing the Code window and return to the SSIS designer window, we can reopen the OLE DB Connection Manager Editor as shown in Figure 17:

    SSISCM_2_17
    Figure 17

    Note the Password textbox is empty. If one clicks the OK button now and closes the OLE DB Connection Manager Editor, the Connection Manager is validated (via SSIS design-time validation) and – since the Password textbox was empty when the developer clicked the OK button, validation fails as shown in Figure 18:

    SSISCM_2_18
    Figure 18

    Returning to the Code view, we see that – compared to the previous XML – the <DTS:Password> tag is missing from the later version (on the right) as shown in Figure 19:

    SSISCM_2_19
    Figure 19

    The other difference between the two versions of the XML is the <DTS:ConnectionManager> tag is closed with a “/ >” construct in the later version (on the right) instead of a closing tag “</DTS:ConnectionManager>” in the earlier version (on the left).

    Conclusion

    Is this complex? Yep. As a data integration developer with SSIS, do you really need to know all of this? No, not all of it; but you do need to understand the moving parts of SSIS that are related to security – as well as how they interact – and these are some of the moving parts.

    Learn more:
    Advanced SSIS Training with Tim Mitchell and Andy Leonard in Dallas, TX 9-11 Mar 2015
    Watch the Video
    Test your knowledge -->
    Linchpin People Blog: SSIS
    Stairway to Integration Services

    SSIS2014DesignPatterns200

  • A Cool SSIS Catalog Visualization

  • Social Media and Me

    Earlier this month, I decided to take a break from Twitter and Facebook. A couple of you noticed (I’m ok, Mom. Really!) so I decided to share this post.

    I continue to post updates about articles, blog posts, and presentations at LinkedIn, and I’m (obviously) still blogging. I encourage everyone interested in the goings-on at Linchpin People to follow us on Twitter, Facebook, and LinkedIn.

    :{>

  • Secure Connections Management in SSIS, Part 1

    SQL Server Integration Services (SSIS) is built to move data. SSIS ships with features to support the secure transmission of data. In this series, my goal is to promote awareness of these features and advocate for their use.

    SSIS Connection Managers

    Let’s begin by examining ways to connect to a database – any database – using SSIS. SSIS connectivity is provider-driven. To connect to a database (or almost* any resource), SSIS developers use Connection Managers.

    * Some exceptions: SSIS developers can use the Script Task and Script Component to connect to resources via the .Net Framework. Custom tasks and components can “reach” from within SSIS to outside resources. The Raw File Source and Raw File Destination access the file system from within the Data Flow Task without using a Connection Manager.

    Configure an SSIS Connection Manager

    There’s no better way to learn than by doing, so please follow along if you have SSIS installed and available. Create an SSIS solution. Note that I am using SSIS 2014 for my screenshots, but I will endeavor to point out differences in versions of SSIS (I have experience with all released versions of SSIS). Once the solution is created, right-click in the Connection Managers tab and click “New OLE DB Connection…” as shown in Figure 1:

    SSISCM_1_1
    Figure 1

    Clicking “New OLE DB Connection…” opens the Configure OLE DB Connection Manager window, shown in Figure 2:

    SSISCM_1_2
    Figure 2

    If you have not configured OLE DB connections, your Data Connections list will be empty. I have configured a handful of OLE DB connections on my demo virtual machine. They are listed in the Data Connections listbox in Figure 2.

    Click the “New…” button to configure a new OLE DB connection. Clicking the “New…” button opens the Connection Manager editor, shown in Figure 3:

    SSISCM_1_3
    Figure 3

    If you click the Provider dropdown, you can see there are several OLE DB providers available to SSIS, as shown in Figure 4:

    SSISCM_1_4
    Figure 4

    If your workstation has other OLE DB providers installed, such as providers for Oracle or DB2, those providers will also appear in this dropdown list. For the purposes of this exercise, let’s stick with the SQL Server Native Client.

    Enter the name of a SQL Server instance in the “Server name” combobox, or click the dropdown and select a name from the available SQL Server instances, as shown in Figure 5:

    SSISCM_1_5
    Figure 5

    The “Log on to the server” groupbox is located beneath the “Server name” combobox. This is a common first encounter with data security in SSIS. There are two options:

    • Use Windows Authentication
    • Use SQL Server Authentication

    There are several reasons why Windows Authentication is considered better and more secure than SQL Server Authentication. Windows Authentication uses Security ID’s (SIDs) instead of username / password combinations. Active Directory manages domain access when a user logs on. SQL Server permissions are granted based upon SIDs. There are several caveats to using Windows Authentication making Windows Authentication a good, but not perfect, solution for all use cases.

    Note: I am not a security expert. I know some security experts and their advice to me has always been, “Use Windows Authentication whenever and wherever possible.” I share their advice with you.

    In the combobox labeled “Select or enter a database name,” do exactly that (select or enter a database name) as shown in Figure 6:

    SSISCM_1_6
    Figure 6

    Click the OK button to close the Connection Manager editor and return to the Configure OLE DB Connection Manager window, as shown in Figure 7:

    SSISCM_1_7
    Figure 7

    Click the OK button to close the Configure OLE DB Connection Manager window and return to your SSIS package. Note a shiny, new Connection Manager, as shown in Figure 8:

    SSISCM_1_8
    Figure 8

    Using Windows Authentication for SSIS connection managers is your first best practice for developing secure SSIS packages.

    Learn more:
    Advanced SSIS Training with Tim Mitchell and Andy Leonard in Dallas, TX 9-11 Mar 2015
    Watch the Video
    Test your knowledge
    Linchpin People Blog: SSIS
    Stairway to Integration Services

    SSIS2014DesignPatterns200

This Blog

Syndication

News

Friend of Red Gate

My Company


Blog Roll


Check out the Linchpin People Blog...
Linchpin People Blog

Contact Me

Archives

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