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.

Training Feedback

Enterprise Data & Analytics

Here’s what students are saying about the free Enterprise Data & Analytics webinars this month:

I loved the way you present and blend it with the demos Andy.

The advanced level of knowledge required kept the event moving at a frantic pace!

I always learn something from Mr. Leonard.

Andy’s training style is to-the-point and experience based, not a recitation of textbook process. He engages the audience with real-world experiences demonstrating useful application of the products he teaches. I find his insights invaluable.

Andy used SSDT demo mode for the exercises, showing exactly what I could expect along with navigation tips and shortcuts using SSDT. I also like the fact that Andy made a point to make sure we, as the audience, was paying attention to the important sections.

Presentation was straight to the point, useful, and with no unneeded fluff. Also, no PowerPoint presentation! Just real-world demos covering the topic being discussed.

Concise and easy to follow. Seems like it would be useful to students of varying skill levels.

I look forward to more of these, Andy. You are a terrific ambassador to the profession.

I’m always grateful when the experts are willing to freely share knowledge.

Yall make an old man blush!

I enjoy delivering training. I find it’s a great way to learn a topic is to deliver training on that topic.

Stay tuned, there are more free webinars on the way! The best way to keep up is to subscribe to my mailing list.

:{>

The Recording of Faster SSIS is Available

The recording for the free webinar titled Faster SSIS is available and may be viewed above. Thanks to everyone who attended!

Regarding “Bad” BLOBTempStoragePath Settings

Regarding the BLObs.dtsx demo SSIS package, Doug asked a really good question:

“Does the package abort if the [BLOBTempStoragePath] folder path is invalid or does it create it for you?”

The answer is: The package fails. The path is not created for us.

(click to enlarge)

The errors are:

Error:
Error:
Error:
[OLE DB Source [46]] Error: Failed to retrieve long data for column “Demographics”.
[OLE DB Source [46]] Error: There was an error with OLE DB Source.Outputs[OLE DB Source Output] on OLE DB Source. The column status returned was: “DBSTATUS_UNAVAILABLE”.
[OLE DB Source [46]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “OLE DB Source.Outputs[OLE DB Source Output]” failed because error code 0xC0209071 occurred, and the error row disposition on “OLE DB Source” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on OLE DB Source returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

(click to enlarge)

Enjoy the video!

:{>

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.

:{>