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, developer of the Data Integration Lifecycle Management (DILM) Suite, 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 the Stairway to Integration Services.

  • An Interview With Me

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

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

    I’m in!

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

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

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

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

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

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

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


  • DLM (Database Lifecycle Management)

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

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

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

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

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


    You’ve Done a Good Job

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

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

    Automate (Please)

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

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

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

    How To Start

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


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


  • SSIS Catalog Browser Update


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

    And it’s free.

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

    New Features

    Catalog Properties

    Catalog Properties now display catalog metadata including:

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

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

    Virtual Folders Sort Order

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

    Package Properties

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

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

    TL;DR Regarding SSIS Package Version Build Property Values

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

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


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

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

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

    Enjoy the new version of SSIS Catalog Browser!


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

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

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

  • 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

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

    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, and then 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:

    That's not entirely accurate

    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!

This Blog



My Companies

Community Awards

Friend of Red Gate

Contact Me


Privacy Statement