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 a Data Philosopher at Andy Leonard Consulting, 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 author of Managing Geeks - A Journey of Leading by Doing, and the Stairway to Integration Services.

  • 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.”


    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…)


    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.


    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 


  • 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 and 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


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


  • 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!



    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


  • 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


  • 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


  • 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.


    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 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,

    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,

    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,

    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:


  • 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:

    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:

    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:

    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:

    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:

    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:

    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:

    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:

    Figure 8

    In addition, an error will display in the Error List window (View—> Error List) as shown in Figure 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:

    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:

    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:

    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:

    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:

    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:

    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:


    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:

    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:

    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:

    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).


    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


  • 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:

    Figure 1

    Clicking “New OLE DB Connection…” opens the Configure OLE DB Connection Manager window, shown in Figure 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:

    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:

    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:

    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:

    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:

    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:

    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


  • What’s the Number One Attribute of Awesome Email?



    Learn more:


  • SSIS Catalog Environments– Step 20 of the Stairway to Integration Services!

  • Consulting 101: Viewing the Field

    It’s important for a consultant to be able jump into a situation and quickly evaluate and assess the dynamics involved, in order to serve the client efficiently and effectively and economically. While a lot of people dislike military metaphors, there are a lot of good lessons to learn from military thinking. This is one area where military lessons can help, in my opinion.

    In his book, The Face of Battle, John Keegan writes about a skill:

    “…best described by the French word formation, aims if not to close his mind to unorthodox or difficult ideas, at least to stop it down to a fairly short focal length, to exclude from his field of vision everything that is irrelevant to his professional function, and to define all that he ought to see in a highly formal manner.”

    The general idea, fleshed out later in the book, is to view the current situation tactically, assessing the threat level of each component or “sub-situation” in terms of responses to potential actions of these actors. Formation is about prioritization with a bias for action. If you’ve read The Alchemist you know “beginner’s luck” is really a combination of the inevitability of opportunity combined with a bias for action.

    The ability to conduct a fast and accurate assessment of a situation is an important consulting skill.


    Learn more:


    Managing Geeks is available at Amazon in paperback and Kindle editions!

This Blog



Friend of Red Gate

My Company

Blog Roll

Contact Me


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