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.

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

  • I’m Presenting at SQL Saturday Charlotte (#560) Next Saturday!

    I’m honored and all giggly with anticipation about presenting at SQL Saturday 560 in Charlotte next Saturday, 17 Sep 2016! I’m presenting Biml and SSIS Frameworks, and I hope to see many of you there.

    For the SSIS Frameworks part of my presentation, I’m using SSIS Framework Community Edition, a free and open source SSIS Framework from Enterprise Data & Analytics. SSIS Framework Community Edition (non-invasively) integrates into the SSIS Catalog and allows you to execute a collection of SSIS Packages (even if those packages reside in different folders and projects in the Catalog) by executing a single stored procedure. How neat is that? You can find links to the source code and documentation at the SSIS Framework Community Edition page.

    Here’s hoping I see you in Charlotte next weekend!


    Related Training:
    IESSIS2: Immersion Event on Advanced SQL Server Integration Services

    Learn More:
    Biml Academy
    SSIS Framework Community Edition (FREE! and Open Source)
    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!

  • The Basics of Biml – the Execute SQL Task

    A couple years ago, I wrote a series of guest posts about the Basics of SSIS for my friend Pinal Dave’s [Blog | @pinaldave] popular blog, SQL Authority. In that series, I pointed out that I use only a handful of SSIS Control Flow tasks when designing SSIS packages. You can find the series here:

    In this post, I’m going to demonstrate and discuss building the Execute SQL Task using Biml.

    SQL SERVER - The Basics of the SSIS Execute SQL Task - Notes from the Field #044 ssise1

    “So how does one build an Execute SQL Task using Biml, Andy?” I’m glad you asked. First you have to build an SSIS package in Biml. But before you build an SSIS package in Biml, you need to populate the Biml Relational Hierarchy. To play along at home grab the 14-day free trial of Mist here and download the World Wide Importers sample database here. Then go learn how to populate the Biml Relational Hierarchy by reading this post: The Basics of Biml – Populating the Biml Relational Hierarchy. You’ll be glad you did.

    The Prerequisites

    I’m going to continue building out the demo Mist project named BimlBasics – the one I started in The Basics of Biml – Populating the Biml Relational Hierarchy. (I told you you’d be glad you read that post!)

    Create an SSIS Package

    You can create an SSIS package using the Mist GUI. But for this exercise, let’s build one from the ground up using Biml. In Logical View, right-click Library—>Utilities and then click Add BimlScript:


    I named my BimlScript file “TruncateAndLoad_People.biml”:


    If I double-click the file in Logical View, the BimlScript Input Editor displays my file contents:


    If you position your cursor at the end of line 1 and press the Enter key a new, properly-indented line begins. Type “<” to begin adding Biml to your file. Note the IntelliSense provides a list of options available at this level of the Biml object model:


    Double-click Packages to begin creating a Packages collection:


    Type the “close tag” symbol – “>” – and Biml automagically generates the close tag: </Packages>:


    Position your cursor between the opening and closing Packages tags and press the Enter key. A new properly-indented line appears:


    Type the “open tag” symbol (“<”) and IntelliSense provides a list of valid Biml objects. Type of click Package to proceed:


    Type a space at the end of the word “Package” followed by the letter “N.” Note the Name attribute is highlighted by IntelliSense:


    Enter “Package1” as the Name attribute value:


    Close the Package tag:


    Press Enter to start a new, properly-indented line. Use IntelliSense (or type) to add a “<Tasks>” tag:


    Inside the Tasks tag, add an “<ExecuteSQL>” tag:


    Add the ConnectionName attribute:


    Note IntelliSense supplies valid values – WorldWideImporters, in this case – to the attribute:


    Supply a Name attribute for the Execute SQL Task and close the tag:


    Inside the ExecuteSQL tag, next a DirectInput tag:


    In Logical View, right-click the Application.People table, hover over Copy SQL Script, and then click Select SQL:


    Position your cursor between the DirectInput tags and paste the clipboard contents:


    Look at all that well-formatted T-SQL! How neat is that?


    We’ve been ignoring this message at the top of the BimlScript Input Editor for a while now:


    Click it.

    Observe the Preview Expanded BimlScript window:


    That right there is your Package1.dtsx Biml.

    Next Steps

    You do not yet have an SSIS package. You have the Biml template of a package. I can hear you thinking, “So how does this become an SSIS package, Andy?” I’m glad you asked!

    We now need to execute the BimlScript and then build the output.

    Execute the BimlScript

    First, we execute the BimlScript file. In Logical View, right-click the TruncateAndLoad_People.biml file (found in Library—>Utilities) and click Execute BimlScript:


    The Biml template of an SSIS package has now been executed. It generated the Biml version of an SSIS package. In the image below, I positioned the Logical View and Project View side-by-side for comparison:


    We now have a Biml representation of the SSIS package named Package1.dtsx.

    Build the Package

    To generate the actual SSIS package, right-click Package1 (found in Logical View—>Integration Services—>Packages) and then click Build:



    The Output window displays the results of the Build operation:


    An SSIS project is generated in the output folder of the BimlBasics Mist project folder:


    The package was generated just as we specified:


    And it runs!



    In this post, I discussed and demonstrated how to configure an SSIS package that contains an Execute SQL Task, one of the handful of tasks I use when designing SSIS packages.


    Related Training:
    IESSIS2: Immersion Event on Advanced SQL Server Integration Services

    Learn More:

    Biml Academy
    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!

  • The Basics of Biml – Populating the Biml Relational Hierarchy

    In this post, I’m going to demonstrate how to build the objects Business Intelligence Markup Language (Biml) requires before creating anything – the Biml Relational Hierarchy. The Biml Relational Hierarchy provides the foundation for all relational interaction between packages, cubes, dimensions, facts, and T-SQL.

    It’s important to note that Biml is useful for generating SSIS and SSAS, but Biml can generate any text – which includes .Net code (I’ve used Biml to generate C#) – that is based on a database schema.

    To play along at home grab the 14-day free trial of Mist here and download the World Wide Importers sample database here.

    The Biml Relational Hierarchy


    Once you create a new project in Mist (mine is called “BimlBasics”), the Logical View displays an empty Relational hierarchy. Note that the relational hierarchy is flat. There are good reasons for representing the hierarchy thus, but they are beyond the scope of this post (but trust me, it’s a good and smart thing). One thing I will share in this post: The objects beneath the Relational node reside in the “RootNode” object in Biml. Reeves Smith [@SQLReeves] explains RootNode in this (excellent) article: Stairway to Biml Level 5 - Biml Language Elements at SQL Server Central.

    The “logical flatness” of these objects in RootNode make them easier to access and manipulate programmatically. One way to illustrate the logical flatness of the RootNode’s relational hierarchy is shown here:


    But, these objects – connections, databases, schemas, and tables (and columns) – are really part of a related hierarchy. Another way to think of the RootNode’s relational hierarchy is shown here:


    “But Andy, there’s no “Columns” node in the flattened presentation.” True. The columns are there, though; they’re inside the Tables as we will see in a bit.

    Populating the Relational Hierarchy

    There are two ways to populate the relational hierarchy:

    • Graphically
    • Programmatically

    In this post, I am going to cover graphically populating the relational hierarchy. Scott Currie [Varigence] provided a remarkable example of programmatically populating the relational hierarchy in his article Biml Basics for Relational DBs.

    Populating the relational hierarchy graphically is simple; just follow the steps as they are listed from top to bottom in the Logical View’s Relational node in the Mist IDE. Begin with Connections.


    Right-click the Connections node, hover over Add Connection, and then click OLE DB:


    The “OleDbConnection1” window displays. You can enter a connection string value in the Connection String textbox or build a connection string. To build a connection string, first select a Provider from the dropdown:



    Enter a database instance in the Server textbox:


    Select an authentication method and enter credentials (if needed):


    The Database Name dropdown is empty until you click the Update link:


    After clicking the update link, the Database Name dropdown is populated with a list of databases:


    You can test the connection by clicking the Test button:


    If the test is successful, you will see the following dialog:


    You can rename the Connection in Logical View by right-clicking the connection and clicking Rename:


    I renamed my connection “WorldWideImporters”:


    Logical View now reflects a configured Database. Let’s configure a Database.


    To add a database to the relational hierarchy, right-click Databases in Logical View, and then click Add Database:


    When the Database Properties window displays, Select the WorldWideImporters connection from the Connection property dropdown:


    Edit the Name property of the Database (I named mine WorldWideImporters). When you navigate away from the Name property textbox you are prompted to confirm you want to also rename the Database object’s Biml file:


    Click Yes.

    Logical View now reflects a configure Connection and Database:


    Let’s next configure a schema.


    To add a schema to the relational hierarchy, right-click Schemas in Logical View, and then click Add Schema:


    When the Schema Properties page displays, configure the Database property from the dropdown:


    Provide the name of the Schema in the Name property textbox (I chose the Application schema). When you navigate away from the Name property textbox, you are prompted to confirm you want to also rename the Schema object’s Biml file:


    Click Yes.

    Logical View now reflects a connection, database, and schema:


    Next, let’s import a table:


    There a couple ways to add a table to the relational hierarchy. If the table exists in the relational database, you can save time by importing the table. Right-click Tables in Logical View and then click Import Tables:


    When the Import Tables dialog displays, select the connection (WorldWideImporters) from the Source Connection dropdown:


    Note the Connection Established indicator changes from red to green when you successfully connect.

    Select the Project Database from the dropdown (WorldWideImporters):


    the Importable Assets treeview displays the available schemas, tables, and views in the WorldWideImporters database. I’m going to import a single table named “People”:


    Note that I could have skipped creating the Application schema in the Biml relation hierarchy as it is imported as part of the table import process. In this case, I choose to overwrite the schema object I created earlier with the imported version:


    The Logical View now displays a completed Biml relational hierarchy that includes a connection, database, schema, and table:



    I can hear you thinking, “Where are the columns, Andy?” I’m glad you asked. If you double-click the Application.People table, the graphical viewer will display the table designer, which displays many table objects including columns:



    Congratulations! You’ve just populated a Biml relational hierarchy.


    Learn More:

    Biml Academy
    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