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
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.
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.
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.
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):
The screenshot above shows me deploying MedicalDataDemo. Right-click the project name – MedicalDataDemo – and then click Deploy (click to enlarge):
This opens the Integration Services Deployment Wizard (click to enlarge):
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):
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):
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):
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:
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.
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.
SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy
Previous Posts in this Series:
Related Training: SSIS Lifecycle Management (free recording, registration required) IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago
An Example of Data Integration Lifecycle Management with SSIS, Part 0
IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago
From Zero to Biml - 19-22 Jun 2017, London
I 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.
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).
SSIS Catalog Management – 10 Jan 2017
Advanced SSIS Execution – 24 Jan 2017
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
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 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:
- You ROCK! Wearing many hats is hard. I’ve been there, done that, and have the blood-, sweat-, and tear-stained t-shirt.
- Do whatever works for you.
- 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.
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.
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.
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
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
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>
2016 was a year of lessons learned for me. I am looking forward to 2017!
Happy New Year!
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.
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.
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, Day 1, Day 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.
Click here for an overview of updates included in SQL Server vNext CTP 1.1 and click here to download the bits for Windows.
This post is inspired by Kenneth Fisher’s [blog | @sqlstudent144] post, An interview with me. Ken suggested,
“Now if any other bloggers read this and feel like answering questions too I’d love to read them so make sure you put a link to your blog down in the comments below.”
1. What are the various database job roles and respective hiring requirements at your company?
Enterprise Data & Analytics (EDNA) is a consulting company. We hire people with different levels of experience ranging from intermediate skills to experts. Intermediate skills usually means 1-3 years experience, depending on the type of experience. Experts write books, write blog posts, and/or present at SQL Community events. One goal is to help people grow from their current skill level to senior or expert.
2. Describe an entry level job and the hiring requirements at your company?
Although EDNA hires experienced people, we will consider any applicant. I shared some advice about careers in this post. Maybe it’ll help…
3. What is your job role?
My official title is Data Philosopher which I equate to Grand Poobah from The Flintstones (without the cool hat… although I could totally rock a Grand Poobah hat…). I own EDNA and a software development company named Andy Leonard Consulting. Why does the name of my software development company contain the word “Consulting” while my consulting company doesn’t? That’s another story…
4. What is your background?
Triflin’-kid-with-a-big-mouth, Long Haired Country Boy, farmer, student, tobacco puller, computer hobbyist, hay bailer, peach picker, sax player, guitar picker, truck driver, stockyard hand, soldier, electronics tech, TOW and Dragon (tank-killer) missile guidance systems tech, alarm and vault systems tech, electrician (manufacturing only, I don’t do houses), electrical contractor (ditto), engineer, entrepreneur, husband, dad, believer, instructor, divorcee, husband (again), dad (some more), granddad, software developer, author, blogger, technical community person, consultant, manager, ETL/SSIS architect, co-founder, leader, follower, failure, quitter, re-starter.
5. Describe the path that led you to this job role.
I started tinkering with Motorola machine code in 1975. I learned BASIC and computers were a hobby until I became an electrician at a manufacturing plant. In that role my hobby and day job started to merge. In the 90’s I owned a business that designed, built, programmed, and installed electrical control systems for manufacturing machines. Some of the human-machine interface software acquired data from the control systems and stored it in databases. I earned the Microsoft Certified Solutions Developer certification back in the day (before .Net). I started doing data integration before I knew what it was called. The secret to my
success […whatever…] is:
Make the problem you are trying to solve give up before you do.
6. Give me an example of an interview question that you would ask an entry level applicant, and explain what you would look for in a response.
I like to open an interview with, “Tell me about your greatest failure as a human being.” I’m kidding. I loathe questions like that almost as much as I dislike “Why are manhole covers round?” – unless I’m interviewing for a job that requires working underground via manhole access, and some of the alarm systems tech work did require that, but I digress… I fish for mistakes, errors, and slip-ups when interviewing consultants. Why? First, it’s an integrity test. I cannot work with people I can’t trust. Second, as a consultant (and human), you are going to fail. I’d like to hear that you’ve already failed (and lived through it) because I’d rather you already know how to deal with (and, Lord willing, recover from) failure. I’d also like to know you’re not too ashamed or too afraid to tell me about failure. I’ve made mistakes. I will make more. I’m not going to judge you for failing. Your failures aren’t going to scare me off, most likely. I’ve probably done worse. And if you’re working for me and make a mistake, I’m going to want to know about it so we can fix it.