THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is 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 Example of Data Integration Lifecycle Management with SSIS, Part 2

    In this post I will demonstrate how to use the SSIS Catalog to execute and monitor an SSIS project.

    Manual SSIS Package Execution in the SSIS Catalog

    To execute an SSIS Package manually using the SSIS Catalog, connect to the SQL Server instance that hosts the Catalog using SQL Server Management Studio (SSMS). Once connected, open Object Explorer and drill into the Integration Services Catalogs node until you locate the SSIS package you desire to execute. I’m going to execute the ProviderGetFile.dtsx package. Right-click the package and click Execute (click the image to enlarge it):

    dilm_17

    The Execute package window displays. For this SSIS project, please pay particular attention to the MedicalDatabaseConnectionString parameter value (click the image to enlarge it):

    dilm_18

    If you downloaded the demo project, it includes a SQL Server 2016 backup of a database named Medical. Restore the Medical database to a test instance of SQL Server 2016 (or higher) before proceeding. Then follow these steps to update the MedicalDatabaseConnectionString parameter value.

    1. Click the ellipsis to the right of the MedicalDatabaseConnectionString parameter value (click the image to enlarge it):

    dilm_19

    In the Edit Literal Value dialog, edit the connection string in the Value property. Set the Data Source portion of the connection string to the SQL Server instance to which you restored the Medical database (click the image to enlarge it):

    dilm_20

    Click the OK button and your Execute Package window should now reflect an overridden value for the MedicalDatabaseConnectionString parameter value (click the image to enlarge it):

    dilm_21

    Values that are overridden with Literals are shown in bold font.

    Monitoring Using the SSIS Catalog Reports Solution in SSMS

    Click the OK button to begin executing the SSIS package. You will be prompted with a dialog similar to that shown below. Click the Yes button to view the Overview report:

    dilm_22

    The Overview report contains information about this execution of this SSIS package (click the image to enlarge it):

    dilm_23

    The ProviderGetFile.dtsx package takes some time to execute. On my virtual machine, it took about 5.5 minutes (click the image to enlarge it):

    dilm_24

    Monitoring Using the Catalog Reports Solution from DILMSuite

    I really like the SSIS Catalog Reports that are built into SSMS. They are informative and intuitive. I have one complaint, though, and that is that the reports are built into SSMS. Why is that an issue? Some people in the enterprise have a legitimate need to view these reports. As you can see by viewing the screenshots above, the Overview Report can provide operational and historical data regarding the execution of an SSIS package. This information is vital for enterprise data integration.

    But SSMS contains other functionality that is, frankly, not vital for many consumers of the Overview Report. The additional nodes highlighted in the screenshot below, for example, are unnecessary for people in non-database-savvy roles within an enterprise.

    One may argue – fairly, even – that enterprise security can and should manage access in such a way that users who access the Overview Report do not have access to the highlighted nodes in the following screenshot. We are in violent agreement. But… must we surface these nodes and their accompanying functionality to users who only need to view the Overview Report?

    dilm_26

    One of the first Data Integration Lifecycle Management (DILM) Suite solutions I built was Catalog Reports. Catalog Reports is a relatively simple and straightforward version of some of the SSIS Catalog Reports embedded in SSMS. The main difference is Catalog Reports is a SQL Server Reporting Services (SSRS) solution.

    It’s free.

    And it’s open source. Here’s a screenshot of the Overview Report for the same execution above (click the image to enlarge it):

    dilm_27

    As you can see by mere observation, the reports are not identical. Most of the information included in the SSMS-version of the Overview Report is, however, present in the DILM Suite version. Plus it’s open source, so if you’d like for the report to surface more data or appear differently, you have the source.

    Best of all, the DILM Suite version provides no additional access to SQL Server functionality. Must you still manage and maintain SQL Server security? That’s a silly question and the answer is, “Of course!” DILM Suite’s Catalog Reports helps by surfacing only data and functionality required for enterprise data integration reporting.

    Conclusion

    In this post I demonstrated how to use the SSIS Catalog to execute and monitor an SSIS project.

    :{>

    You might like working with Enterprise Data & Analytics because we like helping people understand the SSIS Catalog.

    Learn More:
    SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy
    SSIS Academy: Using the SSIS Catalog Day 2 - Package Execution and Monitoring

    Previous Posts in this Series:
    An Example of Data Integration Lifecycle Management with SSIS, Part 0
    An Example of Data Integration Lifecycle Management with SSIS, Part 1

    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
    From Zero to Biml - 19-22 Jun 2017, London

  • Introducing astreams

    astreams_0

    For months, I’ve received pings about a screenshot I posted on social media a year or so ago.

    Plus I needed to wind down. You real runners will know what I mean: You know how you feel after you’ve covered some distance (for you)? You need to walk around for a bit, you can’t just stop cold. You need to wind down. I needed that after the effort to release SSIS Catalog Compare v2.

    astreams became the coding-equivalent of a wind down.

    I’d been using another application that kind-of-sort-of did what astreams does but astreams is cleaner, in my opinion.

    So, how does one use astreams? Open it. It’s empty:

    astreams_1

    Right-click in the treeview and click Add Stream to begin adding a new stream:

    astreams_2

    Type the name of the new stream:

    streams_14

    Press Enter when done:

    astreams_4

    Right-click any stream to add a sub-stream:

    astreams_5

    As before, begin typing to enter the sub-string’s name:

    astreams_6

    Also as before, press Enter when done:

    astreams_7

    You can nest streams as deeply as you like:

    astreams_8

    You may equate streams with tasks. Check them off as you complete each stream:

    astreams_9

    When you’re done, you can save an individual stream by right-clicking and selecting “Save Stream File.” You can save all the streams loaded by right-clicking and selecting “Save Streams Folder”:

    astreams_10

    Select a folder and click OK:

    astreams_11

    Your streams are stored in JSON format in the selected folder:

    streams_12

    You can manage streams by copying, pasting, editing, and deleting the JSON files, if you’d like.

    As you can see by viewing one of my streams folders, I use the utility often:

    streams_13

    It is a simple and flexible utility. I find it very helpful for tracking what I’ve done and what I need to do.

    You may purchase astreams here.

    Enjoy!

    :{>

  • The Recording for SSIS Catalog Management is Available

    The recording for the Enterprise Data & Analytics webinar titled “SSIS Catalog Management” is now available (registration is required).

    In the SSIS Academy: Using the SSIS Catalog series, Kent and I walked through the SSIS Catalog functionality built into SQL Server Management Studio (SSMS). This webinar picks up where SSIS Academy: Using the SSIS Catalog, Day 3 – Configuration left off.

    In this webinar, Kent Bradshaw and I discussed and demonstrated some enterprise management shortcomings of the SSIS Catalog as it ships, and some of the tools and solutions we use to overcome those shortcomings.

    Learn More:
    SSIS Academy: Using the SSIS Catalog, Day 3 – Configuration

    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
    From Zero to Biml - 19-22 Jun 2017, London

    :{>

  • An Example of Data Integration Lifecycle Management with SSIS, Part 1

    In this post I will provide some details about the demo SSIS project. I will then focus on version control and deployment.

    What DILM Is and Is Not

    Data Integration Lifecycle Management (DILM) is not about data integration development.

    DILM is about everything else:

    • Configurations Management
    • Version Management
    • Deployment
    • Execution

    Although DILM is not about development, implementing DILM will impact the design of SSIS solutions.

    The remainder of this post focuses on obtaining the solution, getting some pieces of DILM in place, and deploying the SSIS project to the SSIS Catalog.

    The Setup

    I’m using SQL Server Data Tools 2016 (Visual Studio 2015) on a virtual machine named vmSql16. The VM’s operating system is Windows Server 2016.

    The Project

    My SSIS project is named MedicalDataDemo. It contains two SSIS packages of relative complexity: ProviderGetFile.dtsx and ProviderStage.dtsx. ProviderGetFile will attempt to connect to the CMS website and download the latest National Provider Index (NPI) zip file, then it unzips the file. (Yes, it does all this in SSIS using a couple/three snappy design patterns.) The file name and file status are stored in a database named Medical. The ProviderStage package reads the name of the file and loads its contents to a table in the Medical database. There’s a 2016 version backup of the Medical database included in the project, which you can download the project in this state from Dropbox here.

    Version Control

    If you download the project and open it in SSDT, you may get source control messages. I added the project to source control, then “unbound” it before zipping it and sending it to Dropbox. Hopefully that was enough and you won’t see those pesky source control prompts.

    That said, please use source control. If you do not have access to version control, create a (free) account at visualstudio.com. I use visualstudio.com version control for internal work; I’ve been using it since it was in beta. 

    “There are two types of developers, those who use source control and those who will.” – Andy, circa 2005

    Once source control is set up, SSDT Solution Explorer will add indicators about the state of the solution (click to enlarge):

    dilm__1

    Deployment

    The screenshot above shows me deploying MedicalDataDemo. Right-click the project name – MedicalDataDemo – and then click Deploy (click to enlarge):

    dilm_0

    This opens the Integration Services Deployment Wizard (click to enlarge):

    dilm_1

    The first page of the Integration Services Deployment Wizard is an introduction. Click the Next button to open the next page in the process (click to enlarge):

    dilm_2

    Note that the “Select Source” page is skipped. That’s intentional; the wizard knows you’re deploying from SSDT.

    On this page you need to do some work. First, select a SQL Server instance that hosts an SSIS Catalog. If you’re scratching your head and asking, “What’s an SSIS Catalog?” that’s ok. Please see SSIS 2016 Administration: Create the SSIS Catalog for more information about setting up an SSIS Catalog.

    Click the Next button to open the next page in the process (click to enlarge):

    dilm_3

    The Review page contains some helpful information. Did you know you can execute SSIS project deployments from the command line? You can, and the arguments portion of the command line are shown on the Review page of the Integration Services Deployment Wizard. Click the Deploy button to deploy the project and open the next page in the process (click to enlarge):

    dilm_4

    Once deployment is complete, the Integration Services Deployment Wizard should appear as shown above.

    If you open SQL Server Management Studio (SSMS) and connect to the SQL Server instance to which you deployed the project, you can expand the Integration Services Catalogs node and drill down to the SSIS project you deployed:

    dilm_6a

    The SSIS packages in our project are now ready for execution, but they will execute with the default values we configured at design time. Our next step is to set up some external configurations.

    Conclusion

    In this post we discussed the demo SSIS project, version control, and deployment. In the next installment, I will demonstrate how to use the SSIS Catalog to execute and monitor these packages.

    :{>

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

     

    Learn More:
    SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy

    Previous Posts in this Series:
    An Example of Data Integration Lifecycle Management with SSIS, Part 0

    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 
    From Zero to Biml - 19-22 Jun 2017, London
  • Announcing SSIS Catalog Compare and CatCompare Version 2

    CatCompareI am pleased to announce the release of SSIS Catalog Compare and CatCompare version 2. You can purchase a bundle that includes both products (and saves you some money) or can purchase each product separately: SSIS Catalog Compare and CatCompare.

    If you’re interested in learning more about the functionality provided by the products you can download (or view) the documentation for free.

    A Couple CatCompare Examples

    Two of my favorite chunks of functionality are the CatCompare commands: Deploy Folder Differences and Deploy Catalog Differences.

    Deploy Folder Differences

    Deploy Folder Differences can be executed after you load a couple Catalogs into the CatalogBase (an object I built to model the SSIS Catalog) server objects. They must be compared to detect the differences, and then the differences in one Catalog Folder in a Catalog instance can be overwritten by the version in the other Catalog’s Folder. The following command line accomplishes that, redirects the output to a text file, and then exits:

    "C:\Program Files\DILMSuite\SSISCatalogCompare\CatCompare.exe" "-server0;vmSql16\Dev" "-server1;vmSql16\Test" "-comp" "-deployfolderdiffs;\vmSql16_Dev\IS\SSISDB\Folders\Demo\|0|\vmSql16_Test\IS\SSISDB\Folders\Demo>E:\Test\CatCompare_DeployFolderDiffs_Results.txt" "-exit"

    The command line above loads the Catalogs hosted on the vmSql16\Dev and vmSql16\Test SQL Server instances. The catalogs are compared, and then the differences identified by the compare operation between the Demo folder in each Catalog are deployed from the vmSql16\Dev Demo folder to the vmSql16\Test Demo folder.

    Why do I like this command? I can run it every night to collect the changes a data integration developer deployed to her Dev Catalog, deploying the updates (only) to an integration SSIS Catalog instance. You know, just like the C# developers when they practice DevOps.

    Deploy Catalog Differences

    The Deploy Catalog Differences command will help keep two Catalogs in sync:

    "C:\Program Files\DILMSuite\SSISCatalogCompare\CatCompare.exe" "-server0;vmSql16\Dev" "-server1;vmSql16\Test" "-comp" "-deploycatalogdiffs;0>E:\Test\CatCompare_DeployCatalogDiffs_Results.txt" "-exit"

    As before, two Catalogs are loaded and compared. Things that are different in server0 (vmSql16\Dev) are deployed to server1 (vmSql16\Test). The results are redirected to a file and the utility exits.

    Why do I like this command? I can use it to keep a “warm copy” of the Test SSIS Catalog somewhere else in the enterprise. This is handy if two (or more) data integration teams are developing updates to coupled projects.

    Conclusion

    My long-term goal with DILM Suite products is to facilitate DevOps and Data Integration Lifecycle Management with SSIS.

    Kent Bradshaw and I will be demonstrating SSIS Catalog Compare, CatCompare, and more DILM Suite utilities in the upcoming (free!) webinars: SSIS Catalog Management (10 Jan) and Advanced SSIS Execution (24 Jan).

    :{>

    Learn More:
    SSIS Catalog Management – 10 Jan 2017
    Advanced SSIS Execution – 24 Jan 2017

    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

  • Save Time and Improve SSIS Quality with Biml, A Free Webinar

    Join me at 1:00 PM EST 31 Jan 2017 for Save Time and Improve SSIS Quality with Biml, another free webinar from Enterprise Data & Analytics!

    “Save Time and Improve SSIS Quality with Biml” introduces Business Intelligence Markup Language (Biml) to SSIS developers, DBAs, sysadmins, and others who want to learn more about automating SSIS development.

    Automation (or code generation) improves the quality of SSIS solutions while saving time. With Biml, you can build lots of SSIS packages faster than building them manually. Because Biml generates SSIS packages from a template human error is mitigated. Therefore, the quality of the SSIS packages improves.

    I look forward to seeing you there.

    Register today!

    :{>

    Learn More:
    Biml Academy

    Related Training:
    From Zero to Biml - 19-22 Jun 2017, London
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago

  • Presenting “Using Biml as an SSIS Design Patterns Engine” at the (605) SQL Server User Group 10 Jan

    I am honored to present – albeit remotely – Using Biml as an SSIS Design Patterns Engine to the (605) SQL Server User Group in Sioux Falls, SD Tuesday 10 Jan 2017!

    Perhaps you’ve heard the buzz about Business Intelligence Markup Language (Biml) and wondered, “Why is everyone so excited about Biml?” Occasionally, a new technology emerges that changes everything. For SSIS developers, Biml is one such technology. Business Intelligence Markup Language (Biml) is a powerful solution for rapidly creating SSIS packages and solutions. SSIS Design Patterns support various load profiles. Combined, Biml and SSIS Design Patterns offer a compelling solution for automating enterprise data integration. In this session, you will learn: -How to build and execute your first Biml file! -How to design a single SSIS package using Biml -How to rapidly build multiple SSIS packages using Biml.

    :{>

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

    Learn More:
    Biml Academy 
    The Basics of Biml – the Execute SQL Task
    The Basic of Biml – Populating the Biml Relational Hierarchy

    Related Training:
    From Zero to Biml - 19-22 Jun 2017, London
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago

  • Free Webinar–Advanced SSIS Execution–24 Jan 2017

    Join Enterprise Data & Analytics for Advanced SSIS Execution, a free webinar starting at 1:00 PM EST 24 Jan 2017!

    Kent and I demonstrated how to use the SSIS Catalog in the SSIS Academy series (see links to the recordings in the Learn More section). In this webinar, we share several ways to execute SSIS packages.

    Register today!

    :{>

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

    Learn More:
    SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy
    SSIS Academy: Using the SSIS Catalog Day 2 - Package Execution and Monitoring
    SSIS Academy: Using the SSIS Catalog Day 3 - SSIS Configuration

    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
    From Zero to Biml - 19-22 Jun 2017, London

  • Free Webinar–SSIS Catalog Management–10 Jan 2017

    Join Enterprise Data & Analytics for SSIS Catalog Management, a free webinar starting at 1:00 PM EST 10 Jan 2017!

    Kent and I demonstrated how to use the SSIS Catalog in the SSIS Academy series (see links to the recordings in the Learn More section). In this webinar, we share best practices, tips, and tools for using the SSIS Catalog in the real world.

    Each attendee will receive a free bit set-able to the value of their choice – available in four states. Seating is limited so register today!

    :{>

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

    Learn More:
    SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy
    SSIS Academy: Using the SSIS Catalog Day 2 - Package Execution and Monitoring
    SSIS Academy: Using the SSIS Catalog Day 3 - SSIS Configuration

    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
    From Zero to Biml - 19-22 Jun 2017, London

  • An Example of Data Integration Lifecycle Management with SSIS, Part 0

    What is Data Integration Lifecycle Management (DILM)?

    Here’s one way to think about DILM:

    Data Integration Lifecycle Management (DILM) is applying software Application Lifecycle Management (ALM) best practices to Data Integration development and operations (DevOps), version control, release management, and configuration.

    I can hear you thinking, “But Andy, why would we apply software best practices to a data integration platform like SQL Server Integration Services (SSIS)?” I’m glad you asked. The answer is: “Because SSIS development is software development.”

    SQL Server Integration Services suffers from having the name of a popular relational database engine – SQL Server – baked into its name. Don’t let that throw you, SSIS development is software development. Yes, SSIS packages are tightly-coupled to data sources and destinations, and to the data the packages move.

    I Object!

    I hear from many people who wear several hats in small shops. Many of them object to my thoughts about DILM for SSIS. If you are one of those people who wear many hats and object to my thinking about DILM, I want to say two things to you:

    1. You ROCK! Wearing many hats is hard. I’ve been there, done that, and have the blood-, sweat-, and tear-stained t-shirt.
    2. Do whatever works for you.
    3. BONUS 3rd thing: You may find some benefit from DILM practices if you give them a shot.

    Who Needs to Practice DILM?

    It depends on  the problem(s) you are trying to solve. Here are a few questions to help you determine your need for implementing DILM:

    • Have you ever lost code?
    • Has a server ever crashed and part of the solution involved re-developing code?
    • Have you ever received a phone call from work while on vacation?

    If you answered, “Yes,” to any of these questions, implementing some flavor of DILM may help.

    This Series

    I don’t have all the answers, so I cannot possibly provide all the answers to you. What I can do, though, is share some things I’ve learned implementing SSIS solutions for the past decade. I’ve led a team of 40 ETL developers building multiple enterprise-class projects simultaneously. I’ve parachuted into enterprises on fire as a lone wolf consultant and helped douse the flames. I've joined teams and formed teams to solve enterprise data integration problems. This breadth of experience has taught me priorities that are different from the priorities of some of my compatriot SSIS professionals.

    You may read some of my thoughts and think, “That’s overkill.” To which I will respond, “Yeamaybe.” I understand. Really I do. As I wrote to the small-shop-people, do what works for you.

    I want to tell the story of a data integration project, to follow it through its lifecycle as it starts, matures, and grows. Although the project is interesting, we will focus on lifecycle. I hope everyone finds some value in this series for that is my goal.

    :{>

  • Three Lessons Learned in 2016

    Some folks do not like to read posts of a more personal nature or posts that contain religious references. If you are one of those people, you have been forewarned.

    I’ve read lots of posts from people who are ready for 2016 to be over. I understand their reasons. Celebrities, friends, and family have passed away this year. The world economy stumbled through another four quarters. About 51% of US voters were disappointed by results of the 2016 US Presidential Election (which are still being updated at the time of this writing, by the way).

    I learned some things in 2016 and would like to join the chorus of sharing as 2016 draws to a close.

    New Businesses

    Isaiah 61:3

    In 2015 I left Linchpin People and founded Andy Leonard Consulting. Shortly thereafter I was having lunch with my friend Nick who said, “‘Andy Leonard Consulting’ sounds like a one-person operation.” “There’s a very good reason for that,” I replied. Before we finished lunch, Nick and I agreed to work together on a new venture called Enterprise Data & Analytics, or EDNA. EDNA officially launched in January 2016.

    I kept Andy Leonard Consulting around and repurposed it as a software development company. ALC built and manages the Data Integration Lifecycle Management Suite (or DILM Suite), a collection of mostly-free utilities that facilitate my vision of enterprise data integration DevOps and Continuous Integration (CI) for SSIS.

    If you’re playing along at home, I operate two businesses: Andy Leonard Consulting (ALC) and Enterprise Data & Analytics (EDNA). ALC has “consulting” in the name but does software, not consulting. EDNA does consulting. Confused? Me too…

    Lesson: Entropy happens. Without an anchor you’ll either drift – or be blown – away. My anchor is faith in Christ. I’m reminded of Isaiah 61:3 that states God gives “beauty for ashes.” Some translations render this passage “God makes beauty from ashes.” A chunk of my life burned down in 2015. I am astounded at, overwhelmed by, and thankful for what God is making from these ashes.

    Becoming a Better Listener

    On Being Wrong

    I started listening to audio books in 2016. I have listened to audio books in the past, but I focused on listening to them in 2016. I like Audible for a number of reasons:

    • I pay ~$15/month and get 1 credit that can be used to access one book.
    • I can accumulate 6 credits over 6 months if I do not use them, so if I go through a period of not accessing audio books I don’t start losing credits until the seventh month.
    • Audible’s cloud integration means I can listen on my laptop or phone, picking up right where I left off on the other device.

    My taste in audio books is different from my taste in books I read. I’ve tried to listen to books for entertainment but I cannot enjoy them. I don’t know why this is so. I enjoy listening to audio books about theology and business. While I enjoy reading books about theology and business and science fiction, I’ve mostly shifted to reading sci-fi and listening to theology and business books.

    I prefer audio books read by the authors. Why? To me, they’re more… real. The author knows where to inflect for emphasis. Sometimes the author will throw “extras” into the audio book that are not in the written edition. The best business book I heard in 2016 is an example: Grant Cardone injected dozens of thoughts into the audio book The 10X Rule that did not appear in the print version. All of them are great and some of them are hilarious! 10X is a book about success. I confess some confirmation bias in recommending this book; I’ve long believed and stated that there’s no substitute for hard work. I assigned this book to my older son, Stevie Ray, as a homeschool reading assignment. That’s how much I liked it.

    My friend and brother Frank La Vigne (blog | @tableteer) recommended this book – thanks Frank!

    Listening to audio books like 10X has improved my listening skills. It turns out that listening to the words that people who live with you and work with you are saying and writing to you is a good idea (who knew?). Listening is an especially good idea for me because I am a hard-headed and triflin’ redneck (Can I get an amen?). I mentioned confirmation bias earlier because I’m more guilty of it than most. Kathryn Schulz’ TED Talk On Being Wrong – and the book – smacked me upside the head. The TED Talk is just under 18 minutes. I encourage you to invest 18 minutes listening to her. She’s brilliant. Will it help if I link to her TED Talk again?

    Lesson: Listen. Listen more and listen well.

    There’s More to Learn

    Pluralsight

    I’m going to confess something. I’m going to be vulnerable (another great TED Talk by Dr. Brene Brown – 20 minutes – we’re up to 38 minutes of assigned TED Talks now…). SSIS Catalog Compare is the first product I’ve written. It’s also the first full application I’ve attempted writing in C#. I’ve been writing software since 1975 so I am familiar with the practice of developing code. Although I’ve built applications in the past, I’ve never built a soup-to-nuts product and I’ve never used C# to do so; I’ve only used C# to build code snippets.

    Catalog Compare grew out of an epiphany while attempting to write a GUI to manage an SSIS Framework. An SSIS Framework can simplify your enterprise data integration. You can start the execution of a collection of SSIS packages with a single command. Awesome, right? What’s not to love? Well… there’s no free lunch. In order to accomplish this magic, an SSIS Framework relies upon a boat load of metadata. SSIS execution, whether or not one uses a Framework, also relies on even more metadata stored in the SSIS Catalog. I was building this interface to manage Framework metadata when I realized there’s no easy way to manage most of the metadata stored in the SSIS Catalog.

    So I wrote an app for that. In C#.

    Why? I found a problem that I wanted to solve. And I wanted to learn C# – really learn it. So what did I do? I committed to building SSIS Catalog Compare in C#. I knew I would need help. Fortunately for me, I’m surrounded by awesome friends who are literally masters of software development and C#. Two friends in particular, Scott Currie (owner of Varigence, inventor of Biml, all-around nice guy) and Kevin Hazzard (blog | @KevinHazzard), listened to me and suggested improvements and next steps without laughing (to my face) at my code. With their help and help from Google and Pluralsight, I learned more C# in 2016 – enough to release SSIS Catalog Compare in August.

    Thank you, Scott and Kevin.

    Lesson: Keep learning.

    Post-note: As I type this, SSIS Catalog Compare v2 is nearing release. There’s a free “view-only” utility based on Catalog Compare functionality (it’s actually a subset of the Catalog Compare codebase) called SSIS Catalog Browser. After the Catalog Compare v2 release I return to developing that Framework Manager GUI I started coding in August 2015. There’s a “view-only” version of this yet-to-be-built application available today, for free. It’s called SSIS Framework Browser and it works with the SSIS Framework Community Edition which is not only free, it’s open source! </ShamelessPlug>

    Conclusion

    2016 was a year of lessons learned for me. I am looking forward to 2017!

    Happy New Year!

    *<:{>

  • My Top Blog Posts for 2016

    KrispyKremeHotNow
    (with a nod to @KrispyKreme)

    I’d like to wish everyone reading this post a blessed, peace-filled, and happy 2017! I would also like to thank you for reading this post and any other posts you may have read.

    Some Blog Post Stats

    I published 87 blog posts this year at SqlBlog.com. I published most in December (12) and least in October (2).

    These posts attracted more reads than the others (ordered by most reads in descending order):

    1. Installing SQL Server 2016 Developer Edition, One Example
    2. SQL Server 2016 Developer Edition is Free
    3. Announcing Biml Academy!
    4. PASS Board Elections–Voting is Open!
    5. SQL Server Developer Edition is FREE!
    6. Biml Academy 2 Webinar Recordings are Available!
    7. Microsoft is Listening
    8. A Couple-Three Thoughts and Questions About Swag at Community Events
    9. A New Version of SSDT is Available
    10. BI’s Not Dead

    Is This Accurate?

    But… Some of those posts have been around a lot longer than others. The oldest post in this list is 325 days old (at the time of this writing). The newest post is 85 days old, the average “age” of these posts is 210 and the median “age” is 215 days. 

    How does a Data Philosopher account for this? I computed the age of each post and then divided the read count by that number. The results now look like this:

    1. The Recordings for SSIS Academy: Using the SSIS Catalog are Available
    2. SQL Server vNext CTP 1.1 is Available!
    3. SQL Server Management Studio (SSMS) v16.5.1 Now Available
    4. Learn More About the SSIS Catalog
    5. Three Free Webinars About Using the SSIS Catalog
    6. An Interview With Me
    7. DLM (Database Lifecycle Management)
    8. SSIS Catalog Browser Update
    9. Broken References in the SSIS Catalog
    10. New Versions of SSMS and SSDT Available

    Is This Accurate?

    But… This second list is skewed towards the newer posts due to the lower denominator. The newest post is 2 days old (and it’s #1) while oldest post in this list was published 43 days ago (at the time of this writing). The median “age” of the posts in this list is 16 days and the average “age” is 18.

    Noodling…

    I suspect both lists are valid because readership declines after the post scrolls off the front page at SqlBlog.com (which displays the latest 20 posts). I write “both lists are valid” because these results (like all results in data analytics) must be considered in context. Context is defined by answering the question, “What is the problem we are trying to solve?” There are other ways to analyze this data – lots of other questions we can ask of it. Data science is science and science (well, good science, anyway) involves experimentation.

    My theory: The first list contains the posts that experience some “longevity in appeal” (or SEO). The second list is “Hot Now.” Or not. I’d love to hear your thoughts.

    Happy New Year!

    *<:{>

  • Predictions for 2017

    I’m hesitant to make predictions for 2017 because I’ve read the parts of the Old Testament that deal with prophets whose predictions did not come to pass. Perhaps a better title for this post is “Hopes for 2017” or “Thoughts for 2017”.

    The Cloud Will Grow

    (Filed under “DUH!”) 2016 saw a number of data services introduced and improved in the cloud. I hope the propagation of economies of scale accelerate in 2017; that the cost-savings will continue and continue to be passed onto the end-users. I heard some complaints in 2016 about the costs of some cloud-based services. While I concur that the costs of some services seem high with some bordering on extravagant and some crossing that border, I see pricing as an effective throttle while new services and offerings are maturing (see Supply and Demand).

    Automation Will Increase Operational Efficiency

    Automation allows one administrator or developer to do the work of many. One of the reasons the cloud will grow is automation. Having worked with computer technology for four decades, I’ve experienced firsthand the efficiency of automation. Automation includes tools that surface metadata and data collected by instrumentation. An entire industry selling support utilities exists and appears to be flourishing.

    This is a good thing.

    Why?

    Consider the past: Hundreds of years ago many spent a significant portion of each day seeking food for that day. Contrast that with today (in first world countries), where we spend minutes each day seeking food. IN the US we spend more time actually eating than searching for food. Not having to search for food frees time for other endeavors. The same can be said of database, network, and systems administration. Automation doesn’t actually create time, it allows us to repurpose time. One way we repurpose time is by administering other databases, networks, and systems. That makes each of us more efficient.

    Security…

    My friend and brother Brian Kelley (blog | @kbriankelley) preaches. To we geeks, he preaches about security. Another friend, Steve Jones (blog | @way0utwest) preaches the same message as Brian: Security matters. Data breaches remain too common. I expect more and worse, in part because people using bot-net attacks are ahead of the curve with automation. They are extremely efficient, using hundreds – sometimes thousands – of machines to do their bidding. As a matter of cosmic history, it has always been easier to destroy than to create. I would love to see these beautiful minds rise to the real challenge of creating.

    :{>

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

    The recordings for SSIS Academy: Using the SSIS Catalog, Day 1Day 2, and Day 3 are now available (registration required)!

    Day 1 focused on creating the SSIS Catalog and SSIS deployment.
    Day 2 focused on SSIS Execution and Monitoring Wednesday.
    SSIS Configuration was the topic covered on Day 3.

    :{>

  • Three Free Webinars About Using the SSIS Catalog

    Mr. Kent Bradshaw and Llian the Wonder PuppyKent Bradshaw and I are delivering a series of webinars 27-29 Dec 2016 called SSIS Academy: Using the SSIS Catalog.

    Why? We’ve been using the SSIS Catalog in Production since 2012. We’ve learned a lot about how it works. Some parts of the SSIS Catalog are complex and we want to share – as best we can – what we’ve learned.

    The daily agenda is:

    Day 1: SSIS Catalog Introduction and Deployment – We discuss and demo creating an SSIS Catalog and deploying SSIS Projects to the SSIS Catalog.
    Day 2: SSIS Execution and Monitoring – We cover executing SSIS packages using the Catalog, and monitoring execution reports. We will also demo alternatives to Catalog-based package execution.
    Day 3: SSIS Configuration – No matter which method or tool you select to manage externalization, configuration is complex. In this session, Kent and I discuss and demo SSIS Catalog Environments, References, and Reference Mappings to project and package parameters.

    Our intention is to remain “in the box:” We plan to only demonstrate and discuss how to use the SSIS Catalog as it ships. The purpose of this series is to help data integration professionals learn more about applying the SSIS Catalog to enterprise data integration.

    Sound interesting? Register today!

    :{>

    Learn More:
    Data Integration Lifecycle Management (DILM) Suite – (mostly) free – some 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!

More Posts Next page »

This Blog

Syndication

News


My Companies



Community Awards

Friend of Red Gate

Contact Me

Archives

Privacy Statement