Biml, Book Reviews, and Metadata-Driven Frameworks

I occasionally (rarely) read reviews at Amazon of books I’ve written. If I learn of a complaint regarding the book I often try to help. If a reader experiences difficulty with demos, I often offer to help, and sometimes I meet with readers to work through some difficulty related to the book (as I offered here). About half the time, there’s a problem with the way the book explains an example or the sample code; the other half the time the reader does not understand what is written.

I own both cases. As a writer it’s my job to provide good examples that are as easy to understand as possible. Sometimes I fail.

In very rare instances, I feel the review misrepresents the contents of a book –  enough to justify clarification. This review afforded one such opportunity. None of what I share below is new to regular readers of my blog. I chose to respond in a direct manner because I know and respect the author of the review, and believe he will receive my feedback in the manner in which it was intended – a manner (helpful feedback) very similar to the manner in which I believe his review was intended (also helpful feedback).

Enjoy.

My Reply to This Review of The Biml Book:

Thank you for sharing your thoughts in this review.

I maintain successful technology solutions are a combination of three factors:

  1. The problem we are trying to solve;
  2. The technology used to solve the problem; and
  3. The developer attempting to use a technology to solve the problem.

I believe any technologist, given enough time and what my mother refers to as “gumption” (a bias for action combined with a will – and the stubbornness, er… tenacity – to succeed), can solve any problem with any technology.

I find your statement, “It doesn’t really teach BIML but rather teaches a specific optional framework for those who want to do everything with BIML rather than increase productivity for repetitive patterns” inaccurate. Did you read the entire book? I ask that question because I most often encounter readers who state the precise opposite of the opinion expressed in this review (some of your fellow reviewers express the opposite opinion here, even). I disagree with your statement, but I understand your opinion in light of other opinions expressed to me by readers during my past decade+ of writing several books. I share one example in this blog post. The short version: we often get more – or different – things from reading than we realize.

There is a section in The Biml Book – made up of 3 of the 20 chapters and appendices – that proposes a basic metadata-driven Biml framework which is the basis of a metadata-driven Biml framework in production in several large enterprises. I wrote those 3 chapters and the basic metadata-driven Biml framework in question. Varigence has also produced a metadata-driven Biml framework called BimlFlex – based upon similar principles – which has been deployed at myriad enterprises. The enterprise architects at these organizations have been able to improve code quality and increase productivity, all while decreasing the amount of time required to bring data-related solutions to market. They do not share your opinion, although they share at least some of the problems (you mentioned ETL) you are trying to solve.

Am I saying Biml – or the framework about which I wrote or even BimlFlex – is the end-all-be-all for every SSIS development effort? Goodness no! In fact, you will find disclaimers included in my writings on Biml and SSIS frameworks. I know because I wrote the disclaimers.

Misalignment on any of the three factors for successful technology solutions – the problem, the technology, and/or the developer – can lead to impedance mismatches in application and implementation. For example, Biml is not a good solution for some classes of ETL or data integration (points 1 and 2). And learning Biml takes about 40 hours of tenacious commitment (which goes to point 3). This is all coupled with a simple fact: data integration is hard. SSIS does a good job as a generic, provider-driven solution – but most find SSIS challenging to learn and non-intuitive (I did when I first started using it). Does that mean SSIS is not worth the effort to learn? Goodness, no! It does mean complaints about simplifying the learning process are to be expected and somewhat rhetorical.

Architects disagree. We have varying amounts of experience. We have different kinds of experience. Some architects have worked only as lone-wolf consultants or as members of single-person teams. Others have worked only as leaders of small teams of ETL developers. Rarer still are enterprise architects who are cross-disciplined and experienced as both lone-wolf consultants and managers of large teams in independent consulting firms and large enterprises. Less-experienced architects sometimes believe they have solved “all the things” when they have merely solved “one of the things.” Does this make them bad people? Goodness, no. It makes them less-experienced, though, and helps identify them as such. Did the “one thing” need solving? Goodness, yes. But enterprise architecture is part science and part art. Understanding the value of a solution one does not prefer – or the value of a solution that does not apply to the problem one is trying to solve – lands squarely in the art portion of the gig.

Regarding the science portion of the gig: engineers are qualified to qualitatively pronounce any solution is “over-engineered.” Non-engineers are not qualified to make any such determination, in my opinion.

By way of example: I recently returned from the PASS Summit. Although I did not attend the session, I know an SSIS architect delivered a session in which he made dismissing statements regarding any and all metadata-driven frameworks related to ETL with SSIS. If I didn’t attend his session, how do I know about the content of the presentation? A number of people in attendance approached me after the session to share their opinion that the architect, though he shared some useful information, does not appreciate the problems faced by most enterprise architects – especially enterprise architects who lead teams of ETL developers.

My advice to all enterprise architects and would-be enterprise architects is: Don’t be that guy.

Instead, be the enterprise architect who continues to examine a solution until underlying constraints and drivers and reasons the solution was designed the way it was designed are understood. Realize and recognize the difference between “That’s wrong,” “I disagree,” and “I prefer a different solution.” One size does not fit all.

Finally, some of the demos and Biml platforms were not working at the time you wrote this review. Many have been addressed since that time. In the future, updates to SSIS, Biml, and ETL best practices will invalidate what this team of authors wrote during the first half of 2017. As such, the statements, “And much of the online information is outdated and no longer works. It’s a shame someone hasn’t found a way to simplify the learning process.” is a tautology that defines progress. Learning is part of the job of a technologist. I encourage us all – myself most of all – to continue learning.

Peace.

One Way to Write a Technical Book

In my previous post titled Writing a Technical Book, I discussed phases of the process of writing a technical book. In this post I share details of my approach to writing a technical book.

Review

To summarize, the steps for writing a technical book are:

  1. Idea
  2. Proposal
  3. Contract
  4. Writing
  5. Editing
  6. Typesetting
  7. Publication

How I Write Technical Books

First, a disclaimer. I want to be very clear: This is but one way to write a technical book. This is not the only way and there are likely better ways to write a technical book. This works for me; your mileage may vary.

I choose to list the problems I am trying to solve and my responses.

“AreYouDoneYet?”

Publishing is a business. Books are commodities in the publishing world. Like every other commodity on the planet, books are treated as goods to be sold, deliverables to be delivered. While the publisher desires a well-written book, they want a book above all else.

Impedance Mismatches

The process of writing a technical book is fraught with impedance mismatches – the misalignment of the goals between the author and publisher.

One example: the author may see the book as their magnum opus.
The publisher desires a good book, well-written. But most of all, the publisher wants to publish something. That’s how they get paid.

At the intersection of magnum opus and getting paid lies the AreYouDoneYet? call / email. This is usually an (or several) unpleasant communication(s) for all involved. The publisher likely feels fiscal pressure to ship. The author feels deadline pressure. As I said, not pleasant.

I know plenty of single-book authors who tell me, “I will never again publish a book through a publisher.” Without fail, each one lists an AreYouDoneYet? experience as their reason or part of their reason. I feel you. I really do. I don’t like those calls either.  Many solve this problem by self-publishing.

One Solution: Write First

How do I solve AreYouDoneYet? I change the order of the process and write the first draft, first – and then submitting the book proposal.

If you don’t like the AreYouDoneYet? communications, get done first. If you believe self-publication is the route to go – and I write this as someone who has self-published – then you will need to write the book before you can publish it. Go ahead and write the first draft.

Just ship.

It is not uncommon to experience difficulties writing such as writer’s block or finding adequate time to write. If you are writing about software that is scheduled to be released, the release schedule may slip. In fact, you should count on experiencing these difficulties and allow for them in your writing schedule, whether you are writing for a publisher or self-publishing.

I am not sharing this with you to discourage you. Quite the opposite, in fact! I want you to enter the writing process with both eyes wide open. I want to be unequivocal about these facts:

  • Writing is hard work.
  • Writing requires discipline.
  • Writing takes time.

If you read the writing of others and think, “I could do a better job,” odds are you are correct (especially if you’ve read my writing). I am not a good writer. Prolific does not equal good. But – and this is an important but – you have to write to write better than me.

Motivation

“Why do you write, Andy?” In my humble opinion, why you want to write is the most important question you need to answer. This question needs to be thoroughly settled in your mind before you apply keystrokes to document editor.

My answer? “I write because I love to write.” I think it stems from the joy I derive from learning and a desire to pass that joy to others. Trite? Perhaps. But altogether true. I enjoy writing. I write stuff no one else reads, even. I journal. I do not care if a single soul reads this blog post. Does that mean I don’t care about writing?

Nope. I rewrite for you.

I want you to experience the joy I derive from learning. I know some of you experience that joy because you tell me.

The coolest thing about writing, for me, is helping someone learn something.

So I rewrite. I do that here, even. I edit posts before publishing them. I often read a post later, cringe, and rewrite some sentence, paragraph, or entire section. Often, I add material or links for clarification. More often, I delete stuff (especially commas and parentheses and run-on sentences and sentences that begin with conjunctions and ellipses…).

Robert Graves said, “There is no such thing as good writing, only good rewriting.” He’s right. In his book titled “On Writing,” Stephen King shares awesome tidbits about the craft. I found King’s On Writing and Steven Pressfield’s The War of Art and Do The Work inspiring and informative. Note: those links are to my Amazon Associates account. If you would rather not use an Amazon Associates link, please click one the following links: On Writing, The War of Art, Do The Work. These are books worth reading. I wish I’d read them before I started writing.

If you are going to be a writer, please learn to write.

Learning to write will actually solve most of the AreYouDoneYet? problem.
Don’t believe me? Start writing a book today. Follow my advice and write first. You’re going to have to write the book for the book to get written, right? Even if you self-publish, you will need to write eventually.

Why not start writing today?

Strategery

Do you remember grammar and language arts classes in grade school? I do. I made straight D’s in these classes until my senior year in high school. I had a fantastic teacher my senior year at Nottoway Senior High School. His name was Barrow Cyrus. Mr. C. taught with passion. I’m sure other teachers had passion; I noticed Mr. C’s passion. Years later I would see the movie Dead Poet’s Society and be reminded of him. I made a B in Mr. Cyrus’ class. I started enjoying reading. I began to think about writing.

Remember those dumb outlining exercises? I hated those. First you have to plan how you’re going to write. You have to think first, then plan, and then write. Even back then, I enjoyed writing, but I wanted to get to the fun part and Just Write.

Those dumb outlines will save you many a headache, many hours rewriting, and might just help you avoid one or more AreYouDoneYet? communications. Please trust me: I’m right about this.

Does your outline have to be in standard outline format? Nope. It just needs to exist before you write the body of material.

In Practice

Start writing by creating a document for each chapter. Provide a title. Optionally, write an “In this chapter…” section. Don’t write more body text, not yet.

Add section headers and optionally include an “In this section…” sentence or two. You may add subheadings.

Please resist the urge to start writing in earnest on the topic until you’ve created an outline for each chapter you intend to write.

Does this mean you will not add chapters later? Goodness no! You will add and remove and consolidate chapters as you write. Start here. Write an outline.

On Advances

An advance is a pre-payment for writing. It’s essentially a loan against future royalties. An advance is seen as a way to compensate authors during the writing process, since writing takes time away from other paying endeavors.

I no longer accept advances on writing.

Why? I find it depressing to receive the first royalty statement from the publisher stating something akin to, “You earned $100 in royalties. You now owe us only $400 of the $500 we advanced you.” I’d rather receive a check for $100.

In worst cases, and advance can be weaponized against an author in an AreYouDoneYet? communication. I have a strict rule regarding ultimatums that applies to everything in life, not just writing.

Andy’s Ultimatum Rule states: You can deliver an ultimatum to Andy once.

I almost always capitulate when delivered an ultimatum. The other party gets their way. They have deployed a nuclear option, however; whether they realize it or not. My top priority from that point forward is departure.

It’s probably time I shared some potentially discouraging truth with you about writing…

You Are Not Going to Make Money Writing

Now that’s not entirely accurate. Odd are, someone reading this post will, in fact, make money writing. I have. So why’d I write that? I wrote that because you should not begin writing if you are only in it for the cashola. If even part of the reason you want to write is to make money from writing, stop now and save yourself some frustration.

I earn less – considerably less – than minimum wage writing.

“Are you telling me there’s no money in writing, Andy?”

Yes. Yes I am telling there is no money in writing. But

You can earn more money as a published consultant. If you want to look at this as a silver lining, please understand there’s a cloud attached.

My first book project was Professional SQL Server 2005 Integration Services (Wrox, 2006). That particular book sold more copies than all my other books combined. I made money off that book. The year that book was released, my salary tripled. Why? That’s an excellent question to explore.

Often, CIO’s and CEO’s do not follow the technology crafts. They don’t know who’s good from who’s not-so-good at tech. They listen to friends in the business for advice about who to hire. They hire larger firms that have been around longer because, they reason, those firms must be doing things correctly to have survived and grown.

You and I know the biggest and oldest are not always the best. We understand technology and know how to discern between who’s up-and-coming and those riding past success – or even firms that have better salespeople – or firms that flat-out lie. Some CIO’s and CEO’s cannot separate the wheat from the chaff. Many can (they didn’t get where they are for no reason!) but not all.

If you and I are bidding for the same work and I am published and you are not, I am probably going to win the work because the customer is going to reason, “Andy must know what he is doing because he’s written a book about the technology.” They would be correct insofar as I know enough to write about it. Does that mean I am going to do a better job for them, though? Maybe. But you may not get a chance to show them you can do better work than me because I can drop a physical book on their desk and you cannot.

Is that unfair?
Yes. Yes, that’s wholly and completely unfair.
It’s also reality.

So I lied. You will make money from writing, just not from the act of writing all by itself.

Consider not accepting advances.

If you accept an advance, I recommend you save the money as if you hadn’t received an advance. That way, should you find yourself in an AreYouDoneYet? discussion and an ultimatum regarding the advance is made, you preserve the option of responding akin to, “Will you take a personal check?”

If you accept an advance, please, please pay very close attention to the section in the contract about how the advance is distributed and collected. I’ve heard horror stories about authors actually owing the publisher money when books don’t sell as expected.

On Self-Publishing…

Maybe reading this post has discouraged you. If so, I am glad. Do I like hurting people’s feelings? I promise you that I do not. If you are discouraged it’s likely because you opened this post thinking some things that are simply inaccurate about writing technical books. If I’ve disabused you of false notions, I feel I’ve done you a favor.

“I’ll Just Self-Publish”

Self-publishing is a way to avoid AreYouDoneYet? communications.
Self-publishing is a way to avoid contracts and advances and all the associated headaches.
Self-publishing is a way to add work to managing the book and, thereby, the book’s value – especially the long tail. (That link is to an excellent blog post by none other than THE Seth Godin. If you’re thinking of self-publishing, you need to read Seth Godin!)

Shelf Life

Books have a shelf life. Publishers have relationships with book outlets – physical outlets like the ever-diminishing book box stores and with Amazon. In addition to these relationships, publishers also have marketing departments that create banner ads for books you and I see online. Publishing marketing departments also create email campaigns designed to attract buyers to books.

This increases book sales.
This takes time.

Math

Please do the math. Is your goal to make money from writing? I’ve shared my thoughts on that matter already. I contend the value of writing lies in consulting after being published. I feel that way because that’s what I have experienced. John Grisham probably has a different perspective.

Once he writing is done – the work – and the book is published, it’s time to advertise. You want as many people as possible to learn about your book. You want them to want to buy your book. You want them to want to hire you as a consultant. And, as I said earlier, you want to win the horribly-unfair competition against those who are not published (or not yet published) for mind-share in the minds of CIO’s and CEO’s.

The math is simple: More is better.

It’s true that I can avoid the pitfalls I’ve listed (and many that I’ve not touched upon) by self-publishing. But, bottom line, you are most likely not going to be able to advertise as much or as well as a publisher. And if you are able to advertise as much or as well as a publisher, consider doubling – multiplying, really – the advertising of your book by combining your efforts with that of the publisher.

One last thought about the math: Is it better to earn 100% of the royalties by selling 100 copies of your self-published book or to earn 10% of the royalties for selling 1,000 copies of your publisher-published book?

If you read that and thought, “That’s the same!” you are wrong.

Please scroll up and reread the paragraph about my salary tripling. You need mind-share. That’s why you should write (unless you love writing like I do!). Even if you love writing, you still need mind-share. Mind-share pays the bills.

Confession

I wrote Building Custom Tasks for SQL Server Integration Services (clean link) from a 12-part blog series. I wrote the blog series in 2012. I copied the contents into Word, edited the material, built a more-relevant demo task, paid an artist $200 to design the cover (not this cover), and popped over to CreateSpace to ready the book for self-publication.

I even printed a test copy. I was that close to pushing the button on self-publishing in March 2017. And then my editor emailed and asked, “Andy, why didn’t you send me that manuscript?” It was a fair question and I gave an honest answer: “I didn’t think you’d be interested in publishing so small a book.”

Building Custom Tasks for SQL Server Integration Services comes it at 111 pages. I really didn’t think Apress would be interested. Apress was interested. The book was released in July 2017.

Everything is Negotiable

I negotiated a bunch or author copies for Building Custom Tasks for SQL Server Integration Services. A bunch.

I failed to consider – and negotiate – more copies for my co-authors of The Biml Book.

I failed.

I led that team initially, and co-led the team along with Scott Currie at the end of the process. I regret my failure to lead and apologize to the team for missing this detail.

Why did I negotiate a bunch of copies of Building Custom Tasks for SQL Server Integration Services? I wanted to be able to give copies of the book away at conferences and speaking engagements. I did and I have.

Key take-aways:

  • The number of author copies is negotiable.
  • The writing schedule is negotiable.
  • The advance (or lack thereof) is negotiable.
  • Editorial control is negotiable.
  • Ownership and the right to share some (or even all) of the content in other outlets such as blog posts and articles and whitepapers for which you may receive additional remuneration is negotiable.

Conclusion

If you are considering writing, I encourage you to write. I want you to enter the profession (and it is a profession), though, with both eyes wide open. I want you to know what to expect. I do not want you to think writing is easy because it’s hard work (even if you enjoy writing).

After publishing your first book, it’s normal – perhaps even a rite of passage – to look back and think, “I am never doing that again!” If you’ve published and feel that way, I promise you things get better with the second book and subsequent books. Why? Because your initial expectations were part of the problem.

The problems caused by your inaccurate expectations about writing have been solved.

Last August, a small subset of The Biml Book author team rewrote the book. It took about three weeks. One Amazon reviewer commented on the unity of voice, even. That didn’t happen by accident.

For me, the rewrite was the most difficult part of writing The Biml Book. When it was done I was ready to not think about Biml for a while. If you read that last sentence and know anything about me, you know that was a heavy emotion and a pretty big deal. I love Biml! But I needed a break.

Enterprise Data & Analytics started a gig right after the rewrite concluded. We were required to be onsite for the kickoff and the customer’s HQ was a few hours drive east of my home in Farmville. Since the project was just getting started, Kent Bradshaw and I were onsite 8 hours per day; no more, no less.

Being an early riser, I got up each morning a few hours before we were due at the work site and wrote. Of all the things to do, why did I write? After weeks of rewriting, why in the world did I write?

I wrote because I love writing.

I wrote Data Integration Lifecycle Management with SSIS in one week, writing a couple hours each morning and a couple hours each evening.

I spent several weeks rewriting the book, editing, removing, and adding content. But the 185 pages you can read today started as 110 pages in Word written that week.

You Gotta Figure Out Your Why

Simon Sinek wrote a couple really good books about vocation motivation:

Start With Why and Find Your Why are excellent books.

You need some reason to write. Really, you need motivation to accomplish anything in life. I don’t think money is a good-enough reason. You need something else – something beyond money.

My why? Love. I love writing.

Regardless of whether you write or not, find your motivation.

Peace.

Writing a Technical Book

Writing a Technical BookI am often asked for more information about writing a technical book. As author or co-author of twelve books focused or touching on technology, I consider myself experienced – not an expert.

I’ve noticed “ah-ha” moments recently when sharing insights and advice, so I thought I’d write a post about it. I hope that’s ok. If you’re here, I reckon it is.

A Typical Workflow for Writing a Technical Book

“So just how does a technical book get written, Andy?” I’m glad you asked. The answer is, unsurprisingly, “It depends.” It depends on the publisher and author(s), but mostly on the publisher. It can also depend on the topic and competition (real or imagined).

Step 0 – Idea

Someone has to come up with an idea for a book. It can be either a publisher or author, but someone says, “There should be a book about that!” Publishers often seek authors these days. Every time I share this fact I get blank stares and looks that communicate, “What did you just say?” It’s true. These days publishers are looking for authors to write about technology topics.

Finding good writers is hard for publishers. The writing process is hard work and requires discipline and stubbornness from authors. I am not making any of this up.

Once the idea for writing a technical book is codified-enough, the next step is…

Step 1 – Proposal

Every publisher with whom I’ve worked has a book proposal process. Book proposal templates vary but share some important features, such as:

  • Title
  • Description
  • Author(s)
    • Author bio(s)
  • Target audience
    • Reader market size
  • Competing titles
  • Projected page count
  • Writing schedule
  • Remuneration details

Most of these items are self-explanatory. Some are negotiable. For example, you will be guessing (wildly) at the reader market size. You will also be guessing page count. And writing schedule.

Although it’s very important I choose to hold off sharing details about remuneration, for now.

When writing a technical book, there is often some give-and-take regarding the book proposal, followed by…

Step 2 – Contract

The book contract usually includes snippets from the proposal. At its heart, the contract is the work-for-remuneration document.

You should always read the contract carefully and strive to understand not only the language of what is written but the nuance of what is not written.

If you have any concerns, I recommend you consult a lawyer – especially if this is your very first experience with a writing contract.

The contract will always include the writing schedule and page count. Pro tip: delivering a first draft early that is longer than the proposed page count is not going to be an issue. (That last sentence is pregnant…)

As an author, you want to make sure you are comfortable with the contract when writing a technical book.

Step 3 – Writing

The author writes (or authors write) the first draft. For technical books, this includes screen shots and code snippets. Writing may also include quotes from others or other books.

Everything must either be original or attributed. No excuses.

There will be language regarding original / attributed content included in the contract, accompanied by language about penalties should the book contain non-original-and-unattributed content, i.e. plagiarized material.

Writing a technical book takes time to do well.

Step 4 – Editing

Editing involves at least a couple phases:

  1. Language editing – someone who is good at human language syntax, plow, and structure and understands technology well-enough to not bungle the author’s intent reads through the manuscript and notes places where better sentence structure, different words, and entire sections can be moved or changed to enhance idea-flow.
  2. Technical editing – someone, usually a technologist, who is willing to walk through the examples, samples, and demo code to verify it works and there are no missing steps.

Sometimes you can find a technologist who is good-enough with language to perform both functions – or a language editor willing to tackle the technology demos – but that’s rare.

Editing takes time to do well when writing a technical book.

Step 5 – Typesetting

Not all books are published physically, but all books pass through a typesetting phase. You may think, “Why, Andy?” That’s a fair question, especially for books published only in electronic formats (e-books).

Formatting the written word is important. There’s an entire science dedicated to fonts. What looks good on a paper page may not be as easy to read in a web browser or an e-reader like the Kindle. Publishers (and authors… well, good authors) want the reader to enjoy as little resistance to reading as possible. That’s not as easy as it sounds.

Books are more than just words. There are images and code to consider. Readers often complain about line-breaks in published code. In response, publishers go to great lengths to manage code line breaks and line continuation indicators. Again, this is not as easy as it sounds.

When writing a technical book, typesetting takes time to do well.

Step 6 – Publication

Once the idea for the book has been conceived, the proposal submitted and accepted, the contract agreed upon, the book has been written, has been edited, and the typesetting is complete; the book is ready to be published.

All authors understand this step: this is why they signed up to write a book in the first place!

There is no way to adequately and accurately communicate the emotions of opening a package and seeing your name printed on the cover of a book.

Conclusion

This post covers writing a technical book at a high-level. In my next post, I share tweaks to this process I have learned from experience.

What’s the Best Book for Learning SSIS?

Best Book for Learning SSISI am often asked the question, “Andy, what’s the Best Book for Learning SSIS?” There are some really good books out there that will help you learn SSIS (and I am co-author of several).

The book from which I learned the most about SSIS is Extending SSIS 2005 with Script by Donald Farmer.

Why do I consider this book the best?

Donald Farmer (TreeHive Strategy | Interview on the Data Driven Podcast) is a data engineering industry expert. He led the team that developed Microsoft SQL Server Integration Services. He’s technical, a great communicator (speaking and writing), and fantastic at solving business problems.

In Extending SSIS 2005 with Script, Donald discusses and shares demo code that informs he reader of the problems SSIS is designed to solve, and how SSIS is designed to solve those problems. Best of all, you learn this from one of the minds that designed the product.

I consider the Data Flow task to be the heart of SSIS – and the SSIS Script Component of the Data Flow Task is arguably the most difficult and most flexible component in the SSIS Data Flow. If you get your mind around the Script Component, you have a pretty good handle on how the SSIS Data Flow works, in my humble opinion.

I am aware of SQL Server Integration Services (SSIS) courses that are based on the material contained in this book. It’s that good.

“This book is over 10 years old, Andy…”

Yep. The information remains cogent. If I have to choose one book, Extending SSIS 2005 with Script remains the best book for learning SSIS in my opinion.

The Best Book for Learning SSIS

How serious am I in this recommendation? I just ordered a copy for one of the independent consultants at Enterprise Data & Analytics who is learning more about SSIS. I want her to become a senior SSIS developer in 2019 and I consider this book vital to accomplishing that goal.

Full disclosure: The links to the book above pass through the Amazon Affiliate Program. If that bugs you, please click here for a clean link to the book.

Enjoy.

:{>

One Learning Path for Biml

One Biml Learning Path - The Biml BookI regularly get email asking how to learn Business Intelligence Markup Language (Biml). I thought I would share one Biml learning path in this post.

Learning Biml is like learning any new technology: it’s hard. You have to commit to spending hours “heads down” working through examples, viewing training material, and reading blogs and books. How many hours? It depends. In the past, I’ve shared my estimate that it takes about 40 hours to learn Biml well-enough to be proficient with Business Intelligence Markup Language. Because I believe it takes 40 hours to learn Biml, I recommend learning Biml on a real-world project that requires you to build 20+ SSIS packages using the same SSIS design pattern.

While difficult, it is not impossible. I know because I and others learned Biml, and we are all confident you can learn Biml too.

Let’s get started!

One Biml Learning Path

To get started with Biml, I recommend the following learning path in the following order:

  1. Stairway to Biml Level 5 – Biml Language Elements
  2. Stairway to Biml Level 6 – Programming with Metadata
  3. Stairway to Biml Level 2: Biml Basics
  4. Stairway to Biml Level 3: Building an Incremental Load Package
  5. Stairway to Biml Level 4: Using Biml as an SSIS Design Patterns Engine
  6. The Biml Book

Please find video-based training for some of the material above at Biml Academy, some of which is free. Of the content that is not free, I recommend the Basic Biml Training course for three reasons:

  • Basic Biml Training is the best value.
  • The price will increase.
  • Enrolling in the course now grants you access to the course for its lifetime – including lessons not yet released – at no additional cost.

Be sure to follow Biml-related accounts on social media for the latest announcements regarding Biml and Biml-related content published by BimlHeroes and Biml users. Two of my favorite Biml blogs are Cathrine Wilhelmsen‘s blog and Ben Weissman‘s Solisyon blog.

Enjoy learning Biml!

:{>

My Books

I’ve authored and co-authored a bunch of books!

Truth be told, I enjoy writing. I think my love of writing stems from a love of learning. I’m just trying to share the joy!

It’s a huge honor to write. I’m humbled and thrilled (all at the same time – a feeling I describe as “all giggly inside”) whenever I’m attending an event like the PASS Summit or a SQL Saturday and someone tells me they enjoy what I’ve written or learned something from something I’ve written.

Mostly I’ve co-authored books but a few I’ve written by myself. I owe all my knowledge to those who shared their knowledge with me. I am merely your scribe.

For a list and links to my books, please visit my Amazon author page.

If you’d like for me to train you or your team in the fine arts and sciences of SSIS, Biml, or both in-person or remotely, please contact me.

:{>

Why I Built DILM Suite, by Andy Leonard

The following is Chapter 6: Catalog Browser from my latest book titled Data Integration Life Cycle Management with SSIS: A Short Introduction by Example:

I was honored to be a Microsoft SQL Server MVP for five years (2007-2012). One cool thing about a being a Microsoft MVP was access to the internal developer teams. Everyone could file Microsoft Connect items reporting bugs and making suggestions for product improvements. Many MVPs did so only to have their bug reports marked as “works as designed” or “won’t fix” and suggestions responded to with something similar. It was discouraging. There are reasons many Connect items were addressed in this way. I am happy to report the root cause (Performance-Based Management or PBM) has been abandoned and the Microsoft Developer Teams are really and truly listening and responding to requests from the field.

Why I Built DILM Suite, by Andy Leonard

That doesn’t mean every suggestion is acted upon (I promise this is not a complaint). It turns out that Microsoft is a software development enterprise. As big as Microsoft is, they cannot possibly respond to every request. When I realized this, I began thinking about how I might address gaps I perceived. I’d co-founded a consulting company and we (collectively) weren’t interested in becoming a software product company. But I was very interested in developing products to address gaps in Data Integration Lifecycle Management (DILM).

In 2015 I left the consulting company I co-founded and immediately began developing the software I’d dreamed of building. In my opinion, the most fair answers to the question, “Why?” are:

  1. I came to believe the Microsoft SSIS Developer Team would never address the things I perceived as “gaps” in the product story; and
  2. I came to believe the consulting company I co-founded and I held irreconcilable visions of how to address DILM issues.

Looking back with two years of perspective, I believe focusing on DILM was the best long-term move for me. I started another consulting company, Enterprise Data & Analytics (entdna.com), mostly to fund my coding habit.

Surfacing the SSIS Catalog

Let’s examine the SSIS Catalog surface in the SSMS Object Explorer’s Integration Services Catalogs node as shown in Figure 6-1:


Figure 6-1. The SSIS Catalog as Shown in the SSMS Object Explorer Integration Services Catalogs Node

Beneath the Integration Services Catalogs node we find the SSIS Catalog named SSISDB. Two Catalog Folders are displayed, Framework and Test. The Test Folder contains Projects and Environment virtual folders. The Projects virtual folder contains our SSIS Catalog Project named DILMSample, which in turn contains our SSIS Package named SimplePackage.dtsx. The Environments virtual folder contains our Catalog Environment named envConnection1.

We know – because we’ve done the work – that there’s more there than meets the eye.

6.1   SSIS Catalog Environment Configuration

If we double-click envConnection1 we can see details of our Catalog Environment Variable on the Variables page as shown in Figure 6-2:


Figure 6-2. Viewing the Variables Page of an SSIS Catalog Environment

The Variables page contains details about SSIS Catalog Environment Variables including name, data type, description, value, and whether the variable is sensitive.

6.2   SSIS Catalog Project Configuration

The Parameters tab on the Parameters page of the SSIS Catalog Project Configuration dialog lists SSIS Project and Package parameters, their container name, and value by default as shown in Figure 6-3:


Figure 6-3. Viewing Project Parameters and Values for an SSIS Catalog Project

The Connection Managers tab of the Parameters page contains a list of SSIS Project and Package connection managers and their properties as shown in Figure 6-4:


Figure 6-4. Viewing Connection Manager Parameters and Values for an SSIS Catalog Project

The References page of the SSIS Catalog Project Configure dialog contains a list of SSIS Catalog Environments the SSIS Catalog Project may reference at runtime as shown in Figure 6-5:


Figure 6-5. Viewing Project References for an SSIS Catalog Project

That’s a lot of right- and double-clicking just to see what’s configured in an SSIS Catalog Project.

6.3   Catalog Browser

The SSIS Catalog is filled with really cool and useful configuration information, but one has to know where to look and – in some cases – where to look isn’t so obvious.

Enter Catalog Browser, a free utility that is part of the DILM Suite and available at dilmsuite.com/catalog-browser. Catalog Browser was built to surface the contents of the SSIS Catalog in a single view – a tree that exposes all relevant SSIS Catalog artifacts, properties, and configurations.

As shown in Figure 6-6, Catalog Browser surfaces the same metadata as the SSMS Object Explorer Integration Services Catalogs node:


Figure 6-6. Catalog Browser Surfacing Part of the SSIS Project and Configurations Metadata

Looking at Figure 6-6, though, you probably already see some differences between Catalog Browser and the SSMS Object Explorer Integration Services Catalogs node. Please note the Project Parameters and Project References virtual folders present beneath the SSIS Catalog Project, in addition to the Packages virtual folder.

Expanding these virtual folders reveals the SSIS Catalog Project Parameters and Reference as shown in Figure 6-7:


Figure 6-7. SSIS Catalog Project Parameters and References

Please remember in Figure 6-3 the SSMS Object Explorer Integration Services Catalogs node surfaced all parameters – SSIS Catalog Project Parameters and SSIS Package Parameters. Where are the Package Parameters? They’re here in Catalog Browser. To view Package Parameters, expand the SimplePackage.dtsx SSIS Package node as shown in Figure 6-8:


Figure 6-8. Viewing SSIS Package Parameters

Please recall Connection Manager Properties are treated as Parameters in the SSIS Catalog. They are prefixed with “CM.”. We see the SSIS Package Connection Manager vmDemo\Demo.TestDB1 Connection String property is mapped to an SSIS Catalog Environment Variable named ConnectionString.

To surface the Reference used for the Reference Mapping, expand the Package References virtual folder as shown in Figure 6-9:


Figure 6-9. Viewing the Package Reference

Expanding the Package Reference virtual folder surfaces the Test/envConection1 Catalog Environment. Expanding the Test/envConection1 Catalog Environment reveals the Catalog Environment Variable named ConnectionString is mapped to the vmDemo\Demo.TestDB1 Connection String property.

But what’s the value of the ConnectionString Catalog Environment Variable? Expand the envConnection1 Catalog Environment in the Environments virtual Folder to view the collection of Catalog Environment Variables, their data types, and their values as shown in Figure 6-10:


Figure 6-10. Catalog Environment Variables, Data Types, and Values

SSIS Package Properties includes a Package Version property constructed from the Version Major, Version Minor, and Version Build properties of the SSIS package. Every time a developer saves an SSIS package, the Version Build property increments. It’s possible to revise an SSIS package and “trick” the Version Build property by manually setting it. I have not yet found a valid use case for doing so to SSIS Catalog-deployed SSIS packages.

The Package Version property can be used to detect different versions of SSIS packages deployed to an SSIS Catalog. Because SSIS developers can manually set the Version Build property, Package Version is not a reliable indication.

The Package Properties virtual folder surfaces SSIS Package metadata as shown in Figure 6-11:


Figure 6-11. SSIS Package Properties

Catalog Properties are handy for detecting differences in patch levels (via the Schema Build property). Catalog Version is a property exposed by Catalog Base – the custom Catalog object that lies beneath Catalog Browser.

Catalog Base works with SSIS 2012, 2014, and 2016 Catalogs.

An update that also works with SSIS 2017 is under development.

The Catalog Properties virtual folder surfaces SSIS Catalog metadata as shown in Figure 6-12:


Figure 6-12. SSIS Catalog Properties

Conclusion

Catalog Browser surfaces SSIS Catalog artifacts, configurations metadata, and artifact properties in a single view.