Posted on

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.

:{>

Posted on

Biml Academy – Using Biml to Build a Target Database

Using Biml to Build a Target Database is the latest unit available at Biml Academy.

You can see a description of Using Biml to Build a Target Database at the link above, but I ask you to consider Basic Biml Training – which includes:

There are additional reasons you should consider Basic Biml Training.

  1. Basic Biml Training is less expensive than the sum of the individual courses.
  2. If you sign up for Basic Biml Training now, your membership does not expire. (This will change. Soon.)
  3. This means if you sign up for Basic Biml Training now,  you will get access to the current content and all future Biml Basic Training content – at no additional charge.
  4. With the release of each new module, the cost of Basic Biml Training increases.
Posted on

DILM Suite + Azure Data Factory Integration Runtime

DILM Suite was designed to support enterprise data engineering / data integration with SSIS. But the solutions, utilities, and tools work well with Azure Data Factory Integration Runtime (ADF IR, or “SSIS in the cloud”).

As I mentioned in my post titled The Cloud Costs Money, leaving ADF Integration Runtime running can get pricey – and fast.

Most DILM Suite Functions Do Not Require ADF Integration Runtime to be Running

That’s right. Since DILM Suite tools and utilities connect directly to the database, they do not require Azure Data Factory Integration Runtime to be in a running state for most operations.

Here, for example, I’ve connected Framework Browser to an instance of SSIS Framework Community Edition – deployed to the ADF Integration Runtime as demonstrated in the latest Summer-O’-ADF webinar, Designing a Custom ADF SSIS Execution Framework – doesn’t have to be in a Running state to allow interaction with Framework Browser and Catalog Browser (click to enlarge):

That’s handy information right there. And it can save you money.

:{>

Posted on

SSIS Catalog Compare vNext Preview

I am happy to announce that the next version of SSIS Catalog Compare is officially in Preview!

What exactly does “Preview” mean? I’m glad you asked. It means I am sharing copies of SSIS Catalog Compare with users of the current version to get their feedback, find bugs, and help identify improvements.

Here’s a screenshot:

Looking at this screenshot (click to enlarge), you may notice some things:

  1. Font color indications – in addition to background color changes (which provide a shading for people who have difficulty seeing colors), the font color of “different” items is also different.
  2. The SSIS Catalog loaded into Catalog 2 is an Azure Data Factory Integration Runtime. SSIS Catalog Compare now speaks cloud.

I will be demonstrating using this preview of SSIS Catalog Compare in an upcoming (free) webinar titled Use SSIS Catalog Compare to Lift and Shift SSIS to ADF. The webinar is 19 Jul 2018 at noon EDT. I hope to see you there!

:{>

Posted on

Free Webinar – Use SSIS Catalog Compare to Lift and Shift SSIS to ADF

I’m excited to announce the next free webinar in the Summer-O’-ADF series: Use SSIS Catalog Compare to Lift and Shift SSIS to ADF!

Consider…

Your enterprise uses Microsoft SQL Server so the powers-that-be asked the good people at Microsoft to demonstrate the latest and greatest Azure coolness. Due to competition on cost and time to market, everyone is looking for ways to improve efficiency. So the team from Microsoft was called for presentation and demo.

And wow!

The C-levels, VP’s, and Directors were all impressed. Marching orders have been decreed. It’s time. The boss-lady is ready to start implementing the latest cool-new-shiny analytics capabilities of the cloud. There’s just one problem:

All your data is on-premises!

If only there was some tool, some utility, that would facilitate the scripting of existing SSIS configurations from the SSIS Catalog. There is such a tool! SSIS Catalog Compare!

Lift and Shift SSIS

SSIS Catalog Compare facilitates DevOps for enterprise SSIS, saves time, improves code quality, and lifts and shifts SSIS projects to Azure Data Factory’s Integration Runtime. More than just an “Package / Project Mover,” SSIS Catalog Compare scripts and/or deploys entire SSIS Catalog configurations including folders, projects, environments, environment variables, references, and reference mappings.

Join SSIS author, consultant, trainer, and blogger Andy Leonard at noon EDT Thursday 19 Jul 2018 as he demonstrates using SSIS Catalog Compare to lift and shift enterprise SSIS to ADF!

Register today!

The Summer-O’-ADF Series Continues!

I’m having too much fun delivering this series. I hope it shows! Because it’s a very cool time to be working in data-related technology. It seems every week there’s a new announcement of some awesome new functionality available in the cloud.

Please join me as we continue this epic journey this summer. It’s the Summer-O’-ADF! Next stop, Lift and Shift SSIS with SSIS Catalog Compare!

:{>

Posted on

100 Dumb Little Things

Software development is hard. It takes time, yes. But more than that, software development takes patience and thought and blood and sweat and love and tears.

My friends at Varigence recently released an update to their Business Intelligence Markup Language (Biml) products. If you’re into business intelligence or data science, integration, or engineering, you should check out Biml.

The release took longer than some would have liked.
Varigence didn’t provide regular updates on progress.
Some became… antsy.

I understand. Really, I do. As a BimlHero I get just a little more access behind the curtain compared to the average bear. Would I like to know more? Yep. Does it bother me when I don’t hear more? Nope. Why?

Software Development is Hard

I know how difficult it is to develop software because I decided back in the early 20-teens that I wanted to develop some software. (And I did it! Check out DILM Suite!) In the early 20-teens, I encountered… resistance… to the idea. Make no mistake, the resistance was well-founded and may ultimately prove to have been correct. But resistance didn’t do anything to curb my beliefs that:

  1. Software should always participate in a lifecycle that is managed, preferably by a process akin to DevOps;
  2. All software is tested. Some intentionally; and
  3. SSIS development is software development.

SSIS Rocks

The SSIS team at Microsoft has given us some incredible out-of-the-box functionality. I love the SSIS Catalog! It’s a great enterprise framework for managing data engineering execution, logging, and externalization (configuration). I believe that strongly-enough to have included similar statements in my last book: Data Integration Life Cycle Management with SSIS:

I can hear you thinking, “If you’re convinced the SSIS Catalog is so awesome, Andy, why did you build DILM Suite?” That’s a fair question. I actually answer this question in the book in chapter 6 titled Catalog Browser. You don’t have to buy the book to learn my answer; I published Chapter 6 here on this blog in a post with the obscure title, Why I Built DILM Suite, by Andy Leonard.

Was I Right?

I don’t know.
Time will tell.

There have been thousands of downloads since I built DILM Suite. I view the number of downloads as indicative of interest. Does everyone who downloads a product – especially a free product – use that product? Goodness no. Does everyone who uses SSIS or the SSIS Catalog need to download DILM Suite components? Goodness no.

If you’re trying to practice lifecycle management (or DevOps) with SSIS, though, DILM Suite can help.

100 Dumb Little Things

Software development is a lot like being a parent in that it consists of getting 100 dumb little things right. Are the dumb little things important? Some are, some are not, and some are vital. Does anyone get all 100 dumb little things right in parenting? in software development? No and no.

At the end of the day, every day in fact, I am extremely proud of what I’ve built.

SSIS Catalog Compare is the first product I’ve ever attempted to develop. Perhaps that shows. My competition certainly thinks so and has made much hay out of this fact. Do I shy away from telling folks because my competition uses it against me?

Nope. At the end of the day, every day in fact, I am extremely proud of what I’ve built. I get regular feedback from customers sharing how much the product helps them manage SSIS in their enterprise. The feedback greatly overshadows the… statements… of the competition. (Sidebar: I sometimes wonder how my competition sleeps at night…)

Getting software right is all about getting everything right including the best wording for feedback and error messages (like that shown at the top of this post).
Getting everything right is almost impossible, and certainly cost-prohibitive, but it should absolutely be the goal of any software development endeavor.

Getting 100 dumb little things right is my goal.

Peace.

Posted on

It’s Biml 2018 Release Day!

Scott Currie and the team at Varigence announce the release of BimlFlex 2018, BimlStudio 2018, and BimlExpress 2018!

This much-anticipated release includes awesome features for experienced Biml professionals as well as for data engineers new to automating processes!

BimlFlex 2018

BimlFlex is a mature and complete data engineering automation framework. Out of the box, BimlFlex automates several data models, including:

  • Staging Database
  • Persisted Staging Area (ODS)
  • Raw Data Valut
  • Business Data Vault
  • Dimensional Data Warehouse
  • Data Marts
  • Cubes
  • Tabular

The benefits of BimlFlex:

  1. Flexible Data Framework
  2. Robust Metadata Mapping Tool
  3. Avoid Data Debt
  4. Shorter Delivery Times
  5. Simplified Maintenance
  6. Upfront Pricing

 

I absolutely love the BimlFlex Savings Calculator near the bottom of the page (click to enlarge)!

BimlStudio 2018

BimlStudio 2018 includes support for all Azure Data Factory (ADF) version 2 items (as of 06/20/2018). Which ADFv2 items does BimlStudio 2018 support?

  • Linked Services
  • DataSets, Sources, and Sinks
  • Pipelines and Activities (including control flow)
  • Triggers

Also included is support for BimlScript PreCompiled Assembly Package (BSPCAP) files, which include the preprocessed binary assets for all of the BimlScript files in your project.

BSPCAP promises faster processing – especially with large codebases – for builds in the interactive designer and for command line builds. I’m looking forward to learning more about this feature!

There are too many new features to list here – go to the BimlStudio 2018 page and check them all out!

BimlExpress 2018

Varigence keeps giving away cool stuff! Nowhere is Varigence’s commitment to community more evident than in the feature list for BimlExpress 2018. The previous version – BimlExpress 2017 – included the Preview Pane. BimlExpress 2018 includes the ability to Convert SSIS Packages to Biml:

How cool is that? And it’s in the free (FREE!) version!

As with BimlFlex and BimlStudio, there are too many cool features to list here. Head over to the BimlExpress 2018 feature page to learn more.

And Happy Biml’ing!

:{>

Posted on

SSIS Catalog Browser v0.6.4.0 Released

“There I was…” happy as a clam, rolling right along in my free webinar titled “Introduction to Lifting and Shifting SSIS to the Cloud” (you can view the recording if you missed it [registration required]). I opened Catalog Browser to show off this free utility from DILM Suite and “Guess what my code did?”

“Exactly what you told it to do, Andy!”

That’s right.

The Bug

I found a bug in Catalog Browser – and in the next version of Catalog Compare (they share the same Catalog visualization code base). I fixed the bug this fine Saturday morning and I can best describe it by showing you the previous and current versions side-by-side (click to enlarge):

As you can see from the SQL Server instance textboxes, I am connected to the same SSIS Catalog in both images. v0.6.2.0 had a bug in the way it surfaced connection references. In my defense, the logic for connection references is among the most complex functionality in Catalog Browser.

But still.

Something very similar to the left image happened during my webinar. I was using Catalog Browser to display the Values Everywhere feature – which I like a lot – and I got duplication in some of the nodes. Not just any duplication either, mind you, it was very… multiple-ish – maybe even exponential-y – duplication. As a result, I was surprised during my presentation and even thought for a minute the ADF Integration Runtime Catalog had been updated since my previous tests.

Then I thought about it some more and could not recall testing Values Everywhere with multiple connection reference mappings. That bugged me. So I decided to fix it post haste.

It’s fixed in both the latest release of Catalog Browser and the upcoming release of Catalog Compare.

Catalog Browser is free. It’s part of the DILM Suite (DILM stands for Data Integration Lifecycle Management) which is a collection of mostly free – and some open source – utilities that support managing SSIS in the enterprise. I wrote about several DILM Suite solutions in my latest book:

Conclusion

As I mentioned, Catalog Browser connects to all versions of the SSIS Catalog. At the time of this writing, “all versions” includes 2012, 2014, 2016, 2017, and Azure Data Factory Integration Runtime.

Learn More:
Free Webinar – The Azure Data Factory Controller Design Pattern – 28 Jun at noon EDT

Honored to Present an SSIS and ADF Precon at Data Platform Summit 2018!

Expert SSIS – Live, Online, 2.5 Days – 10-12 Sep 2018 – delivered in cooperation with Brent Ozar Unlimited

Honored to Deliver Intelligent Data Integration – a PASS Summit 2018 Precon

:{>

Posted on

Free Webinar – The Azure Data Factory Controller Design Pattern – 28 Jun

Andy’s Summer-o’-ADF continues with another free (yes, FREE) webinar!

 

Abstract

Join me as I begin to explore a brand new topic: Azure Data Factory Design Patterns. This first webinar in the series is titled The Azure Data Factory Controller Design Pattern. In this demo-tastic session I show how to apply the pattern described in this post – titled SSIS Design Pattern: Controller Pattern – to ADF.

Registration is required to attend The Azure Data Factory Controller Design Pattern.

Sign up today!

In conclusion, I hope to see you there.

:{>

Posted on

The Recording for Introduction to Lifting and Shifting SSIS to the Cloud is Now Available

 

The recording of my free webinar – Introduction to Lifting and Shifting SSIS to the Cloud – is now available (registration required).

Abstract

  • How does an enterprise lift and shift SSIS projects from SSIS Catalogs on-premises to the Azure Data Factory Integration Runtime?
  • What are the best practices?
  • Do SSIS lifecycle management tools exist to support SSIS lift and shift?

Join Andy Leonard – SSIS author, trainer, and consultant – to learn the answers to these questions. In this webinar, Andy discusses and demonstrates migrating SSIS to the Azure Data Factory Integration Runtime.

Enjoy!

You may want to hire Enterprise Data & Analytics to help your enterprise SSIS to Azure Data Factory because we grok ADF Integration Runtime.

Contact us today.

:{>