The Recording for Loading Medical Data with SSIS is Available

Kent Bradshaw and I had a blast delivering Loading Medical Data with SSIS earlier today! If you missed the webinar and, perhaps more importanly, coupon codes to save on upcoming Enterprise Data & Analytics Training.

Enjoy the video!

We demonstrated a handful of (free!) DILM Suite (Data Integration Lifecycle Management) utilities:

Join us next week for another free webinar: Enterprise SSIS Execution!

Free Webinar – Enterprise SSIS Execution

Join Kent Bradshaw and me as we present (another) free Enterprise Data & Analytics webinar Tuesday, 23 Apr 2019 at 12:00 PM EDT: Enterprise SSIS Execution.


SQL Server Integration Services (SSIS) is a powerful enterprise data integration tool that ships free with Microsoft SQL Server. Join Andy Leonard – Microsoft Data platform MVP, author, blogger, and Chief Data Engineer at Enterprise Data & Analytics – and Kent Bradshaw – Database Administrator, Developer, and Data Scientist at Enterprise Data & Analytics – as they demonstrate several ways to execute enterprise SSIS.

Join this webinar and learn how to execute SSIS from:

  • SSDT (SQL Server Data Tools)
  • the Command Prompt
  • the SSIS Catalog
  • a metadata-driven SSIS Framework

Register today!


Training Feedback

Enterprise Data & Analytics

Here’s what students are saying about the recent Enterprise Data & Analytics free webinars and paid training:

Thank you for presenting the Expert SSIS training sessions Monday and Tuesday this week. I, for one, learned something during each session. Overall, this was one of the top two or three most useful training courses I’ve attended in years.

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:

[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!


My Day – Thursday, 19 Jul 2018 – #SQLCareer

#SQLCareer post for Thursday, 19 Jul 2018

4:26 – 5:30 AM

– Wake up (beating the alarm), weigh in, pray, read the Bible, coffee, check the burn barrel.
Christy and I have been on a keto diet since 5 Mar. I started out at 238 pounds. This morning I weigh 199 pounds. I’ve been bouncing in the 196-203 range since early May. Part of the reason for the plateau is cheating on the diet (diet fatigue). Part of the reason is I’ve probably lost what I can with diet alone and need to add more exercise. My exercise lately has been work around the farm.
There’s a bunch to pray about. I start with praise followed by gratitude, thanking God for another day.
I’ve been reading through the Book of Psalms recently and this morning I read 149 (of 150). I’m also studying the books of Romans and Colossians.
Coffee: ‘Nuff said.
I’m slowly burning limbs and brush from areas I’m clearing around the farm. There are hot coals from the three larger pieces I put in the barrel last night. I add three more larger pieces.

5:30 – 6:00 AM

– Write. I create a Notepad document to record my day for the next few days. After reading Brent Ozar’s first entry last night – in which he reminded me of Steve Jones’ SQLCareer post earlier this month – I decided to share a few days.
Startup vDemo – my demonstration virtual machine – in preparation for today’s Summer O’ ADF webinar titled Use SSIS Catalog Compare to Lift and Shift SSIS to ADF.
While vDemo is starting, I log into Azure Data Factory and start my Integration Runtime. The webinar is over 6 hours from now but I don’t want to wait until the last minute – it takes 20-30 minutes for ADFIR to start. I note the free Azure account I set up for the Summer O’ ADF has $181 left and is probably down to about 10 days remaining in the free month with $200 of credit. I note the accounting doesn’t (yet) reflect the 5 hours or so I had Integration Runtime running yesterday. My Azure spend has latency and I am attempting to monitor the latency. At present I see resource usage reflected in billing 36-60 hours after the resource usage.
I fire up vSQL2014 to begin testing SSIS Catalog Compare v3.2.1.1 (Preview). I’ve been working on this latest release of Catalog Compare for a year. Or more. Reasons for the delay, in order:
0. I bit off a lot. I made major changes to the graphical (treeview) representation of the SSIS Catalog. If you’ve been playing along at home, you can see these changes in the free DILM Suite utility, Catalog Browser. Catalog Browser and Catalog Compare share a lot of code.
1. Enterprise Data & Analytics landed two large data engineering projects at about the same time one year ago. One client doubled their request at the beginning of 2018 which resulted in doubling our people. Both projects are done but the both clients indicate they will have more work for us in the future. I still deliver consulting work – designing and building SSIS and database solutions – while managing the company. Nick Harris manages the business development cycle; a skill at which he is most gifted and for which I have neither proclivity or the inclination to proclive (…to quote the sage wisdom of Tom Smykowski [Office Space], “Engineers are not good at dealing with customers.”). Consulting and management took time away from software development.
2. I am a C# n00b. A year ago I was working my way up to becoming a C# n00b. (This maybe should have been first…)
While walking through the script for today’s webinar, I found a bug in the scripting of SSIS parameter literals. Booleans are… difficult. The string value of a Boolean is “true” or “false.” The corresponding T-SQL values are 1 or 0. Since SSIS Catalog parameter values are stored as sql_variants, getting the value right is vital. True/false not only doesn’t work, it causes the T-SQL script to fail. Hence, SSIS Catalog Compare v3.2.1.0 (Preview) was updated yesterday to SSIS Catalog Compare v3.2.1.1 (Preview). I’ll put it through some paces this morning by running through the webinar script again. If all goes well, I will drop it into the Dropbox folder and let the Early Adopters know there’s a new version available.
Software development is hard. Product development is harder. Product management is harder still. I am getting there…

6:00 – 6:30 AM

– Check the Buffer(.com) buffer. Load it up with posts for today from and a few other sites.
Check the results of yesterday’s newsletter campaign.
Look over stats from the business websites:
Enterprise Data & Analytics
DILM Suite
Biml Academy – more sales this month compared to last month, most likely due to new content.
SSIS Academy – launched this week and has decent traffic for a newly-launched site.
Poke around on social media for a few minutes. I start with LinkedIn and use it more than Twitter and Facebook these days. I really like the Daily Rundown notification and read at least the headlines each day. The article that catches my attention today is about drug companies retracting price hikes or announcing they will not increase drug prices in response to a tweet by the president. I’m drawn to this on a couple levels:
1. Many of Enterprise Data & Analytics’ customers are medical-related.
2. I am a Type-2 diabetic and one of my medications is expensive (~$500/month). (Related: the keto diet I mentioned earlier has helped. I now take 1/3rd of the medications I used to take and my blood sugar is lower.)
I also read about Amazon’s Prime Day and how it improved revenue for other online retailers, specifically Target.

6:30 – 7:45 AM

– I get a low battery warning on the laptop. I don’t always bring the laptop downstairs after work but I did yesterday hoping I’d get a chance to look at the SSIS Catalog Compare bug I’d identified earlier in the day. I did have time and I fixed it (I think) – more testing today… I shut down the laptop for now.
I make a keto-friendly breakfast for Christy and me and fry up some bacon for Emma and Riley (Stevie Ray is at camp this week). I put Christy’s omelette (spinach and cheddar) in the preheated-and-then-shut-off oven along with the bacon for the kids. I have an omelette and some fried bologna. The keto diet is weird if you’re used to non-fat diets. Fat is part of the keto diet, as are complex carbs. And it’s time for my second liter of coffee.
I catch up on some reading while eating. I am currently reading The Holiness of God by R. C. Sproul during mornings and The Culture Series by Iain M. Banks before sleeping. Reading is important and I enjoy both reading and writing.
I check the fire a couple times to make sure the wood I added is catching up. It’s not, so I add some more, smaller pieces this time. The wood I put in before sunrise was damp with the light dew. Sitting on the coals has dried it out and the twigs catch in a few minutes.
I load and start the dishwasher. I forgot about the clothes in the washer last night – it was a busy and late night for me with praise band rehearsal – so I re-wash that load.
I empty the trash and make a run to the landfill, checking the fire on the way out and when I return. It’s burning nicely.

8:00-8:20 AM

– I get to the office and settled a little before 8:00 AM. The laptop battery is happy (well, happier, as it charges).
I contemplate live-blogging the day and decide I will not be able to provide many updates, but it’s worth starting, and so I do. After some proofreading, it’s ready to publish.

Update 1: 8:20-8:45 AM

– I proofread (again!) this post and correct typos and misspellings and verb tenses and… you get the picture. I promise I proofread it in Preview. But for some reason, it’s not the same as reading it after it’s posted.

I pop open Outlook and check my email. Nick has closed another gig for what we call “SSIS Review.” I need to add a page to the Enterprise Data & Analytics website about this service. We’ve been doing a lot of this lately. We typically set the contract at not-to-exceed 40 hours (minimum) and only invoice for hours as they are consumed (unless the customer requests otherwise… some customers have budgets that they must consume within a fiscal year. If the customer requests it, we invoice up front.).

I also see a Customer Status Update from Penny. Penny has been working with Enterprise Data & Analytics for a couple years now. She’s my daughter, Mom to three of my favorite five grandchildren, and quickly becoming a senior SSIS developer. I’m proud of her.

Time to hit Update and post about this post on social media.

More later…

Update 2: 8:45-9:45 AM

I restarted vDemo and starting walking through the script for today’s webinar.

I put the wash in the dryer and started a new load. I propped open the dishwasher so the load that just finished will dry faster.

I checked the fire again and, durnit! Still not going like I want. I added some more small stuff and let it set for a while. It’s started burning well as you can see here.

I rescued a small bird from a couple of our cats that must’ve just left the nest.

I posted a LinkedIn video about the upcoming webinar:

Update 3: 9:45-10:45 AM

Webinar script rehearsal is complete and was successful.

Update 4: 10:45-11:45 AM

Took a break away from the laptop. Why? I like to do something else before presenting – something… different. Plus it’s a gorgeous day in Farmville Virginia with surprisingly low humidity for this time of year. Emma and I took advantage of the remaining coolness of the morning and piled some brush near the burn barrel.

It’s now time to start the webinar.

Update 5: 11:45 AM-1:15 PM

The webinar is done and, by all indications, was a success. It’s lunch time!

Update 6: 1:15-2:30 PM

Lunch was keto-friendly, of course. A salad and a meat-stuffed squash.

Christy and I dropped off her van across town. It needs new tires. The shop tells us they can get it done today which is great because we have a couple other errands to run this afternoon – we’re meeting friends from church and I am getting a haircut (or several hairs cut…).

Update 7: 2:30-4:30 PM

I posted The Recording for Use SSIS Catalog Compare to Lift and Shift SSIS to ADF is Available.

I got a haircut.

I restocked the burn barrel.

I edited the PowerPoint and lab document for the upcoming SSIS Academy Data Flows 2 course. Online courses take probably more time to build than you think – especially if you do it right. I spent a lot of time documenting the Data Flow 1 course. I’m spending just as much time going through the documentation for the Data Flow 2 course, editing and clarifying. Why? I don’t want to have to try to remember (or figure out) the mechanics of publishing a course each time. I want to spend my brain cycles on developing awesome courses!

At this point, I’ve been at it for over 12 hours. Do you know what entrepreneurs call a 12-hour shift? A half day.

Update 8: 4:30-6:00 PM

I updated pages at Biml Academy, SSIS Academy, and Enterprise Data & Analytics.

The shop finished with Christy’s van so I took her across town to pay for the work and pick it up.

I folded the last of the clothes.

I captured the video and began editing the audio script for SSIS Academy Data Flow 2 lecture. I hope to record the lecture audio tomorrow but it may have to wait until Saturday – tomorrow we pick Stevie Ray up from camp.

It’s time to take another look at the burn barrel, grab supper with the sexiest redhead on the planet, watch some Food Network, read some Culture Series, and then get a good night’s rest.

I am thankful. Today was a pretty good day.


Free Webinar – Designing a Custom ADF SSIS Execution Framework

Folks, I’m not going to even pretend to be cool and collected about this webinar about designing an ADF SSIS Execution Framework. I’ve been waiting for this one for a loooooooong time! The next (free!) webinar in the series I’m calling the Summer-O’-ADF is Designing a Custom ADF SSIS Execution Framework.

It’s at noon EDT Thursday 12 Jul 2018.

Why Am I So Excited?

I’ve been designing data engineering frameworks since the days of DTS (Data Transformation Services). Enterprise Data & Analytics (EDNA) implements SSIS Frameworks for enterprises. We even give away a free version of a framework at DILM Suite (DILM == Data Integration Lifecycle Management). It’s called SSIS Framework Community Edition and it’s not only free, it’s open source.

I wrote about SSIS Framework Community Edition and Data Integration Lifecycle Management in my latest book: Data Integration Lifecycle Management with SSIS.

I’m excited about this webinar because it combines a long-held passion – DevOps for SSIS – with a new passion – Azure Data Factory (ADF)! I cannot wait to show you how!

If you haven’t seen my webinars titled The Azure Data Factory Controller Design Pattern and ADF Controller Design Pattern with the SSIS Integration Runtime (also part of the Summer-O’-ADF webinar series), you may want to check them out first (registration required).

Register today!