A Basic SSIS Catalog Dashboard in Power BI

Announcing the latest addition to the DILM SuiteBasic SSIS Catalog Dashboard in Power BI!

This free download includes a T-SQL script to create a view in the SSIS Catalog database (SSISDB) and a Power BI template for viewing SSIS Catalog executions by Year, Month, Folder, Project, Package, and/or Execution Status.

The Basic SSIS Catalog Dashboard in Power BI is designed to introduce SSIS developers new to Power BI to basic slicer and chart functionality with a familiar and useful demonstration dashboard.

Please Change the Color of the SSIS Progress Tab “Finished” Icon

I’m tuning SSIS for better performance for a large client. The SSIS package I’m working with runs for over an hour and generates thousands of entries on the Progress / Execution Results tab. I wrote a small demo package, executed it, and show the results here to demonstrate:

Why is the Finished icon red?

This slows me down when I’m trying to find the needle-in-a-haystack error(s) that caused my package execution to fail. As I scroll through thousands of messages I have to pause and evaluate the shape and content of each red image. I could find errors much faster if they were the only red icons in the list, which could look something like this:

Why not make them black? or any color – other than red?

Andy

Updates to Integration Services Deployment Wizard

I’m doing a little writing and I noticed something different about the Integration Services Deployment Wizard. Specifically, the Select Destination page now looks like this:

The Authentication dropdown – along with the User name and Password textboxes – are recent additions.

I’m not sure if they’re part of the SSMS 17.2 installation or one of the updates released yesterday, though I’m leaning towards SSMS 17.2.

I haven’t had a lot of time to tinker (still writing), but I discovered that I cannot create an SSIS Catalog Folder in SSIS 2016 when logged in as sa (Mixed Mode installation). Here’s me trying with another (admin) user:

And here’s the error I got when I clicked the OK button on the Create New Folder dialog:

I will update this post when I learn more.

A Custom Biml Framework in Action!

Custom Biml Framework

For years I’ve been dreaming about automating data integration until I needed only a single click to create and populate a target database. In March 2017, I realized this dream!

Versions similar to that shown in this short (2:32) video are in production in several locations:

Enjoy!

:{>

PS – If you want one, please contact Enterprise Data & Analytics.

 

Expert SSIS Training Online with Brent Ozar Unlimited!
IESSIS1: Immersion Event on Learning SQL Server Integration Services – Chicago, 2-6 Oct 2017!

My Latest Book – Building Custom Tasks for SQL Server Integration Services – Is Now Available!

What is the DILM Suite? 2. SSIS Catalog Compare
What is the DILM Suite? 1. SSIS Framework Community Edition
Adding an SSIS Application to SSIS Framework Community Edition
Schedule an SSIS Application with SSIS Framework Community Edition
The Heart of SSIS Framework Community Edition-Parent.dtsx
SSIS Framework Community Edition Updates

The Recording for Enterprise SSIS, Biml, and DILM is Available!
The Recording for Designing an SSIS Framework is Now Available
The Recording for Biml in the Enterprise Data Integration Lifecycle is Available!

IESSIS1: Immersion Event on Learning SQL Server Integration Services – Chicago, 2-6 Oct 2017!

I am honored to once again deliver Immersion Event on Learning SQL Server Integration Services (IESSIS1) in Chicago with SQLSkills 2-6 Oct 2017! You can find additional details here.

Click here to register!

Here’s what I’ll be covering:

Module 1: Introduction

In the introduction we share information about the instructor, course flow, and content, and gather information about attendee expectations for the course. Topics covered include:

  • Training overview
  • Expectations
  • Introductions

Module 2: Creating Your First SSIS package

We believe many learn SSIS best by doing, so we get attendee’s hands on SSIS as soon as possible. In this module, we demonstrate how to build an SSIS package and invite attendees to walk through building a basic SSIS package with us. Topics covered include:

  • Introduction to SQL Server Data Tools – Business Intelligence (SSDT-BI), a version of Microsoft Visual Studio that serves as the SSIS integration development environment (IDE)
  • Visual Studio tips and tricks
  • Exercise: Create an SSIS package

Module 3: Introduction to the Data Flow Task

SSIS was designed to move data. The SSIS Data Flow Task is a complex data integration engine built to support generic data load use cases. In this module, we introduce SSIS Data Flow concepts. Topics covered include:

  • Introduction to the Data Flow Task
  • Connection Manager and Data Flow Task basics
  • Source and destination adapters
  • Exercise: Create a Real Data Flow, Part 1

Module 4: Data Flow Task 201

Once the basics of Data Flow development are understood, we introduce a basic version of the SSIS design pattern called the incremental load. Building idempotent (safely re-executable) SSIS packages is one key to successful data integration in the enterprise. Topics covered include:

  • Intermediate Data Flow concepts
  • Building re-executable data loaders
  • Exercise: Create a Real Data Flow, Part 2

Module 5: Data Flow Task 202

Change detection is one of the most important components of Extract, Transform, and Load (ETL) projects. In this module, we expand the basic change detection introduced in the previous module and begin to examine SSIS design patterns that improve SSIS Data Flow performance. Topics covered include:

  • Advanced Data Flow concepts
  • Building and tuning robust incremental loader
  • Exercise: Create a Real Data Flow, Parts 3 and 4

Module 6: The Control Flow

The SSIS Control Flow is the work flow manager for an SSIS package. In this module, we demonstrate work flow containment and management in the SSIS Control Flow, including advanced Precedence Constraint configuration and package execution control. Topics covered include:

  • Containers, precedence, and work flow
  • Transactions, restartability, and locking

Module 7: Loop Containers

Loop containers provide execution work flow isolation and iterative functionality. In this module, we examine traditional and non-traditional configurations for the For Loop and ForEach Loop containers. Topics covered include:

  • Using For Loop and Foreach Loop Containers

Module 8: Data Flow Task 301

After turning our attention to the SSIS Control Flow, we return to the SSIS Data Flow for an extended period of hands-on labs. In this module, attendees are tasked with the first of three labs that build a robust data integration loader using SSIS, with an instructor available to assist with questions. This lab focuses on transforming the data by applying strong-typing rules and managing data that violates those rules. Topics covered include:

  • Data typing basics
  • Building an incremental loader for real-world data
  • Exercise: Build a Robust Incremental Staging Loader, Part 1

Module 9: Data Flow Task 302

In this module, attendees are tasked with the second of three labs that build a robust data integration loader using SSIS, with an instructor available to assist with questions. This lab focuses on using built-in SSIS Adapter functionality to manage and execute DDL operations during data integration staging development. Topics covered include:

  • Intermediate data staging
  • Managing real-world changes to data sources
  • Exercise: Build a Robust Incremental Staging Loader, Part 2

Module 10: Data Flow Task 303

In this module, attendees are tasked with the third of three labs that build a robust data integration loader using SSIS, with an instructor available to assist with questions. This lab reinforces writing idempotent SSIS Data Flow packages and introduces data quality concepts via data cleansing in SSIS. Topics covered include:

  • Basic data cleansing
  • Deriving values
  • Exercise: Build a Robust Incremental Cleansing Loader

Module 11: Event Handlers, Logging, and Configurations

An important component of any solution is messaging and configuration. In this module, we demonstrate SSIS’s messaging model in the context of event handlers, logging, and managing externalized parameter and variable values. Topics covered include:

  • A survey of SSIS event handlers focusing on OnError and OnInformation events
  • Using SSIS’s built-in logging facility to capture package execution details and statistics
  • Using built-in package configurations to externalize variable values

Module 12: Security, Deployment, and Execution

SSIS facilitates secure connection and parameter value management. In this module, we build on our understanding of messaging, logging, and externalization by examining project, package, and connection security. We then turn our attention to deployment and execution patterns, and discuss the role of security in each. Topics covered include:

  • SSIS Package deployment options
  • Security implications
  • SSIS Package execution

Module 13: ETL Design Patterns

SSIS is a powerful enterprise data integration engine. In this module, we discuss experiences using SSIS to solve complex and “edgy” use cases, and some patterns we’ve realized from those engagements. Topics covered include:

  • Leveraging less-documented features of SSIS
  • Using Parent-Child design pattern to achieve “hands-free” custom logging and creative custom configuration
  • ETL Instrumentation

Module 14: Enterprise Execution Patterns

Data integration in a large enterprise is different from data integration in a smaller shop. In this module, we discuss patterns of SSIS development and management that lower the total cost of owning a data integration solution built using SSIS. Topics covered include:

  • Leveraging the Parent-Child design pattern and much of what we’ve learned over the past four days to build a metadata-driven SSIS execution framework engine

I hope to see you there!

:{>