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 Enterprise Data & Analytics, an SSIS Trainer, Consultant, and developer; a Business Intelligence Markup Language (Biml) developer and BimlHero; 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.

  • SQL Server Management Studio (SSMS) v16.5.1 Now Available

    An updated version of SQL Server Management Studio (SSMS) for SQL Server 2016 (v16.5.1) is now available.


  • Learn More About the SSIS Catalog

    Join Kent Bradshaw and me for three free webinars about the SSIS Catalog!

    SSIS Academy: Using the SSIS Catalog is Tuesday – Thursday, 27-29 Dec 2016. Attend and learn how to:

    • Deploy an SSIS Project
    • Execute one or more SSIS packages
    • Monitor execution results
    • Configure execution and project parameters
    • And more!

    Kent and I hope to see you there. Register today!


  • Broken References in the SSIS Catalog

    It’s possible to create a broken reference in the SSIS Catalog. What’s a “broken reference?” Let’s begin by explaining SSIS Catalog References.

    I wrote a lengthy explanation of SSIS Catalog References and Environments in SSIS Catalog Environments – Step 20 of the Stairway to Integration Services at SQL Server Central.
    Microsoft has a good article on SSIS Catalog Environments and References called
    Create and Map a Server Environment.

    An SSIS Catalog Reference connects an SSIS Project to an SSIS Catalog Environment. Once an environment is referenced by a project, environment variable values can be mapped to project parameter, package parameter, and connection string values. Multiple references-per-project can be configured, but at runtime only one Reference may be selected for use with execution.

    We can demonstrate a broken reference by creating a new environment in an SSIS Catalog. Connect to an instance of an SSIS Catalog using SSMS. Expand the Integration Services Catalog node in Object Explorer. Expand the SSISDB node and a folder node. Right-click the Environments folder and click Create Environment:


    The Environment requires only a name and I named this one “EmptyEnvironment”:


    Note: Were I using EmptyEnvironment for externalization, I would create and configure Environment Variables.

    Next, right-click a project and click Configure. Click the References page and then click the Add button. In the Browse Environment dialog, navigate to EmptyEnvironment, select it, and click the OK button:


    A referenced environment appears as shown:


    Note: Were I using EmptyEnvironment for externalization, I would map Environment Variables to Parameter and / or Connection String values on the Parameters page.

    Return to the Environments virtual folder. Right-click EmptyEnvironment and click Delete:


    The Delete Object window displays. Click the OK button to delete the environment:


    The Environment is deleted with no indication or warning that this Environment is referenced by an SSIS Project in the SSIS Catalog.

    We just created a broken reference in the SSIS Catalog’s metadata.

    “Is this a bug?”

    I don’t think so. The design of the SSIS Catalog allows quite a bit of flexibility in managing references and referenced environments.

    Let’s query the SSISDB.catalog.environment_references view. Here we find the reference_id value. (Reference_id is used when the intent to execute  is created by the SSISDB.catalog.create_execution stored procedure.) Examining our EmptyEnvironment row shown below, we find a project_id field and value (58 in this example), which is what we might expect for a Catalog Reference that connects a Project and an Environment.

    But we don’t find an environment_id field.

    Instead, we find two attribute fields named environment_folder_name and environment_name, and a third attribute named reference_type which tells us whether the reference is to a Catalog Environment in the local Catalog Folder (Relative) or to a specified Catalog Folder (Absolute):


    This means we can fix a broken reference fairly easily by creating (or deploying) an SSIS Catalog Environment to the referenced Catalog Folder.

    I can hear you thinking…

    “How Do I Detect This Condition Exists, Andy?”

    I’m glad you asked! The answer is, “It depends on when the condition was introduced.”

    If manually executing a package with a broken reference from the SSIS Catalog node in SSMS, we will see a message similar to this:

    The parameter "SensitivePackageParameter" is configured to use an environment variable, but no environment has been selected.  Check the "Environment" checkbox and specify the environment to use, or specify a literal value for the parameter.


    If we are scheduling a package with a broken reference to execute using SQL Agent, we will see a message similar to that shown here:

    Parameter "SensitivePackageParameter" is configured to receive a value from an environment variable named "SensitiveStringValue" but there is no environment variable named "SensitiveStringValue" in environment "_env\env2".  Select a different environment, or use a literal value for the parameter. (Microsoft.DatatransformationServices.DTSExecUI.Controls)


    If the reference was broken after the SSIS package execution was scheduled, we may see an error similar to that shown below in the SQL Agent log for the job step that attempted to execute the SSIS package:

    Failed to execute IS server package because of error 0x80131904. Server: vmSql16\Test, Package path: \SSISDB\Test\ParametersTest\SensitiveTest.dtsx, Environment reference Id: 35.  Description: The environment 'env2' does not exist or you have not been granted the appropriate permissions to access it.


    There are other indications, but the scenarios above are where I usually encounter broken references.

    Another way to detect broken references is to use SSIS Catalog Browser,  a free utility from DILM Suite. In beta at the time of this writing, SSIS Catalog Browser provides a rich view of the SSIS Catalog in a single treeview. Broken references are indicated by gray text and a tooltip that identifies them as broken as shown here:


    SSIS Catalog Browser is free and you can download it here.

    “How Do I Fix a Broken Reference, Andy?”

    One way to fix a broken reference is to simply create or deploy the environment to the SSIS Catalog. Because environment_references are “connected” to projects by folder name and environment name (in the SSISDB.internal.environment_references table), broken references are relatively easy to fix.

    “Only You Can Prevent Broken References” – Smokey the Bear, Andy, circa 2016

    Like SSIS Catalog Browser, SSIS Catalog Compare detects broken references.


    Did you know SSIS Catalog Compare can help prevent broken References in the first place? It can!

    If you’re using SSIS Catalog Compare to manage your Data Integration Lifecycle, you can delete an Environment from the Catalog Browser treeview:


    SCC will ask you if you’re sure:


    If you click the Yes button, Catalog Compare will check to see if the Environment is referenced by an SSIS Project. If so, it warns you:


    If you click the Yes button, you will create a broken reference. If you click the No button, SSIS Catalog Compare displays a message informing you that you canceled the Delete Environment operation:


    Warning: Gratuitous Profit-Mongering! Sales Pitch Attempted Help:

    I am admittedly the World’s Worst Sales Person. I think it’s because I’m overly-focused on helping people.  With that disclaimer…

    As you can tell from the screenshots, SSIS Catalog Compare v1.5.2 is a beta / test version at the time of this writing. It hasn’t been released yet. Testing continues and I hope to release this version soon. I am also testing the first version of a Catalog Compare CLI (Command-Line Interface) named CatCompare (check out the 3-minute CatCompare preview video). I intend to release CatCompare with the new release of SSIS Catalog Compare and offer two pricing tiers:

    1. SSIS Catalog Compare + CatCompare for $???
    2. SSIS Catalog Compare only for $295USD

    Right now, you can purchase SSIS Catalog Compare v1.0 for $295. If you purchase SSIS Catalog Compare before I release the next version, you can upgrade to the new version of SSIS Catalog Compare and get CatCompare for free. But only if you make the purchase before the release. Ithoughtaboutwritingsomefineprinthereandrunningallthewordstogethertotrytosimulateinwritingthosedisclaimersyouhearattheendofcommercialswherethere’ssomecatchaboutprice,availability,orsomesuch.Thereisnocatch.Sorry.ThisisthebestIcoulddo…

    You can learn more here.


    You might like working with Enterprise Data & Analytics because we help you catch broken references.

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

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

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


  • A Tale of Software Development, Testing, and Demos

    Public Failure and Humiliation

    I delivered an SSIS Academy presentation yesterday, the first of many (I hope). You can view the recording here (free, but registration required). If you watch around the 57:30 mark, you will notice I encounter a bug in SSIS Catalog Compare around 59:15. My subsequent demo of CatCompare – the command-line interface scheduled for release around the end of the year – suffered as well.

    Live Demos Fail

    I enjoy presenting because I enjoy learning. I’ve learned a lot by listening to others present. I still do.

    Presenters whom I respect advise against doing live demos. I understand their logic. Errors and failures are unnecessary distractions to folks who want to see the capability of a technology.

    I get that.

    I Wish the Real World Would Just Stop Hasslin’ Me

    I like to demo real-world scenarios. Failures are real-world – especially when developing software. So I don’t feel defeated when a demo goes south.

    Take Two

    I continued testing today. I found and fixed the code that caused the issue yesterday. You can view the results here.


    I’ve divorced emotion from failure. It’s difficult but necessary if one is going to treat failure like steps on the path to success. Scott Adams has interesting thoughts on failing your way to success in his book:


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

    Learn More:
    Data Integration Lifecycle Management (DILM) Suite – free (mostly) and not-free software to help you manage SSIS in the enterprise.


  • Deploying SSIS Projects to a Restored SSIS Catalog (SSISDB)

    You’re coding away with SSIS, happy as a clam, and it’s now time to deploy your hard work to an instance of the SSIS Catalog. If you’re in Visual Studio, you right-click the project name in Solution Explorer and click Deploy. If you’ve been given an ISPAC file, you double-click it in Windows Explorer. Either way, the Integration Services Deployment Wizard starts and you (happily) supply the SSIS Catalog host instance and choose (or create) the SSIS Catalog folder click Next buttons until you click the Deploy button. It’ll be just a minute now, and you’ll see four green circles with checks…

    Except you don’t.

    You see three green circles with checks and one ugly red circle that contains an X:


    What’s worse, there’s an ERROR message box – sporting another of those handy red-X circles – that states:

    Please create a master key in the database or open the master key in the session before performing this operation. (Microsoft SQL Server, Error: 15581)

    For help, click:

    Ok, not to worry. Copy the For-help-click URL and paste…

    This isn’t the site you are looking for…

    What Now?

    I’ve seen this happen in the wild. It’s not pretty and never convenient. Sometimes, someone has “saved all sorts of time and money” by backing up the SSISDB database – say, on a Production server – and restored it to a QA server using code similar to this:


    And that restore executes and succeeds – just like the one I’ve shown above. Everything’s good to go, right? Well, everything looks good to go…

    With the restore complete, you’re ready to deploy the latest updates to QA for testing. That’s when this particular error rears its ugly head.

    “How Do I Fix It, Andy?”

    I’m glad you asked!

    Fix #1

    SSISDB is not your ordinary, run-of-the-mill database. There’s an application built around it. I can hear you thinking, “That’s pretty ordinary, Andy…” and you’re right. But most of the applications databases work with are not built into SQL Server Management Studio (SSMS), now are they? SSISDB’s application is found in the SSMS Object Explorer node named “Integration Services Catalogs.”

    Microsoft has some very helpful instructions at a page named Backup, Restore, and Move the SSIS Catalog (for SSIS Catalogs in SQL Server 2016. See this link for 2012 and this link for 2014). To backup and restore the SSISDB database, you’ll need to know the password used for encryption when you initially created the SSIS Catalog. The script I created to restore my SQL Server 2016 SP1 Catalog test instance is appended to this post. Feel free to copy, paste, and edit. But again, you must have the password used to create the original SSIS Catalog.

    Fix #2

    You can use SSIS Catalog Compare to generate scripts and ISPAC files from your Production instance of the SSIS Catalog, execute the scripts and ISPAC files in the prescribed order on your QA server, and be on your merry way. If your updates reside in a folder (or folders), you can opt to script the contents of a single folder thus:


    If you desire to migrate the contents of an entire SSIS Catalog instance to another instance, you can script the entire Catalog:


    Either way, scripts and ISPAC files are generated inside the file system folder you select. A file system folder is created for each SSIS Catalog folder, and the contents of this file system folder are scripts and ISPAC files required to migrate your SSIS packages, projects, folder, environments, references, and parameter mappings to the SSIS Catalog of your choosing and you don’t need the original password used to create the original SSIS Catalog:


    The scripts are named with numeric prefixes to ensure they are deployed according to SSIS Catalog precedence requirements.

    You might want to learn more about SSIS Catalog Compare here.

    You might also want to view the free recording my of my webinar SSIS Lifecycle Management (registration required).

    Andy’s Script for restoring SSISDB to SQL Server 2016 SP1

    As promised, here’s the script I use to restore SSISDB to a SQL Server 2016 SP1 instance:


      My script for restoring SSISDB to a SQL Server 2016 SP1 instance of SQL Server.
      I followed the instructions found at

      Hope this helps,
      Andy Leonard

      *** Action is required where you see three asterisks "***"


    -- create the ##MS_SSISServerCleanupJobLogin## login if it does not already exist.
    USE [master]

    print '##MS_SSISServerCleanupJobLogin## login'
    If Not Exists(Select [name]
                  From sys.sql_logins
                  Where [name] = '##MS_SSISServerCleanupJobLogin##')
      print ' - Creating the ##MS_SSISServerCleanupJobLogin## login'
      CREATE LOGIN [##MS_SSISServerCleanupJobLogin##] WITH PASSWORD=N'DWehrJfiRgMxEFaE=KxomUkF7fnV3poW/ZQPJ' -- *** change this, please - Andy
       , DEFAULT_DATABASE=[master]
       , DEFAULT_LANGUAGE=[us_english]
      print ' - ##MS_SSISServerCleanupJobLogin## login created'
    print ' - ##MS_SSISServerCleanupJobLogin## already exists.'

    print ''

    print ' - Disabling the ##MS_SSISServerCleanupJobLogin## login'
    ALTER LOGIN [##MS_SSISServerCleanupJobLogin##] DISABLE
    print ' - ##MS_SSISServerCleanupJobLogin## login disabled'

    USE [master]



    print 'dbo.sp_ssis_startup stored procedure'
    If Exists(Select + '.' +
              From sys.procedures p
              Join sys.schemas s
                On s.[schema_id] = p.[schema_id]
              Where s.[name] = 'dbo'
                And = 'sp_ssis_startup')
      print ' - Dropping dbo.sp_ssis_startup stored procedure'
      Drop PROCEDURE [dbo].[sp_ssis_startup]
      print ' - dbo.sp_ssis_startup stored procedure dropped'

    print ' - Creating dbo.sp_ssis_startup stored procedure'

        CREATE PROCEDURE [dbo].[sp_ssis_startup]
            /* Currently, the IS Store name is 'SSISDB' */
            IF DB_ID('SSISDB') IS NULL
            IF NOT EXISTS(SELECT name FROM [SSISDB].sys.procedures WHERE name=N'startup')
            /*Invoke the procedure in SSISDB  */
            /* Use dynamic sql to handle AlwaysOn non-readable mode*/
            DECLARE @script nvarchar(500)
            SET @script = N'EXEC [SSISDB].[catalog].[startup]'
            EXECUTE sp_executesql @script
    print ' - dbo.sp_ssis_startup stored procedure created'
    print ''

    use master  
    print 'Enabling SQLCLR'
    exec sp_configure 'clr enabled', 1 
    print 'SQLCLR enabled'
    print ''

    print 'MS_SQLEnableSystemAssemblyLoadingKey asymetric key'
    If Not Exists(Select [name]
                  From sys.asymmetric_keys
                  Where [name] = 'MS_SQLEnableSystemAssemblyLoadingKey')
      print ' - Creating MS_SQLEnableSystemAssemblyLoadingKey'
      Create Asymmetric key MS_SQLEnableSystemAssemblyLoadingKey 
       From Executable File = 'E:\Program Files\Microsoft SQL Server\130\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll'  -- *** check this, please - Andy
      print ' - MS_SQLEnableSystemAssemblyLoadingKey created'
    print ' - MS_SQLEnableSystemAssemblyLoadingKey already exists.'
    print ''

    print 'MS_SQLEnableSystemAssemblyLoadingUser SQL Login'
    If Not Exists(Select [name]
                  From sys.sql_logins
                  Where [name] = 'MS_SQLEnableSystemAssemblyLoadingUser')
      print ' - Attempting to create MS_SQLEnableSystemAssemblyLoadingUser Sql login'
      begin try
      Create Login MS_SQLEnableSystemAssemblyLoadingUser 
           From Asymmetric key MS_SQLEnableSystemAssemblyLoadingKey  
      print ' - MS_SQLEnableSystemAssemblyLoadingUser Sql login created'
      print ' - Granting Unsafe Assembly permission to MS_SQLEnableSystemAssemblyLoadingUser'
      Grant unsafe Assembly to MS_SQLEnableSystemAssemblyLoadingUser
      print ' - MS_SQLEnableSystemAssemblyLoadingUser granted Unsafe Assembly permission'
      end try
      begin catch
       print ' - Something went wrong while attempting to create the MS_SQLEnableSystemAssemblyLoadingUser Sql login, but it''s probably ok...'
       -- nothing for now
      end catch
    print ' - MS_SQLEnableSystemAssemblyLoadingUser Sql login already exists.'


    print ''

    print 'Restoring SSISDB'
    USE [master]

    begin try
    end try
    begin catch
    -- ignore the error (usually happens because the database doesn’t exist…)
    end catch

    FROM DISK = N'E:\Andy\backup\SSISDB_SP1.bak'  -- *** check this, please - Andy
      WITH FILE = 1,
       MOVE N'data' To N'E:\Program Files\Microsoft SQL Server\MSSQL13.TEST\MSSQL\DATA\SSISDB.mdf',   -- *** check this, please - Andy
       MOVE N'log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL13.TEST\MSSQL\DATA\SSISDB.ldf',   -- *** check this, please - Andy
    , STATS = 5


    print ' - SSISDB restore complete'
    print ''

    print 'Set ProcOption to 1 for dbo.sp_ssis_startup stored procedure'
    EXEC sp_procoption N'[dbo].[sp_ssis_startup]', 'startup', '1'
    print 'ProcOption set to 1 for dbo.sp_ssis_startup stored procedure'

    print ''

    Use SSISDB

    print '##MS_SSISServerCleanupJobUser## user in SSISDB database'
    If Not Exists(Select *
                  From sys.sysusers
                  Where [name] = '##MS_SSISServerCleanupJobUser##')
      print ' - Creating ##MS_SSISServerCleanupJobUser## user'
      CREATE USER [##MS_SSISServerCleanupJobUser##] FOR LOGIN [##MS_SSISServerCleanupJobLogin##] WITH DEFAULT_SCHEMA=[dbo]
      print ' - ##MS_SSISServerCleanupJobUser## user created'
    print ' - ##MS_SSISServerCleanupJobUser## already exists.'
    print ''


    -- One method for restoring the master key from the file.
    -- NOTE: You must have the original SSISDB encryption password!

    Restore master key from file = 'E:\Andy\backup\SSISDB_SP1_key'    -- *** check this, please - Andy
           Decryption by password = 'SuperSecretPassword' -- 'Password used to encrypt the master key during SSISDB backup'    -- *** check this, please - Andy
           Encryption by password = 'SuperSecretPassword' -- 'New Password'    -- *** check this, please - Andy

    -- Another method for restoring the master key from the file.
    -- NOTE: You must have the original SSISDB encryption password!
    print 'Opening the master key'
    Open master key decryption by password = 'SuperSecretPassword' --'Password used when creating SSISDB'   -- *** check this, please - Andy
    Alter Master Key
      Add encryption by Service Master Key
    print 'Master key opened'

    print ''

    print 'Checking the SSIS Catalog Schema Version'
    exec [catalog].check_schema_version @use32bitruntime = 0

    My results appear similar to this image (click to enlarge):


    I prefer writing idempotent scripts that inform me of what they’re doing.

    If you’ve encountered this error, I hope this post helps you understand a couple options for responding.


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

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

    Learn More:
    Coming Soon: A Command-Line Interface for Managing SSIS Catalogs
    Managing the SSIS Catalog
    Announcing SSIS Catalog Compare v1.0
    Stop an SSIS Package
    Stairway to Integration Services
    Stairway to Biml
    SQL Server Central

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

  • New Versions of SSMS and SSDT Available

    It’s Release Day! :)

    New versions of SQL Server Data Tools (SSDT) are available here. SSDT 16.5 and 17.0 (RC1) are available. Also available are Data-Tier Application Framework (DacFx) versions 16.5 and 17.0 (RC1).

    New versions of SQL Server Management Studio (SSMS) are available here. SSMS 16.5 and 17.0 (RC1) versions are available for SSMS.



  • SQL Server vNext Evaluation Edition Now Available

    You can download SQL Server vNext Evaluation Edition here. Today!


  • Coming Soon: A Command-Line Interface for Managing SSIS Catalogs


    I’m excited to announce CatCompare – a command-line interface (CLI) for SSIS Catalog Compare – is in pre-release testing. I plan to release the next update of SSIS Catalog Compare with an option to purchase the GUI product stand-alone or the GUI + CLI for a higher price.


    If you purchase SSIS Catalog Compare before that release, you can purchase the GUI and receive a free upgrade to the GUI + CLI version when they are released!

    Purchase SSIS Catalog Compare today! Check out the CatCompare video.

    Learn More:

    Data Integration Lifecycle Management (DILM) with SSIS Catalog Compare: SSIS Catalog Environments
    Announcing SSIS Catalog Compare v1.0

  • Agile or Waterfall?

    Waterfall project management is a serial approach to the phases of a project. Proponents of waterfall methodologies hold it’s best to plan: gather requirements, design the solution, develop it, test it, deploy and maintain it in discrete steps. Critics maintain it is impossible to know all of the requirements prior to design or development.

    by Peter Kemp / Paul Smith - Adapted from Paul Smith's work at wikipedia, CC BY 3.0,

    Agile project management is an iterative process whereby developers focus on deliverables in short deliverable cycles. Proponents of Agile methodologies believe these practices more accurately reflect the complex nature of software development and place more decision-making with the developer. Critics (accurately) point out that Agile projects are fluid in schedule and impossible to predict – especially when it comes to time and money.

    Which is Better?

    The answer is: it depends. “What does it depend on, Andy? “ I’m glad you asked!

    First, we need to accept that there are no pure Agile (iterative) or Waterfall projects. Every project is a blend. But every project also leans towards iterative or waterfall.

    What do You Prefer for Managing Business Intelligence Projects?

    I lean towards Scrum, an Agile project management methodology for managing business intelligence projects.


    By Lakeworks - Own work, GFDL,

    The critics of Agile are correct: there is no way to predict the end date and, therefore, the cost of a project. From a business perspective, stakeholders often feel they are being asked to continue writing checks without knowing how many more checks they will have to write, or for how long, or for how much. The critics of Waterfall are also correct: some – most, in fact – software development projects are simply inestimable. I’ve written about software estimation in the past, sometimes in the context of project management. 

    Is there some way to limit the business risk? Yes there is…

    Risk-Balanced Project Management

    Remember, no project is purely Agile or Waterfall. I’ve been practicing a risk-balanced methodology for decades. How does it work? I combine the best of both worlds: Agile and Waterfall. Here’s an over-simplified explanation of how we deliver projects at Enterprise Data & Analytics: We treat each Scrum Sprint (iteration) as a small Waterfall project.

    Sprints remain focused on deliverables. They must, or they’re not sprints. The developer makes the call about what’s in and what’s out in a given sprint. This works especially well with teams of developers who can practice Kanban or some other Theory-of-Constraints-based approach to problem-solving as a team.

    Are There Daily Standups?

    Yep. It’s not Scrum without daily standups. It’s important for the stakeholders to attend these meetings to maintain contact with the progress of the work. A Scrum Task Board – either virtual or physical – is a requirement. It can provide some feedback to stakeholders, but there is no substitute for stakeholders attending the daily standup meeting.

    Why is it important for stakeholders to attend standups? Risks – time- and money-costing issues – usually surface in the standup meeting first. I measure the risk-awareness of a stakeholder by their standup attendance. You may have read that last sentence and thought, “That’s not fair, Andy!” Maybe not. Experience has taught me that it’s accurate, though.

    Where’s the Waterfall?

    We usually run 30-day sprints. We shorten the Waterfall cycle to 30-days and limit it to the deliverables identified for the sprint. We’ll do a couple days of discovery followed by a couple days of design. Development starts during design in business intelligence projects. Data integration is a large component in business intelligence – often the largest component. Data integration is also a bottleneck for most of the downstream parts of a business intelligence project. Testing (Validation) is tightly-coupled to development, and is vital. If you get nothing else out of reading this post, please remember this:

    Deliver quality late, no one remembers.
    Deliver junk on time, no one forgets.

    We can run shorter sprints but my experience shows this actually delays completion of the project. Why? We need time to manage the (inevitable) issues that surface during a sprint.

    How Does This Approach Mitigate Risk?

    Believe it or not, business priorities shift. New information becomes available after the project starts. Maybe a competitor reveals they are more competitive than stakeholders believed. Maybe more marketing information shows a shift in customer demand. Maybe another internal enterprise project takes priority over the business intelligence project. Any number of market and business conditions can shift the necessity, priority, or direction of a business intelligence project.

    Consider the impact of “re-Waterfall-ing” a business intelligence project during design, development, or testing. I’ve been there. It’s expensive for both the developers and business. Taking a phased approach allows an agile (double entendre intended) shift or graceful pause to the business intelligence project.

    Are You Better Able to Estimate Project Completion?

    Yes and no.

    There are software and business physics in play. Laws that cannot be broken; principles that simply apply whether we like them or not. A phased approach allows us to place bounds around the unknown(s). This is, I believe, the most economical and the most reality-based methodology for delivering business intelligence solutions.

    When I write economical, I mean experience informs me this approach costs the business less money than other approaches while delivering more with greater efficiency. How? Waterfall approaches often involve Change Orders with associated charges (do you remember that time a change order was free? Me neither). Some consultants win with the lowest bid and make up the difference with change orders. My credibility and delighting my customer is worth more to me.

    When I write reality-based, I mean few software projects are completed when projected. On-time projects happen. But it’s rare. Why? Well, it’s either because a) all software developers and consultants are pathological liars; or b) software is inherently inestimable. I vote for b. When someone asks a software developer, “When will you be able to complete this task?” they are most often asking, “How long will it take you to figure out this completely new thing you’re tasked with figuring out?” I hear this question posed in many contexts. Sometimes I get asked the same question different ways. I’m not a fan of that kind of questioning, but the reason it works is bias.

    Sometimes the most honest answer is, “I don’t know.” It’s ok to not know. It’s not ok to not know and not know how to find out; so when I don’t know, I honestly respond with, “I don’t know but I can figure it out.”


    The goal of a phased approach is to balance risk for consultants, developers, project managers, stakeholders, and the business. A phased approach limits risk while preserving the options of all engaged. Should things shift, stakeholders can change the direction, priority, or (in extreme cases) the existence of the project in response; with minimal technical and financial impact to all involved.

    A phased approach is a great way to mitigate risk for all parties. It works well for Enterprise Data & Analytics and our customers.


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

    Related Posts:

  • PASS Board Elections–Voting is Open!

    Update: Voting is closed and the winners have been announced!

    Whether you get an email from the PASS organization or not, voting is now open for the PASS Board. Visit the PASS website and log in. You should see the myPASS page:



    If you’re eligible to vote, your page should appear similar to mine, shown above. Click the Vote Now button and choose wisely.


  • Managing the SSIS Catalog

    The SSIS Catalog is surfaced within the SSISDB database. In this post I describe some tips for managing your SSIS Catalog database, named SSISDB. For information regarding restoring SSISDB, please see Deploying SSIS Projects to a Restored SSIS Catalog (SSISDB).

    Create the SSIS Catalog

    To create the SSIS Catalog, connect to an instance of SQL Server using SQL Server Management Studio (SSMS). Open the Object Explorer and right-click the “Integration Services Catalogs” node:


    The Create Catalog dialog displays:


    The SSIS Catalog requires SQLCLR. Check the “Enable CLR Integration” checkbox to proceed:


    You can optionally select the “Enable automatic execution of the Integration Services stored procedure at SQL Server startup” option by checking its checkbox (I do):


    The SSIS Catalog requires a password. If you restore the SSISDB database (which wholly contains the SSIS Catalog), you will most likely need this password. So store it somewhere very safe.


    Click the OK button to create the SSIS Catalog.

    Note: I’ve created a short (< 2:00) video that walks through this process. You can view it here.

    Back It Up

    As with all SQL Server database, please back up SSISDB. What follows is a (very) basic guide describing one simple method to backup your SSISDB database. Please, please, please learn more about SQL Server backup and restore options and their implications before backing up an SSISDB database in your enterprise. Feel free to use the steps I describe on your laptop or a virtual machine. And please remember…

    Backups are useless. Restores are priceless. Conduct practice Disaster Recovery exercises in which you restore databases and then test functionality. You’ll be glad you did. Here is a link containing Microsoft’s advice on restoring the SSISDB database in SQL Server 2016.

    In SQL Server Management Studio (SSMS), right-click the SSISDB database, hover over Tasks, and click Back Up:


    When the “Back Up Database  - SSISDB” dialog displays, set appropriate backup options. (In this screenshot, I am setting no backup options. You can break stuff here. Unintentionally. Easily. In Production. Again, read more about backing up databases – especially about backing up SSISDB).

    I’m going to walk you through a very simple example of an SSISDB backup on one of my demonstration virtual machines:


    I remove the default Destination by clicking the Remove button.

    I click the Destination Add button and select a location and filename for my SSISDB backup:


    After clicking OK, my very simple (please read waaaaay more than this post before attempting this at work or home!!) back up is configured:


    When I click OK, my backup completes successfully:


    Deleting the SSIS Catalog

    If you want to delete the SSIS Catalog, drop the SSISDB database (Wait! Back it up first!):


    The Delete Object dialog displays. Click the OK button to attempt to drop the SSISDB database (and, thereby, the SSIS Catalog):


    Starting Over with a Fresh SSIS Catalog Installation

    Perhaps you’re trying to build a presentation or demonstration about creating an SSIS Catalog. Maybe you just want a fresh start. Whatever the reason, you may find it annoying that once you’ve created and deleted an SSIS Catalog you are stuck with some of the settings:


    Clearing the SQLCLR Option

    Clearing the SQLCLR option (“Enable CLR Integration”) is accomplished by executing the following Transact-SQL (T-SQL) script:

    sp_configure 'clr enabled', 0; 

    These statements, when executed in SSMS, appear as shown here:


    This solves some of the problem, but not all. The Create Catalog dialog still has that “Enable automatic execution of the Integration Services stored procedure at SQL Server startup” checkbox checked. Worse, now the checkbox is disabled!


    Fear not. This setting can be reset using a T-SQL script:

    EXEC sp_procoption
    @ProcName = 'sp_ssis_startup',
    @OptionName = 'startup',
    @OptionValue = 0;

    When executed, the resulting messages appear as shown:


    After you reset those settings you may demonstrate creating an SSIS Catalog in a pristine instance, as shown here:


    If you want, you may also delete the “SQL Server Maintenance Job” which is created when the SSIS Catalog is created:


    This post provides some basic guidance on how to manage the SSIS Catalog in an instance of SQL Server. Please read more at Backup, Restore, and Move the SSIS Catalog MSDN article.

    You might want to contact Enterprise Data & Analytics  because we get the SSIS Catalog.


    Related Training:
    SSIS Academy: Using the SSIS Catalog – 3 days, you, me, and the SSIS Catalog…
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago

    Learn More:
    Deploying SSIS Projects to a Restored SSIS Catalog (SSISDB)
    A New Version of SSDT is Available
    Announcing SSIS Catalog Compare v1.0
    Stop an SSIS Package
    Stairway to Integration Services
    Stairway to Biml
    SQL Server Central

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

  • 2017 SQL Skills SSIS Immersion Events

    I’m home after a day of travel that included waking up at quarter-til-early to catch a cab to SeaTac with Tim Mitchell [blog | @Tim_Mitchell], bumpy flights, and a two-hour drive back to Farmville. We had a fantastic time! Our students asked lots of interesting questions and Tim and I were, I believe, able to answer them all (with a little help – please continue reading…).

    The next SQL Skills SSIS Immersion Events are scheduled for late April / early May 2017 in Chicago. There are two: IESSIS1 and IESSIS2. As you can glean by perusing the links, IESSIS1 is designed to give those with no experience (or less experience) a firm foundation in the science and art of data integration using SSIS. IESSIS2 is geared for the more experienced data integration developer and architect.

    One cool advantage of delivering Immersion Events with SQL Skills: If you have a tangential question that involves advanced SQL Server knowledge, you can pop next door and find someone who knows these answer! That happened this past week. As Tim presented about New Features in SQL Server 2016, he mentioned the SSIS Catalog database (SSISDB) now plays nice with AlwaysOn. That raised a good question: What happens to executing SSIS packages during a failover? I wasn’t sure. Tim wasn’t sure. Jonathan Kehayias [@SQLPoolBoy] was delivering training right next door, so we asked him. He shared his thoughts and offered to run a test for our class. We did that and learned what happens, but we also learned some very interesting tidbits about configuring AlwaysOn for the SSISDB database. In Jonathan’s opinion, configuring AlwaysOn for SSISDB was “difficult,” perhaps even “tricky.”

    These are the types of things we share and learn (and I always learn something when delivering training) during IESSIS training events. We hope to see you at the next events in Chicago!


    Related Training:
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago

    Learn More:

    A New Version of SSDT is Available
    Announcing SSIS Catalog Compare v1.0
    Stop an SSIS Package
    Stairway to Integration Services
    Stairway to Biml
    SQL Server Central

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

  • A New Version of SSDT is Available

    Microsoft released an update to SQL Server Data Tools (SSDT) 2016 on 20 Sep 2016 – the tool used to design databases, SSIS, SSRS, and SSAS solutions.

    I can hear you thinking, “How do I get the update, Andy?” You can get version 14.0.60918.0 here.

    If you have the installer for SQL Server 2016 handy, you can get to the download link very easily. Start the installer and click the Installation page:


    There’s a link labeled “Install SQL Server Data Tools.” Click that link to go to the Download SQL Server Data Tools (SSDT) page. To get the Visual Studio 2015 version of SSDT 2016, click that top button / link:


    The button / link will take you to the Visual Studio-specific download page for SSDT:


    There’s a link under #3 (at the time of this writing) for downloading the web installer:


    Your mileage may vary, but I had trouble getting the web installer to work for the 20 Sep 2016 update. So I scrolled down a little and grabbed the ISO link under #4 (at the time of this writing):


    You can mount the ISO file using Windows Explorer:

    MountThe Download

    Once mounted the ISO file acts like an optical drive:


    From here, you can execute the SSDTSETUP.EXE file and install the update.


    Related Training:
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago

    Learn More:

    Biml Academy
    The Basics of Biml – the Execute SQL Task
    The Basics of Biml – Populating the Biml Relational Hierarchy
    Stairway to Biml
    Stairway to Integration Services
    SQL Server Central

    Need help or training implementing a Biml solution?
    Contact Enterprise Data & Analytics today!

  • I Was Wrong (About the SSIS Catalog)

    I apologize for misleading people who read my writings and have heard me speak on the topic of the SSIS Catalog. Here’s what I recently realized: The execution_id is different from the operation_id in the SSISDB database.

    In the past, I complained about the execution_id and operation_id being the same value and meaning different things. To be sure, if you are designing a database, you need to name fields with identical definitions with the same name. But, as I said, execution_id and operation_id are different values.


    Above is a portion of the SSISDB.catalog.executions view. This view, in part, joins data in the internal.executions table to data in the internal.operations table. The JOIN operation is accomplished by mapping the values in the internal.executions.execution_id column to the internal.operations.operation_id column.

    I used to fuss about this join, stating something like “This is bad. If the data is the same, the field should be named the same.” Again, that is a correct statement. In this case, though, the data is not the same.

    Operations in the SSIS Catalog include way more than executions. Validations are operations, for example. Updating Catalog-wide settings are operations. Executions are operations, too; just not the only operations.

    As I regularly tell folks, I’m still learning.

  • Value

    Numbers don't lie!

    One of our mottos at Enterprise Data & Analytics is, “Deliver Value.” I can hear you thinking, “That’s nice, Andy. What does that mean?” I’m glad you asked.


    Let’s start with what value is not, shall we?

    Value is not the least expensive. As a consultant, I often “bid” for consulting work, sometimes referred to as “gigs.” How does bidding work? Someone calls or emails. I usually set up a meeting to discuss the problem they are trying to solve. I listen – a lot. I inform the potential customer whether I can help or not. Usually, if I cannot help, I know someone who can; and often I can subcontract someone who can help. Next we talk about hourly rate.

    Let’s talk about hourly rate. And experience.

    I once had a conversation with a customer that went something like this:

    Customer: “We would like for you to help us develop a business intelligence and analytics solution.”

    Me: “Cool. I can help.”

    Customer: “So what is your hourly rate?”

    Me: “$___ per hour.”

    Customer: “Wow. We can hire several people to help with our business intelligence and analytics for that rate!”

    In this instance (and several similar instances), the client opted to hire several people at a lower rate. In this instance (and several similar instances), the client called back later and asked if I had some availability to help them. Why? Experience. The consultants they hired at the lower rate did not deliver. I’ve done this several times before. I know what to expect, and I recognize the unexpected.

    The value of that last phrase is not to be underestimated.

    Having experience means I immediately recognize something new and different. I raise the flag. Having experience also means I know what to expect. Experience often translates into saved time. My hourly rate may be double the competition, but I know how to deliver major portions of the project in 1/4th the time. (Some portions I know how to deliver in 1/100th of the time.) If I’m able, at (hypothetically) $300/hour, to deliver some aspect of the project in 25 hours; and the lower-rate consultants, at (hypothetically) $150/hour, are able to deliver the same functionality in 100 hours; which of us is the better value?

    Let’s do the math.

    $300/hour * 25 hours = $7,500

    The “less expensive alternative”:
    $150/hour * 100 hours = $15,000

    Which of us is the better value, me or the less expensive alternative? To quote Foghorn Leghorn, “numbers don’t lie.” Foghorn is correct. I am the better value, even though I charge more per hour.

    But Wait, There’s More

    Lots more. You see, when a project is under development everyone is laser-focused on the costs of development. Why? Well, these costs are right there in front of everyone. The math is easy, it’s the number of hours invoiced multiplied by the number of hours. But is the cost of development the highest cost of a project?

    The answer is no. Most business intelligence, data warehouse, or analytics projects are used in the enterprise for five to ten years. In my experience, the costs of maintaining and supporting are often more than the costs of developing the solution in the first place.

    If you read that last paragraph and thought, “Of course you’re going to write that, Andy! You want us to hire you instead of your competition who charges a lower hourly rate!” If you thought that, don’t hire me. Hire someone you trust. Your data, in 2016, needs a consultant you trust. Your customers need a consultant you trust. The people behind the personally-identifying information (PII) in your databases need a consultant you trust. In my opinion (again, subjective), integrity should be your number one consideration when selecting a consultant for a data project. Please hire someone you trust. If that’s not me, I will understand.

    The combined costs of developing, supporting, maintaining, and extending a solution is called the total cost of ownership, or TCO.

    The costs of supporting, maintaining, and extending the solution are spread across the years the solution is in production. The individual costs are small – especially when compared to the hourly rate of a consultant – but they are manifold. Over time these costs can, and most often do (in my experience), overtake the costs of development. Designing for supportability, maintainability, and extensibility can save thousands of dollars (sometimes orders of magnitude more) in TCO.

    It’s not just costs of supporting, maintaining, and extending the solution, though. Think about the opportunity cost – the cost of opportunities lost because your team is spending extra time fiddling with this solution – when they could be thinking up killer applications and solutions that will make you a go-zillionaire!

    I design for supportability, maintainability, and extensibility. To design for TCO, one needs experience supporting, maintaining, and extending data-related projects. Not everyone has that experience. Some brilliant consultants have never led or managed a team or project from inside a large enterprise. I led a team of 40 ETL developers when I worked at Unisys. I can tell you, experience managing a team of developers is very different from being an independent consultant.


    Because the money for development projects usually comes out of a different accounting bucket (the capital budget) and support, maintenance, and extending projects comes out of the operations budget, it’s understandable that TCO is often overlooked and development costs are often over-scrutinized.

    Please consider experience and the total cost of ownership when selecting a consultant. You’ll be glad you did.


    Related Training:
    IESSIS1: Immersion Event on Learning SQL Server Integration Services - April 2017, Chicago

    Learn More:

    Biml Academy
    The Basics of Biml – the Execute SQL Task
    The Basics of Biml – Populating the Biml Relational Hierarchy
    Stairway to Biml
    Stairway to Integration Services
    SQL Server Central

    Need help or training implementing a Biml solution?
    Contact Enterprise Data & Analytics today!

More Posts Next page »

This Blog



My Companies

Community Awards

Friend of Red Gate

Contact Me


Privacy Statement