Archive
SSIS and DelayValidation
If you’re a regular reader then you’ll know I’ve recently become enamored of SSIS. I’m absolutely blown away by this product, I wish I learned about it 5 years ago!
Of course, being new to such an extensive technology means dealing with landmines along the way. Unfortunately, I’ve run into a couple of them already. The first one was my own fault: I developed a project in 2008 but the client has Sql Server 2005. To solve it I had to install Sql Server 2005 and create a 2005 version of the Package.
AcquireConnection Method Error
The second issue I’ve encountered was hardly as intuitive. I was at the client site yesterday deploying my first SSIS Package outside my own network. With the help of Andy Leonard, my SSIS mentor, I had worked through all the necessary steps to make the Package dynamic, a process worthy of a blog post of its own. At the client site I followed my cheat sheet to the letter, explaining every step to the client along the way. After solving a missing prerequisite problem on the server, we fired up the Package only to be quickly stopped with a Validation failure message:
The AcquireConnection method call to the connection manager failed with error code 0xC0202009.
I assumed there was a problem with the dynamic connection strings. I spent about an hour diagnosing the problem only to find out there was no problem, at least not with the dynamic connections. The problem, it turns out, was in a thing called DelayValidation.
Dynamic vs. Design Time Connection Strings
When you design an SSIS Package, you need to define connections to data sources and destinations that you can access at design time. Without these connections, SSIS would be pretty useless. So these design time data connections have to be hard coded somewhere: I put mine in package variables and bind the ConnectionString property of the ConnectionManager objects to the variables. These variables are then overridden at runtime by external values, making the connections dynamic and portable.
What I was unaware of was that each ConnectionManager object and each DataFlow Task has a property called DelayValidation. When the Package begins, it validates all of the DataFlow tasks prior to executing any of them. If DelayValidation is set to False, then the validation process uses the design time connection objects. In other words, it tries to connect using that connection string before it has a chance to be set by the dynamic process. And here’s the kicker: False is the default value, so this is the default behavior.
Solution
Working on my own network, I never even noticed this was happening. On the client network, however, it immediately blows up because the tasks cannot connect to my design time databases. I found a good article online that explained the problem and it actually makes sense. The solution is to set DelayValidation to True, which will delay the validation process until the task begins to execute, giving it time to be set dynamically.
I started by setting the property on every ConnectionManager object. At first I believed that this would be sufficient but it didn’t stop the validation error: I had to do the same thing to every DataFlow task as well, after which the Package ran properly. This was a real pain because I had so many to change. I could select multiple tasks within the same container, but I could not select tasks across container boundaries, and I have a lot of containers.
While I understand what DelayValidation is doing and why it does it, I don’t understand why False is the default value. If nothing else, it seems to me this should be a Package level setting. If you know of a better or easier way to handle this problem, please share it in the comments below.
Richmond Code Camp X
This past weekend, about 200 geeks, developers, and other techies converged on the Parham Road campus of J. Sargeant Reynolds Community College in Richmond, VA for Richmond Code Camp X. I think it’s great when an event has been around long enough to begin using Roman Numerals.
Planning and Organization
Regular readers already know I am a huge fan of Code Camps and #RichCC is as good as they come. Of course, you could claim that I am a little biased: I’ve been on the planning committee for the event for the last two installments. But believe me, this was a great event long before I got here!
In fact, one of the things I wanted to address today was how the Richmond Planning Committee is a great model for others to adopt. Andy Leonard wrote about some of this on his blog:
There’s a lot that goes into planning an event of this magnitude. I commend the Code Camp Leadership Team for their hard work, but the team possesses a quality that will ensure many more Richmond Code Camps to come: absence of ego.
There’s no penalty for stepping up or down on our team. Life happens, people move, get more and less busy, change jobs, and just have other plans. Why punish people for that? Especially in a volunteer organization? The on-point person doesn’t try to "edge out" previous leaders. That’s because we’re all pretty secure individuals. It’s a great group to be part of, and I love every member of our team.
I think he really hit the nail on the head when he used the phrase “absence of ego.” In the spring, Kevin Griffin and I were invited to join the committee for RCC2010.1. This was/is a serious group of community activists: Kevin Hazzard, Justin Etheredge, Darrell Norton, Robin Edwards, Susan Lennon, Frank La Vigne, Andy Leonard, Kevin Israel, and please forgive me if I left anyone out.
Each of these folks is a super star in their own right, so it would shock no one to find politics, backstabbing, prima donnas, or any of those other things that happens with a group this size of seriously accomplished folks. The truth, however, is more shocking: there isn’t a territorial attitude in the bunch. Kevin and I were immediately welcomed and assigned tasks that obviously others had done before we came along.
The best thing of all: no one “owns” anything. If you need to jump tasks to make sure something happens, do it. If you need to ask for help you get it. And best of all, if you can’t do something, someone else will. When something goes wrong or contrary to plan, there is always someone there to fill in the gap and address the problem. Since everyone is empowered, this committee is the epitome of the “high functioning team”: it is truly Agile.
In short, this committee ROCKS, and the result is an event that consistently raises the bar. I just wanted to share some of that and say “Thanks” for letting me be a part.
Community Megaphone Podcast
I’ve had the opportunity to be involved with the Community Megaphone Podcast on more than one occasion since its inception: I was the 2nd guest and the 1st guest co-host. I also got to sit in on the Speaker Horror Stories panel in CodeStock earlier this year. I have an absolute blast every time I get to participate on this show.
One unfortunate thing about this weekend is that Philly Code Camp (another favorite) was scheduled the same day. One of the show hosts, Dane Morgridge, lives in Philly and so he went there instead of coming to Richmond. With Andrew Duthie, aka The Devhammer, going to Richmond, they decided to do live recordings at both events, another first for the podcast. Of course, this meant Andrew was on his own, so I was really pleased to sit in again as guest co-host.
Frank LaVigne
We did two sessions with Frank La Vigne, one of the original founders of Richmond Code Camp. During one of those sessions I came dangerously close to ensuring I’ll never be invited back: it turns out some people are a little sensitive when you make jokes about VB (who knew!?) The good natured ribbing aside, we had a great time talking with Frank about the early days of RCC, his community work since moving to Northern Virginia, Windows Phone 7, and even his infant son Jake who made an appearance at the event and was the hit of the Speaker’s Dinner.
Jim Pendarvis
We had a great chat with Jim Pendarvis, founder and organizer extraordinaire of Southern Maryland Give Camp. The 2nd installment of SOMDGC is scheduled for March 25-27th and they have a new goal this year of 150 developers and 25 non-profits. Jim talked about the National Day of Give Camp coming up on Martin Luther King day, saying the reason for moving SOMDGC back is the unpredictability of the weather. I just think he doesn’t want to shave his head in a snow storm!
And speaking of last year’s show, this year we have TWO new items of interest. First, if they meet their goal of 150 developers, not only will Andrew Duthie get a mohawk, he will get it dyed in a color of our choosing! There is also talk of a henna tatoo…
Secondly, we have a challenge going now between Jim, Kevin Griffin, and myself. To paraphrase the immortal words of Tommy Callihan from the movie Tommy Boy, the three of us have “what the doctors refer to as a little bit of a weight problem.” So here is the deal: whichever one of us who loses the largest percentage of his body weight by Give Camp will be determined the victor. The two losers will be required to dress up on the last day of Give Camp as a character from Harry Potter. The characters will be chosen by the attendees, and I’m sure hilarity will ensue.
“Van” Van Lowe
“Van” Van Lowe is a community organizer, blogger, and speaker from Northern Virginia. He spoke with us about making the transi
tion from attendee, to speaker, to organizer within the community. We also talked about attending Code Camps versus the larger national conferences like PDC and MIX. And in a CMP Exclusive, he revealed his real name and told us the story of how he started going by “Van” as a result of his speaking activities. Sorry – I’m not going to tell you the story here, you’ll just have to listen to the podcast yourself!
And don’t forget the others…
There were some other interviews as well that I wasn’t in on: Kevin Griffin took the co-host seat for a session and interviewed Rob Zelt, President of INETA. And then later, new Azure MVP David Makogon filled in while I went to give my presentation and they interviewed up and coming community speaker Stuart Leitch from Charlottesville. Stuart is a friend of mine and I can’t wait hear that session for myself!
A New Presentation
I did have the chance to unveil a new presentation during the last session of the day: Expression Blend and the Visual State Manager: A Deep Dive. I’m never sure how a session is really going to go over until I give it for the first time. Recognizing that Blend is still unfortunately a niche topic, I’m also never sure how well attended a session will be, so I was pleasantly surprised to find a packed room: there were even a couple of people standing in the back for a little while!
This one was a lot of fun: I wanted to show some Blend coolness that I don’t typically get to cover in the introductory sessions. We spent some time going over Templating basics and then used the VSM to solve some problems with Templates. The second half of the presentation was all about creating Custom Visual States and how to use them in creative ways. We covered Transitions, Effects, and Multiple Custom States, and it was very well received. My thanks to everyone who sat in and for all the nice comments afterwards.
A Brief Respite
Without question we can add Richmond Code Camp X to the history books as an extremely successful event. We’ll take a very brief hiatus, but before long we’ll start the process all over again for Richmond Code Camp XI. You can go ahead and put it in your calendar now for May 21st, 2011, I hope to see you there!
Developing For .NET 