Join me For Expert SSIS Training!

I’m honored to announce Expert SSIS – a course from Enterprise Data & Analytics!

The next delivery is 01-02 Apr 2019, 9:00 AM – 4:30 PM ET.

If you sign up by 31 Jan, you save money. Right now the course is on sale!

Data integration is the foundation of data science, business intelligence, and enterprise data warehousing. This instructor-led training class is specifically designed for SQL Server Integration Services (SSIS) professionals responsible for developing, deploying, and managing data integration at enterprise-scale.

You will learn to improve data integration with SSIS by:

  • Building faster data integration.
  • Making data integration execution more manageable.
  • Building data integration faster.

Agenda

  1. SSIS Design Patterns for Performance – how to build SSIS packages that execute and load data faster by tuning SSIS data flows and implementing performance patterns.
  2. SSIS Deployment, Configuration, Execution, and Monitoring – the “Ops” part of DevOps with SSIS using out-of-the-box tools and the latest utilities.
  3. Automation – how to use Business Intelligence Markup Language (Biml) to improve SSIS quality and reduce development time.

Did someone mention a sale?

Yep! The course is currently on sale until 31 Jan 2019!

I hope to see you there!

PS – Want to Learn More About Azure Data Factory?

Want to Learn More About Azure Data Factory?

From me?

I am honored to announce Getting Started with Azure Data Factory – a course from Enterprise Data & Analytics!

The next delivery is 04 Mar 2019, 9:00 AM – 4:30 PM ET.

If you sign up by 31 Jan, you save money. Right now the course is on sale!

Azure Data Factory, or ADF, is an Azure PaaS (Platform-as-a-Service) that provides hybrid data integration at global scale. Use ADF to build fully managed ETL in the cloud – including SSIS. Join Andy Leonard – authorblogger, and Chief Data Engineer at Enterprise Data & Analytics – as he demonstrates practical Azure Data Factory use cases.

In this course, you’ll learn:

  • The essentials of ADF
  • Developing, testing, scheduling, monitoring, and managing ADF pipelines
  • Lifting and shifting SSIS to ADF SSIS Integration Runtime (Azure-SSIS)
  • ADF design patterns
  • Data Integration Lifecycle Management (DILM) for the cloud and hybrid data integration scenarios

Did someone mention a sale?

Yep! The course is currently on sale until 31 Jan 2019!

I hope to see you there!

PS – Join me For Expert SSIS Training!

“Think of the Money You Saved”

A True Story of Value vs. Hourly Rate tells the (true) story of a client who did the math. Enterprise Data & Analytics – charging more per hour – was delivering more value than an offshore team charging less than half our rate. Granted, the lady who did the math is a math major. But still.

To quote the sage wisdom of Foghorn Leghorn, “Figures don’t lie.”

I Engineer

“My name is Andy and I’m an engineer.” In case you’ve never spent time around an engineer or overheard one of us interacting with others, we have a reputation for being… direct. (Direct is the nicest word I could think of to describe many of us. Brash, harsh, rude… these terms all come to mind.)

Not all, but many engineers do not prioritize emotion. Many engineers are solution-focused to the exclusion of, well, many things that may actually improve the solution – things that would almost certainly reduce the time required to achieve said solution.

Meetings With Potential Clients

My engineer-y skills shine when meeting with potential clients. By “shine” I mean I often say things that are not at all helpful to Enterprise Data & Analytics‘ potential client and are most often not-helpful to closing the sale. One such saying is… 

“Think of the money you saved!”

When do I say, “Think of the money you saved!”? Usually when the potential client is describing a previous solution that did not solve the problem they were trying to solve. If I had a nickel for every time I have heard someone describe the factors that led to the decision to hire someone to fix the problem and that cost (per hour) tipped the scales in the final decision, I would have a lot of nickels.

Tragedy

Hiring the least – or even a lesser – hourly rate firm is sometimes tragic… unless you’re hiring unskilled labor (and even that might be tragic).

My friend Nick is an executive corporate strategist. Nick is very good at his job. He has conversations with other executives to help them avoid this error. Nick’s logic? “You believe it’s a good idea to hire a cut-rate professional to engage on an enterprise technology project, but if your child needs a surgeon or you need a divorce lawyer, only the best will do?”

Nick has a point. When getting things done right the first time matters, we naturally hire the best available.

“Do ‘the Best’ Always Charge the Most?”

No. “The best” service providers do not always demand the highest rate. Sometimes the best salespeople demand the highest rate. They often get that rate because they excel at persuasion. That said, the market almost always bears a higher rate for professionals who are experienced and deliver value.

Why Do I Say, “Think of All the Money You Saved?”

My heart is breaking. I’m learning about a company who paid good money for a solution and got either nothing or not the solution they were looking for. The money they’ve already spent was either partially or completely wasted. Now they are willing to spend more to have the project fixed or done correctly, i.e. delivered. I’m being a little obvious and not very helpful by stating a lesson they’ve already learned…

The hard way.

Confession Time

Ok, I confess: I don’t actually say, “Think of all the money you saved.” At least not anymore. Why did I stop saying that? It’s not kind. It’s not empathetic. It doesn’t help. Saying that could cost us the gig, even. Nobody likes a jackhole.

I instead focus on helping. We can help most by delivering the solution. And we often deliver the solution for less money than they already spent – even though we charge more per hour.

The Pain Is Real

The tragedy is real, though. It happens. If you work through the math, it tracks with the old saying, “How come there’s never time to do things right but always time to do things over?”

Experience informs me there are situations when time is of the essence. Minimally-viable products (MVPs), for example, are extremely time-sensitive. Has Enterprise Data & Analytics delivered MVPs? Absolutely! We’ve even recommended – and delivered – minimally-viable products to clients as deliverables for the first sprint of major enterprise projects.

Conclusion

Please don’t be that company. Don’t hire a lower-rate firm if quality and time-to-market are factors in your enterprise deliverable. Hire the best, like you would if your child needed a surgeon. Why?

Deliver quality late, no one remembers.
Deliver junk on time, no one forgets.

Grant SSIS Catalog Read-Only Permissions

The SSIS Catalog has built-in security to manage permissions. SSISDB – the database behind the SSIS Catalog – is “just a database” in many respects. When it comes to security, the SSIS Catalog surfaces an internal mechanism that provides granular permissions management. 
In this post I intend to demonstrate how to use SSIS Catalog security to provide read-only access to SSIS Catalog artifacts. But first…

Two Thoughts About SSISDB Roles

Thought 1: “Help us DBAs, you’re our only hope.” – Princess Leia (paraphrased)

If you work with SSIS you already know the Microsoft team of technical writers is comprised of artists, masters of the field of technical writing. I’m convinced a large part of the successful adoption of SSIS is due these good people. You can see a sample of their outstanding artistry in the article titled Integration Services Roles (SSIS Service).

Two important roles in the SSIS Catalog are ssis_admin and ssis_logreader. According to the article linked above:

ssis_admin. This role provides full administrative access to the SSIS Catalog database.

ssis_logreader This role provides permissions to access all the views related SSISDB operational logs.

SSIS_admin and ssis_logreader are SQL Server database roles. As such, they are typically set and maintained by Database Administrators (DBAs).

Thought 2: Although SSISDB is a SQL Server database, it’s more like an application coded in T-SQL.

One for-instance, for instance, is the SSIS Catalog requires Windows authentication for most administrative activities. It took me a while to understand why Windows authentication is necessary. I now get it, but the explanation is for another post. This has implications, such as:

  • SQL Logins – even sysadmins such as sa – cannot deploy SSIS projects to an SSIS Catalog. Or execute SSIS packages.
    • One exception: an Azure-SSIS SSISDB database hosted on an instance of Azure SQL DB can perform SSIS Catalog administration using a SQL Login.

In sum, the SSIS Catalog is a database application that requires Windows authentication for administrative tasks.

Null Use Case: No Access

When a user has no access to SSIS Catalog artifacts, the SSMS Object Explorer Integration Services Catalogs node appears as shown here:

SSIS Catalog Browser presents a unified view of all SSIS Catalog artifacts (and it’s free!).

All SSIS Catalog-related products and utilities at DILM Suite respect SSIS Catalog security. Early versions of SSIS Catalog Compare allowed users to login with SQL Server Login credentials and access SSIS Catalog artifacts that SSIS Catalog security would block. A couple years ago I refactored CatalogBase – the object beneath DILM Suite’s SSIS Catalog products and utilities that interacts with the SSIS Catalog – to respect SSIS Catalog security.

A user with no access will see an empty SSIS Catalog using SSIS Catalog Browser:

Grant Read-Only Access to Folders

In SSMS, right-click the SSIS Catalog Folder you wish to surface for a 
Windows authentication-based SQL Server login:

When the Folder Properties window displays, click the Permissions page. On the Permissions page, click the Browse button to select one or more Windows authentication logins:

The Browse All Principals dialog displays. Select one or more Windows authentication logins (Windows User type):

To assign read-only permission to the SSIS Catalog Folder, click the OK button on the Browse All Principals dialog and check the Grant checkbox for the Read permission in the Folder Properties window:

The user(s) you selected are now able to view the SSIS Catalog folder using the SSMS Object Explorer’s Integration Services Catalogs node:

Similarly, SSIS Catalog Browser will display the SSIS Catalog folder for the assigned user(s);

“Where’s the Beef?”

I can hear you thinking, “Where are the projects and environments, Andy?” That is an excellent question. I’m glad you asked! They exist, of course, but we only granted the user Read permission for the SSIS Catalog Folder.

Grant Read-Only Access to Folder Artifacts

To see SSIS Projects and SSIS Catalog Environments, a user must be granted Read Objects permission on the SSIS Catalog Folder:

Now users can view SSIS Catalog folder contents using SSMS:

SSIS Catalog Browser users enjoy a similar view:

Conclusion

The SSIS Catalog surfaces a rich and powerful security model that may be leveraged by enterprise data integration DevOps teams using SSMS or SSIS Catalog Browser.

Want to learn more? From me? Join me for the next delivery of Expert SSIS – a 2-day, live, remote SSIS training event! 

The 2:00 AM Test

I’m working with a collection of clients these days providing data integration, cloud, and DevOps advice. I love architecture work! My clients love the results. Everyone wins.

Kent Bradshaw and I are tag-teaming with a couple clients who are converting data integration from other platforms to SSIS. In a recent meeting Kent mentioned “the 2:00 AM test.”

“What’s the 2:00 AM Test, Andy?”

I’m glad you asked! The 2:00 AM test is a question developers should ask themselves when designing solutions. The question?

“Will this make sense at 2:00 AM? When I have – or someone else has – been awakened by a job or application failure? Will I be able to look at this and intuit stuff about how this is supposed to work?”

Future You Will Thank You

The reason the 2:00 AM test is important is because later – at some point in the future when you’ve long-since stopped thinking about the stuff you’re thinking about when you designed this solution – you (or someone) will be awakened at 2:00 AM by some failure. Even if it’s you, there’s a good chance you won’t remember all the nuances of the design. Even if you do, you may not remember why you designed things the way you did.

So…?

Be like Hansel and Gretel. Leave yourself some breadcrumbs. What kind of breadcrumbs?

Comments

Code comments are the best way to record what you are thinking at the time you are thinking it. Consider the lifetime of a software solution. It may take an hour, a day, week, or month to build; but it may be in production for years. Are you going to remember why you made each decision you made? Are you going to be able to recall – years later – why you zigged instead of zagging?

Call out anything not readily accessible, anything invisible on the initial view of the immediate coding surface. If you’re writing VB or C#, include a comment explaining – at a minimum – where to learn more about classes and objects not readily available. If you used NuGet to import stuff, will it kill you to copy and paste the command into a comment?

In SSIS, if you’re using variables or parameters or event handlers, add annotation to the control flow or data flow task.

Leave yourself a note.

Practice Good Naming

My friend Joel has a t-shirt based on this tweet:

One the two hard things is naming things. I suggest descriptive names for stuff. Which stuff? In VB or C#, variables, methods, and classes for starters. For SSIS I heartily recommend Jamie Thomson’s iconic post titled Suggested Best Practises and naming conventions.

Good naming conventions promote self-documenting code.

Small Chunks

Coders on all platforms develop a sense of a “good size.” An easy trap to fall into is attempting to “boil the ocean.” Break things up. Practice separation of concerns. Write decoupled code.

You will find decoupled, small, function-of-work code is simpler to test, easier to maintain, and promotes code reuse. 

Conclusion

Future you will thank for incorporating these best practices in your coding, regardless of your software development platform.

Two ADF Presentations Next Week!

I am honored to deliver Moving Data with Azure Data Factory to two User Groups next week!

Abstract

Azure Data Factory – ADF – is a cloud data engineering solution. ADF version 2 sports a snappy web GUI (graphical user interface) and supports the SSIS Integration Runtime (IR) – or “SSIS in the Cloud.”

Attend this session to learn:

How to build a “native ADF” pipeline;
How to lift and shift SSIS to the Azure Data Factory integration Runtime; and
ADF Design Patterns to execute and monitor pipelines and packages.

Dates and Locations

07 Jan 2019 – 6:00 PM ET

Columbus Georgia SQL Server Users Group
Meeting at the Columbus Public Library
3000 Macon Rd
Columbus, Georgia 31906

Register today!

08 Jan 2019 – 5:30 PM ET

Midlands PASS
Meeting at WeKnowIT
440 Knox Abbott Drive
Suite 424
Cayce, SC 29033

Register today!

If you live – or will be – nearby, I hope to see you!

:{>

PS – want more? Join me 04 Mar 2019 for a full-day of training! 
Learn more:
Getting Started with Azure Data Factory – Live, 1-Day Training

Testing SSIS Catalog Compare v4.5

I just posted a preview of the next version of SSIS Catalog Compare at the brand new DILM Suite blog. You can head on over and check out the video is you have five minutes to spare.

I’ll let you in on a secret: the star of the show is the new SCCPAC Deployment Wizard:

The new kid on the DILM block, SCCPAC Deployment Wizard

I demo this utility starting around the 2-minute mark in the video.

Check it out and let me know what you think.

And Happy New Year!

*<:{>

Thinking of You…

The holidays are a joyous and happy time of year. 

Almost everyone struggles in some way with the holidays, though. There are numerous posts about how to have difficult conversations with family who disagree with you on some matter or other – usually politics or religion.

For some folks, though, it’s worse.

Some struggle with depression year round. The holidays seem to pile on with emotional overload combined with factors like longer nights and shorter days and colder weather that keeps people inside (in the northern hemisphere). 

If you know someone struggling with depression in any of its myriad manifestations, whether diagnosed or not; if you know someone who is just… down; please reach out to them during this time of year.

If you are struggling, you are not alone. 

If you are considering suicide, please reach out to someone. I’m here. You can reach out to me. If you cannot reach me, reach out to a friend or pastor, or call 800-273-8255. 

How May I Serve You?

2018 is drawing to a close.

Some readers recently shared requests for more blog posts on certain topics. I thought I’d put the question to my audience (both of you, hi Mom!) to gather your thoughts. Feel free to leave a comment or reach out via email.

Here are some topics I plan to blog about in 2019, Lord willing:

  • SSIS (of course)
  • Azure Data Factory
  • Azure DevOps
  • Data Integration Testing
  • Data Integration Lifecycle Management (DILM)
  • Containers

What do you think? Good list? 

If you were the boss of me, which topics would you like to see – or see more of? Any additions to this list?

Merry Christmas!
*<:{>