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.

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.

I hope to see you there!

PS – Want to Learn More About Azure Data Factory?

Follow Andy Leonard’s SSIS Training page for more information.

SSIS, Docker, and Windows Containers, Part 1 – Why?

In my first post in this series, SSIS, Docker, and Windows Containers, Part 0 – Getting Started, I covered:

  • Installing docker
  • Choosing a Container OS
  • Pulling a test image
  • Pulling a Windows Container with SQL Server installed
  • interacting with with your instantiated container

In this post I reveal my ultimate goal and then travel down two rabbit holes to explain:
1. How I got here; and
2. One way to get answers to questions no one wants to hear.

My Ultimate Goal

My ultimate goal is to use one or more Windows containers running SQL Server to test SSIS. I can hear some of you thinking,..

“Why, Andy?”

My response? “Cuz.”
And, “I do what I want!”

Seriously, since I hopped the fence from developer to data I’ve dreamed of the day when I could practice lifecycle management with data-stuff like I used to practice lifecycle management with software development.

I recognize the obstacles. The greatest obstacle (in my humble opinion) is software is mostly stateless these days (these days started with Object-Oriented Programming and include its descendants). Stateless development solves lots of engineering problems in lifecycle management, and by “solves a lot of engineering problems” I mean some engineering problems simply don’t exist so lifecycle management for stateless stuff can simply ignore “a lot of engineering problems.”

A database, on the other hand, is all about that state. When it comes to managing lifecycle for a stateful platform – like a database – ACID gets dumped on many lifecycle management tools and solutions (see what I did there?).

Is it possible to manage a data-related lifecycle using stateless tools? Yes. But here there be obstacles. Let’s look at on use case:

Use Case 0

Your team releases an update that adds one column to one table. The column receives data. Someone realizes there’s a bug and determines a rollback is the solution. The web and middle-tier teams successfully rollback the release using functionality built into the release-management / DevOps enterprise tool.

What happens to the column in the table?

  • Is it dropped?
  • Is the data deleted?
  • Since this isn’t a post about data lifecycle management, let’s not even consider what happens if the data is considered personal-identifying-information or some tuple otherwise subject to privacy regulations.

You see the problem? The answer is always going to be, “It depends.”

This is the problem I am trying to solve, specifically for data integration.

I want to be able to easily deploy and rollback data integration deployments. But to accomplish this, I need more metadata (data about the data) and a bunch more automation. Why? My stuff has state – by definition.

“Not Supported”

If you have not yet figured this out, Microsoft is a software development shop. They’re people trying to use software to solve business problems for customers – just like you and me. Just like you and me, they have schedules and deadlines and competition and a need to generate a profit. in other words, Microsoft is a business.

When some redneck from Farmville Virginia starts asking all sorts of out-of-scope questions, he sometimes hears, “That’s not supported, Andy,” in response. Ask me how I know.

So it helps to be stubborn persistent. Persistence sometimes takes the form of thinking of another approach. When I began probing for ways to test SSIS in containers, my ultimate goal was to have the tests simulate Production as closely as possible. In 2019, Production SSIS means executing SSIS in the SSIS Catalog.

Is it possible to execute Production SSIS from the file system or the MSDB database in 2019? It is. I’ve not found a good use case for executing SSIS outside the Catalog, though – not in 2019. I am not trying to hurt anyone’s feelings, I promise. I’m just saying that the SSIS Catalog has been out for north of six years at the time of this writing. Plus: The only way to execute Azure-SSIS packages is via an SSIS Catalog in the cloud (again, at the time of this writing).

At Enterprise Data & Analytics we help clients migrate from file- / MSDB-based SSIS execution to the SSIS Catalog (both on-premises and Azure-SSIS). Some clients realize the benefits of SSIS Catalog-based execution and call us for help with training and consulting. Others ask for help performance-tuning SSIS, and we almost always recommend using the SSIS Catalog because SSIS packages almost always execute faster when executed from the Catalog. When do we not recommend using the SSIS Catalog? Some enterprises have designed their data environments in such a way that using the SSIS Catalog would require a major shift. The technical portion of the shift would be relatively straightforward, but – often – the cultural shift is more disruptive. Not all enterprises are willing to make such a cultural shift; to endure the disruption.

Getting Around “Not Supported”

When I started asking questions like, “Is it possible to run the SSIS Catalog in a Windows container?” the response was, “That’s not supported.”

Fine. What is supported? Running the SSIS Catalog on SQL Server installed on Windows Server Core. I was trying to solve many of the same problems in a container that would need to be solved running an SSIS Catalog on Core. It turned out many of these problems had indeed already been solved.

More Information

If you can make it to SQL Saturday #813 – Boston BI Edition on 30 Mar 2019, Liz Baron and Sreeja Pullagura are delivering an information-packed session titled SSIS DevOps with Azure Containers on this very topic. I’ve had the privilege and honor to work with Liz, Sreeja, and the awesome data engineering team at Teach For America – a worthy non-profit.

I predict Sreeja’s and Liz’s presentation is going to be a session that moves the needle for Data Integration Lifecycle Management (DILM).

My next several blog posts on this topic – sharing some of my efforts and what I’ve learned about executing SSIS using containers – will begin appearing 31 Mar 2019.

Shameless plug: I am delivering Intelligent Data Integration with SSIS – a full-day pre-conference training session – Friday, 29 Mar 2019.
Register here!
I hope to see you there!

:{>

“Andy, I Want More ____”

I am always learning.

I enjoy learning and this trait led me to technology in the first place. It turns out a love of learning is all but essential for technologists.

After I learn, I share what I’ve learned in blog posts, webinars, and via presentations at SQL Saturdays and events like the PASS Summit.

What Do You Want to Learn?

Drop me a comment and let me know what you’d like to learn! I may take your suggestion and run with it.

:{>

The Recording for Troubleshooting SSIS (Part 1) is Available

A fantastic crowd attended today’s (13 Mar 2019) free webinar titled Troubleshooting SSIS. If you attended you got the full show. If not, you may view the content at SSIS Academy for a nominal fee.

Enjoy this focused content to SSIS Self-Help, a service from Enterprise Data & Analytics designed to help folks quickly and affordably overcome SSIS challenges.

Preview Available

The first two segments of the presentation are available for free at the top of this post. In the full version I walk you through:

  • Design-time validation
  • Debugging most SSIS tasks
  • Debugging the Script Task
  • Debugging the SSIS Data Flow Task
    • Using Data Taps
  • Debugging the Script Component

Enjoy.

:{>

T-SQL Tuesday #112 – The Cookie Jar

This month, Shane O’Neill hosts T-SQL Tuesday. His topic is titled Dipping into the Cookie Jar and is inspired by David Goggins‘ book titled Can’t Hurt Me.

Coincidence: I am currently listening to the Can’t Hurt Me audio book.

I made several recent mini-road-trips to Richmond to visit my Dad (more later) – a 75-mile trip (one-way) – and I listened to most of the first four chapters while making those recent trips.

I’ve been following Goggins for a while on Instagram, where he posts about against motivation and about overcoming one’s perceived limitations. He is a practitioner of what I’ve described as “transmuting life’s poison,” the act of using bad things that have happened to you as fuel for achieving good (great, even) things in life. Transmuting the poison is a reference to Dune.

Goggins had a hard life. He was dealt a crappy hand. He did stuff to his own detriment. He pulls no punches about his situation or the source of his woes. What he did with those things, though – how he transmuted the poison – is nothing short of inspiring.

My Stuff

I’m not prepared to share everything now (and may never be), but here’s some stuff that I’ve endured that was self- and / or other-inflicted:

  • Been on welfare
  • Caused a crisis pregnancy
  • Been fired (multiple times)
  • Failed at business
  • Been financially broke (more than once)
  • Been divorced

The latest happened Saturday morning when my Dad passed away after a couple months of declining health. I am not so naive as to think this is as difficult as what others have faced; some for years, even. But it was not nothing and it has challenged me on many levels – some of which are absent from the list above.

He was in Richmond when he died.
He’s the reason I made so many trips to Richmond recently.

Cookies

Like Goggins, I used the darkness of those (and other) experiences to power through some tough experiences in life. What have I achieved by transmuting this poison?

  • Family – beyond what I thought possible or even dreamed; cool and intelligent children; an awesome, beautiful, cute, sexy, cute, smokin’-hot wife.
  • Friends – real friends who lay down their lives for you.
  • Serving our Community – I am honored and humbled to be a member of our Community. I’m a redneck with an Associate’s Degree. I don’t deserve the honor our community bestows.
  • Being published – a kid who made almost straight D-minus’s in high school English.
  • Weaponized ADHD – using the compulsive part of obsessive/compulsive to work multiple days with little or no sleep to get the job done, using the obsessive part to make the obstacles quit before I do. I owe my brother from another mother, Frank LaVigne (franksworld | @Tableteer | DataDriven), credit for the term weaponized.
  • Military service – like Goggins I served in the military, although I did not complete training anywhere near as difficult as he did and I never experienced combat. Basic Training is not nothing, though, and many lessons from boot camp remain with me still. One thing Goggins says with which I wholeheartedly agree: “It’s all a mind thing.” Once you get your mind right, you are unstoppable and unbreakable.

When the going gets tough I remember these things, but this is not all and they are not even the…

Most Important

God is good, all the time. He doesn’t allow things to happen to me that I cannot bear. I confess, however, there have been times – some recently – when I have glanced toward Heaven and asked, “Are You sure You have the right Andy?”

At this time, with the normal stresses of life intermingling with some not-as-frequent stresses of life, I am thankful for Jesus carrying me and surrounding me with sisters and brothers who express their love and caring, and who sacrifice their time and talents to demonstrate their love.

For you see, God is the true “transmuter of poison.”

In Isaiah 61:2b-3, God speaks through the prophet:

“To comfort all who mourn,
To console those who mourn in Zion,
To give them beauty for ashes,
The oil of joy for mourning,
The garment of praise for the spirit of heaviness;
That they may be called trees of righteousness,
The planting of the Lord, that He may be glorified.”

Beauty for ashes.
Joy for mourning.
Praise for heaviness.

Dad is a Christian (present tense intended). This life is part of Christianity.
Though we had our differences, I miss Dad.
But I will see him again.

Peace.

Free Webinar – Faster SSIS

SQL Server Integration Services (SSIS) is a powerful enterprise data integration tool that ships free with Microsoft SQL Server. Join me as I demonstrates practical SSIS performance design patterns.

Register today to learn:

  • Load BLObs faster
  • An Incremental Load design pattern using HashBytes
  • Using a Script Component Destination

This webinar is free!

Register today!

Andy’s Database Credentials Hack

Ok, this isn’t a hack. It’s more of a tip that may help you protect data. Some of you would not have clicked a link to Andy’s Database Credentials Tip. You are welcome.

LastPass

I like LastPass. I pay for Premium and it’s not a bad deal, in my humble opinion.

I also like Azure – a lot. As a Microsoft Data Platform MVP, I get a free subscription to Azure. But there was a time when I paid for my Azure subscription. It was worth it, in my humble opinion.

Provisioning an Azure SQL DB

When it’s time to provision an Azure SQL DB, I sometimes configure a username and password. (You can also use Azure Active Directory to connect to an Azure SQL DB instance).

Back in the day, I would use some generic – but easy to guess – name for the Administrator account. Something like “andy,” for example.

The LastPass Chrome extension includes a Generate Secure Password option:

I use this to generate the Server Admin Login value:

(click to enlarge)

My thinking? Why give away half the key-value pair for logins?

Conclusion

LastPass isn’t the only way to generate a random string. I leverage it because, well, it’s right there in my browser already.

I cannot provide metrics for how often this trick has thwarted would-be attacks. I also do not know if this even slows bad guys down these days. But I’ve been doing this for a while and – if nothing else – it gives me a little peace of mind.

:{>

SSIS, Docker, and Windows Containers, Part 5 – Deploying to the SSIS Catalog

In the previous five posts I:

  • examined how to install Docker for Windows, pull a test image, search for a SQL Server on Windows image, install and interact with it (Part 0)
  • shared why I want to do all this (Part 1)
  • shared one way to execute SSIS on a container in Part 2
  • shared a failed attempt to add an SSIS Catalog to a SQL Server-on-Windows container in Part 3
  • shared a successful attempt to create an SSIS Catalog (Part 4)

I Have Not Yet Figured Out…

  1. I do not (yet) know how to deploy to a container using the Integration Services Deployment Wizard. This bugs me and I want to figure out some way to do it because deployment testing is critical for SSIS.
  2. I have not figured out how to execute SSIS packages in an SSIS Catalog in a container using the SSMS Object Explorer’s Integration Services Catalogs node.

What I Have Figured Out

  1. I know how to deploy SSIS packages to an SSIS Catalog (the topic of this post); and
  2. I know how to execute them from the SSIS Catalog (the topic of the next post) – both using PowerShell.

Deploying an SSIS Project to the SSIS Catalog

Returning to Matt Masson’s PowerShell script – combined with the docker volume added earlier – I have a means to deploy an SSIS Project to the SSIS Catalog in the container.

I copied an ISPAC file into my D:\docker\install directory:

I can access this ISPAC file from within the container:

From Matt’s post, I execute the following slightly-modified PowerShell script:

# from https://www.mattmasson.com/2012/06/publish-to-ssis-catalog-using-powershell/
$ProjectFilePath = “C:\install\TestSSISProject.ispac”
$ProjectName = “TestSSISProject”
$FolderName = “Test”
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Management.IntegrationServices”) | Out-Null;
$ISNamespace = “Microsoft.SqlServer.Management.IntegrationServices”
$sqlConnectionString = “Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;”
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
$integrationServices = New-Object $ISNamespace”.IntegrationServices” $sqlConnection
$SSISCatalog = “SSISDB”
$catalog = $integrationServices.Catalogs[$SSISCatalog]
$folder = New-Object $ISNamespace”.CatalogFolder” ($catalog, $FolderName, “Folder description”)
$folder.Create()
[byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
$folder.DeployProject($ProjectName, $projectFile)

When I execute this script, my results appear as shown here:

(click to enlarge)

View in SSMS

If successful, I can view the deployment in SQL Server Management Studio (SSMS) after refreshing the SSISDB node in SSMS Object Explorer’s Integration Services Catalogs node:

Conclusion

The best way I know to deploy SSIS projects to an SSIS Catalog in a container is using PowerShell.

Next we tackle executing SSIS packages deployed to an SSIS Catalog in a container.

2007-2012, 2019-

I am deeply honored to be awarded Data Platform MVP by Microsoft for 2019-2020.

I was a SQL Server MVP – that’s what they used to call Data Platform MVPs back in the olde days when we used to carve our own IC’s out of wood – from 2007 to 2012.

I stepped away from the program in 2012 to address personal matters that, well, took priority over community participation. Stepping away was the right call. The personal matters are better now.

Azure, Power BI, AI, ML, and Azure Data Factory (ADF)

I focus a lot on Azure these days. Enterprise Data & Analytics (EDNA) helps clients lift and shift SSIS to Azure Data Factory’s SSIS integration Runtime, also know as Azure-SSIS.

At EDNA, our teams deliver ADF concierge, consulting, and training services. Our newest team members are masters of Power BI, Artificial Intelligence, and Machine Learning. This makes sense because data engineering is the largest part of any enterprise Analytics, ML, AI, or BI endeavor.

We are here to help.™

SQL Server integration Services (SSIS)

I was initially awarded MVP in 2007 for my community work, blogging, and writing regarding SQL Server Integration Services (SSIS). At least, I think that’s why I was awarded. MVP criteria was – and remains – a closely-guarded secret at Microsoft. That’s as it should be.

There is still plenty of SSIS out there performing data integration for tens of thousands of enterprises. I and my teams at EDNA continue to write and blog and train and deliver SSIS consulting to support these organizations.

We also built and maintain the Data Integration Lifecycle Management (DILM) Suite. DILM Suite is a collection of for-sale and free tools and utilities to help enterprises practice lifecycle management or DevOps with SSIS. Some of the the free stuff is open source, even. Check it out.

Business Intelligence Markup Language (Biml)

I am honored to a BimlHero.

If you are unfamiliar with Business Intelligence Markup Language (Biml) and work with SSIS, ADF, or SSAS (SQL Server Analysis Services), you should check out Biml.

It’s pretty neat.

Thank You, Microsoft!

Again, I am honored to be awarded MVP.

Thank you, Microsoft!

:{>

SQL Saturday 813 – Boston #SSIS Precon 29 Mar 2019!

I’m honored to deliver Intelligent Data Integration with SSIS – a full-day pre-conference session – at SQL Saturday 813 in Boston 29 Mar 2019!

What is Intelligent Data Integration?
SSIS (SQL Server Integration Services) packages developed using tried and true design patterns, built to participate in a DevOps enterprise practicing DILM (Data Integration Lifecycle Management), produced using Biml (Business Intelligence Markup Language) and executed using an SSIS Framework.
Attend a day of training focused on intelligent data integration delivered by an experienced SSIS consultant who has also led an enterprise team of several ETL developers during multiple projects that spanned 2.5 years. And delivered.
Attendees will learn:

  • a holistic approach to data integration design.
  • a methodology for enterprise data integration that spans development through operational support.
  • how automation changes everything. Including data integration with SSIS.

Topics include:

  1. SSIS Design Patterns
  2. Executing SSIS in the Enterprise
  3. Custom SSIS Execution Frameworks
  4. DevOps and SSIS
  5. Biml, Biml Frameworks, and Tools

Register today!

I hope to see you there!

:{>