It's been well-documented through myriad blogs and forum posts about the case sensitivity of the comparisons in the SSIS lookup transformation (a good review can be found here). In a nutshell, a comparison using the lookup transformation is case sensitive when using the default setting of Full Cache, even if the values in the database are stored in a case insensitive collation. This happens because, in Full Cache mode, the comparison is done by the SSIS engine rather than the database engine, the former of which differentiates between "Value", "VALUE", and "VaLuE" regardless of the collation setting. But did you know that this transformation is space sensitive as well? Consider the following T-SQL code: SELECT Cast('char value' AS CHAR(20)) [val1] INTO test1 SELECT Cast('char value' AS CHAR(40)) [val2] INTO test2 SELECT t1.val1, t2.val2 FROM test1 t1 INNER JOIN test2 t2 ON t1.val1 = t2.val2 As you would expect, executing this code results in a successful match (INNER JOIN), even though we're comparing CHAR values of differing lengths (for more information, see this article for more information about spaces and padding in SQL Server). However, when the same comparison is run through an SSIS lookup transformation in Full Cache mode, the lookup on our sample data will not result in a match. Similar to the case sensitive lookup, you'll find that the SSIS engine would treat the strings 'Hello World' and 'Hello World ' (note the trailing spaces) as dissimilar values. Unlike in SQL Server, trailing whitespace is significant in SSIS value comparisons.
As a workaround, you can use the TRIM() function in SSIS and the RTRIM() T-SQL function to insure that your comparisons are ignorant of trailing whitespace. Alternatively, you could use a cache mode other than Full Cache, but you should be aware of the other implications before making such a change.
Note that this behavior is limited to fixed-length character fields, but could lead to some unexpected and hard-to-detect problems if you aren’t aware of the behavior.
It had been about 5 years since I’d bought a new desktop machine for my home, and with my oldest starting school this year, it was time to add another computer to our inventory. Since my son’s computer needs are relatively minimal at this point, I decided to buy a new computer for the tall people in the house and hand down the current machine to the rugrat. Our home desktop has always been a multipurpose device; it’s got some of my business information on it, most of our personal finance stuff, our entire music and home video/photo library, and several games that I rarely get to play. While I had no intention of spending two grand on this box, I wanted to get something that wouldn’t need an upgrade in the next couple of years.
After a couple of disappointing stops at local electronics retailers, I bought an off-the-shelf Dell from, of all places, Wal-Mart. It’s a 64-bit multicore Athlon with lots of disk space and 8gb of RAM. Even more striking is the 24” widescreen monitor; I’d gotten used to the poor, neglected 17” CRT we’ve relied on for years, so I felt entitled to splurge a bit on the display.
I’m saving the setup and data transfer for tomorrow, after which I’ll get to be the hero for giving my kiddo his very own PC. Everybody wins!
A recent blog by Jack Corbett (No Training Budget Still No Excuse, followed up by SSC’s Steve Jones here) hit the nail on the head with respect to taking charge of one’s own career. I won’t belabor the point by repeating their words here, but the message is that everyone has access to quality SQL Server training even if your training budget is a goose egg.
Jack’s article pointed out a number of organizations and websites that offer free SQL Server educational resources, and I was pleased to find one asset on his list that I had never used. I’ll add on a few Microsoft BI resources that I’ve discovered in my travels:
http://www.learnmicrosoftbi.com – A website with dozens of videos on Microsoft BI, mostly on SQL Server Analysis Services
http://www.sqldts.com/ and http://www.sqlis.com/ – Sister websites with resources for SQL Server DTS and SSIS, respectively
Pragmatic Works webinars – Brian Knight and company have published several of on-demand webinars for business intelligence professionals
SQL Lunch – This event is just emerging, but will start in September and offer semi-weekly lunchtime webcasts on a variety of topics (follow Patrick LeBlanc’s blog for more details on this one).
MSSQLTips.com – A SQL Server resource site, which, among other offerings, sends out a SQL Server Tip Of The Day each weekday via e-mail.
Microsoft Podcasts – Not just for SQL Server, there are Microsoft podcasts and other resources for almost every Microsoft technology. If you spend any time commuting, burn some of these to CD and learn on the road!
The Midnight DBA – According to their own description, husband and wife DBA team Sean and Jen McCown put the kids to bed and stay up late making videos. [OK, they’re SQL Server videos – stop snickering.] There’s a wide variety of content here, and is well worth a visit.
Twitter – OK, this one is reaching just a bit. I add this because there is a fair number of SQL Server pros on Twitter, many of whom are more than willing to offer advice, share upcoming events (webcasts and such) and “tweet” interesting information. SQLServerPedia has a good index page listing many of the SQL folks who use Twitter.
I’m more than 2 months into the netbook experience, and I’m happy to report that it’s still a good investment. Last weekend, I made the leap to Windows 7 RTM. So far so good!
I was more than a little surprised to find out that there was no upgrade path from Windows XP to Windows 7. Although there are a few free utilities that can help ease the pain by migrating user settings and documents, a move from XP to 7 will involve a completely new install. That said, I’m usually not a fan of an OS upgrade anyway, since these tend to be less stable than a clean install in the long run. Fortunately, I’ve not had this machine long, so it didn’t take long to back up all of my data for the reinstall.
The installation of the OS and reinstall of my apps took a good part of the afternoon. Moving to an operating system 8 years newer, I expected a performance drag due to the additional overhead, but was pleasantly surprised at the snappiness of Windows 7 on the netbook. I’ve noticed that I’m under a bit more memory pressure than I was with XP, but I’ve still got the standard 1gb of RAM installed (I can go up to 2gb on this model). The battery life seemed to have diminished a bit, but after reinstalling the Toshiba power management software, it’s still in the neighborhood of 7 to 8 hours.
I’ve noticed that if I have SQL Server, SQL Server Management Studio and Visual Studio running at the same time, the CPU tends to run higher than normal. I have a little more disk paging than before, most likely due to the memory pressure. Overall, though, the jump to Windows 7 has been a positive move for me.
In the next few months, I’ll be giving a couple of talks on SQL Server business intelligence. For October, I have the pleasure of presenting for SQL Lunch, a new online learning series pioneered by Patrick LeBlanc. On October 12th, I’ll be discussing ways to leverage SQL Server Report Builder 2.0 against your existing SSRS infrastructure to allow users to create their own ad-hoc and published reports.
Also, I’ll be visiting the Ft. Worth SQL Server User Group on November 18th. I’ll be discussing Intermediate SSIS, demonstrating some of the more complex tasks and transformations and demonstrating how to leverage these tools to assist with challenging ETL scenarios. Hope to see you there!
Recently, my friend Jack Corbett asked a question on Twitter:
In a nutshell, the SSIS foreach loop will enumerate a given list of items (files in a directory, nodes in an XML file, static list of values, etc.) and will perform some operation for each of the items in the collection. This behavior is similar to foreach loop constructs that are found in most high-level programming languages. These programming elements normally include a continue statement, which allows you to stop processing of the current item and move on to the next one. Jack hoped to find this capability built into the SSIS foreach loop, but unfortunately it doesn’t exist natively.
However, there is a workaround that will allow you to reproduce the continue statement in the SSIS foreach loop. By including a “dummy” data flow within the foreach loop, we can use a precedence constraint to skip some of the items in the list based on the value of a variable. To demonstrate, I’ll use a foreach loop to make a copy of all of the files in a given directory except for one specific file. I'll first set up the foreach loop in the control flow pane to enumerate over the files in a particular directory, by using the Foreach File Enumerator setting:
Now, within the foreach loop above, I create two data flows; one that does nothing at all, and the other that reads the current file in the list and writes the data out to a new file, the name of which is set using an expression.
Next comes the important part: I create a precedence constraint from my dummy data flow to the one that actually performs the work, and I’ll edit the expression to exclude one of the file names:
In this scenario, the package flow will only move from the dummy data flow to the one containing our business logic if the variable value, in this case the current filename, does not match the one we intend to skip.
So the short answer is that the SSIS foreach loop doesn’t have native capability to skip processing for certain values, but using a do-nothing container and a precedence constraint within the loop is a creative and effective substitute.
The monthly North Texas SQL Server User Group (NTSSUG) meeting will be held this Thursday, September 17, at 7:00pm at the Microsoft campus in Irving, Texas. Our guest will be SQL Server MVP and consultant Geoff Hiten, who will be presenting “Bad SQL - Why Does This "Perfectly Good" T-SQL Run So Slow?”
Admission is always free, and pizza and drinks will be provided. Bring a friend!
For more information on this event or the NTSSUG group, visit our website at http://northtexas.sqlpass.org/.
In a couple of previous posts (Part One and Part Two), I shared some thoughts about starting a career path as a database professional, including some notes from my own journey. In this final segment, I’ll share a few discrete tips that will hopefully be useful to those interested in this field.
Build Your Foundation: You’re going to need some base skills outside the immediate scope of SQL Server. Regardless of what role you pursue (DBA, database developer, or BI engineer), you need to have at least a decent understanding of the technologies that support SQL Server. Get to know how Active Directory works, particularly with respect to users, groups, and authentication. Learn about Windows server architecture, including clustering technology. Spend some time learning about disk configuration, RAID levels, and SAN architecture.
Get Training: Be proactive in training yourself. If your employer won’t provide training, don’t use that as an excuse – remember that you are the one with the most skin in the game in your career development. Take advantage of the training resources available to you, many of which are free (here’s a good list to start with). Get a developer copy (nominal cost) or an evaluation version (free) of SQL Server, and start practicing. Get a couple of books and spend the time reading them – in fact, most beginner SQL Server books will have some examples you can work through to get some good hands-on work with the product.
Get Experience: Spend some time getting your hands dirty with real projects. Even though you won’t find a DBA job without having previous experience, there are ways to build tangible, legitimate, documentable experience even if you’re not working full-time as a database professional:
Get Involved in the Community: Much has been written about the benefits of being involved in the SQL Server community, and I won’t try to reproduce it all here. Suffice it to say that active involvement with other similarly-minded professionals provides an avenue for learning and advice, and can expose career opportunities not otherwise available to you. Join PASS and take part in their free webcasts. Find a local SQL Server user group and attend regularly. Join Twitter and follow some SQL Server tweeps. The community is open to newcomers, and most venues welcome even the most basic questions for those trying in earnest to learn about SQL Server.
Pay Your Dues. Know up front that you’re not going to walk straight into a DBA job. With hard work and perseverance, you can eventually work your way into a role where you're handling difficult problems and working on cutting-edge projects. To get there, however, you’re going to have to start small, running database backups, changing tapes, and reviewing log files. Note that paying your dues may include some seemingly unrelated tasks (see Build Your Foundation above).
Above all else, keep your chin up - don’t get discouraged by the “Can’t get a job without experience, and can’t get experience without a job” cycle. Put yourself in an employer’s frame of mind: would you want to hand over the keys to the kingdom to an untested, inexperienced rookie? Know that it won’t happen overnight, and that you’ll most likely take a series of small steps rather than a huge leap into this field. Prove yourself worthy with the small stuff, and the big stuff will come in time.
Good luck to you!
Just a quick update on the SQL Saturday event we are planning for the Dallas area next year. Late last week our local event planning committee met to discuss the big stuff (venue, date, and advertising) and came away with a short but critical action list.
We’ve got a venue that has given soft confirmation to accommodate us, and we’re working to lock that in this week. For the event date, we had to dodge a number of other events in our January/February timeframe; the remaining Saturdays were January 23 and January 30, and we expect to have a decision between the two in the next week or so. We’ve got some folks who are contacting potential advertisers, both international and local, to help fund what we expect to be an event with 500 or more professionals in the database, development, and business fields. [Sidebar: If you are a business principal interested in sponsoring this event, let me know – tdmitch at gmail.]
There are still significant decisions to be made and lots of work to do. Once the date is set, we’ll publish the event to the SQL Saturday website and open it up to registrations and speaker abstracts. Later, the abstracts will need to be reviewed and the schedule created from the submitted sessions. I’ll also be contacting several high-profile speakers to see if they can work this event into their schedule. We need to arrange catering for lunch, along with donuts, snacks, and drinks for the remainder of the day. We will be seeking a host hotel for out-of-town attendees, and the speaker reception and post-event party will need to be set up. That’s just the big stuff – there are flyers to print, conference bags to stuff, tables to set up and tear down, and hundreds of other little things that (hopefully) will be planned well in advance.
Fortunately, we’ve got a good group that has expressed a willingness to get involved. Our combined area user groups (Dallas and Ft. Worth) represent hundreds of technical professionals, and we’ve already had a number of volunteers come forward and offer to help organize the event or present a session. Several of our group, myself included, have been to SQL Saturday events before and have been witness to what works and what could use improvement.
In the meantime, we’re still gathering a list of volunteers. If you’re interested in helping plan or execute this event, please let me know and I’ll add you to our volunteer contact list. We’re also building a list of those interested in presenting, and we’ve already heard from several of you who would like to speak.
I’ll keep the updates coming as things develop.
I see a lot of questions on the forums about updating existing data in SSIS. When the update is dynamic and is based on elements within your data flow, a common mistake is to use the Ole DB Command within the data flow pane:
The above poorly designed update has us retrieving data from some source, and then running an update on our target database based on one or more values in the source. Does it work? Sure. The problem occurs when you touch more than a handful of rows. When you bring in the Ole Db command into the picture in a data flow, you’ll be firing the statement in this control once for every row of data in your pipeline. So let’s say our source retrieves 100,000 rows into the data flow: the downstream UPDATE command will be executed 100,000 times! Such operations could bring the most capable server to its knees.
A better solution could include staging your data. Using this method, you’ll retrieve the data from the source and write it into a staging table in the destination database. You can then use an Execute SQL task to run your update in a more organic manner.
Set up your data flow as shown above to pull in the data to a staging table, then you can run a single UPDATE statement:
UPDATE i SET InvoiceAmount = st.UpdatedInvoiceAmount FROM Invoices i INNER JOIN StagedData st ON i.InvoiceID = st.InvoiceID
The advantage here is that you’re executing the expensive UPDATE statement once for each table rather than once for each row affected.
Of course, there are some situations that explicitly disallow the use of staging tables in destination systems. If storage or access restrictions keep you from using this method, you may have to use the row-by-row insert, so be aware that it's going to be a bottleneck.
Everybody loves a compliment. Not everyone enjoys criticism, even if it’s constructive and politely delivered, but the fact is that it’s difficult to get better at what you do without periodic evaluations of your performances.
It’s only been a few years since I committed to start speaking publicly to share my knowledge and improve my public speaking skills. I’ve learned a lot about creating and delivering presentations, but I’d like to get better, and it’s continual feedback that helps me do that. To assist in this process, I’ve created a profile at SpeakerRate.com, a relatively new website that allows presentation attendees to rate the presenter and leave some brief comments. Once you’ve registered on the site as a presentation attendee, you can rate and comment on sessions which you’ve attended. I created an entry for each of the six talks I’ve done this year, and if you’ve attended any of these, I’d appreciate your feedback and comments.
If you’re a speaker, I recommend registering on the site. Sure, you’ve got to have thick skin to expose yourself to this kind of judgment, but if you’re speaking in public you’re doing that anyway, right? If you do register and you’ve seen me in one of your talks, let me know and I’ll be happy to share my notes.
At times it felt like a party, it had enough content to be a mini-conference, and we learned that some people get a little punchy after 24 straight hours of SQL Server. Regardless if you were a presenter, a casual observer, or stayed engaged for the whole event, the 24 Hours of PASS event for 2009 was a memorable experience.
I was fortunate to have participated in several sessions, but several recent family illnesses and a late project kept me from getting engaged with all 24 sessions as I had initially planned.
I started out by listening to Allen White’s session on PowerShell for SQL Server. Allen’s depth of knowledge and excitement about this topic shone through, and I’ve listed several things that I want to try based on his presentation. Notable takeaway: PowerShell can be used to browse a SQL Server object hierarchy much like a filesystem. Very cool!
Two nontechnical presentations were particularly good. Having moved into a team lead position last year, I found Kevin Kline’s “Team Management Fundamentals” discussion to be particularly valuable. I also listened to Steve Jones’s presentation, “Building a Better Blog”; for anyone working to improve the quality and consistency of their blog, a lesson from a hard-core blogger such as Steve is not to be missed. Notable takeaway: Short blog posts are fine, but blogging consistently (weekly or even monthly) is critical.
I was able to catch one of the two SSIS presentations. John Welch presented “Delivering Good Performance with SSIS”, which had several good tips for those with beginning to intermediate skills in SSIS. Most people working with SSIS focus on the mechanics and accuracy of ETL operations, and don’t spend a lot of time optimizing data flow. Notable takeaway: Be aware of nonblocking, partial blocking, and fully blocking transformations in your packages, as they can significantly impact performance.
Although I missed a good part of it, Adam Machanic’s session entitled “SQLCLR or T-SQL? A Brief Survey of Performance Options” was helpful. He demonstrated some scenarios where T-SQL outperforms SQLCLR, and vice versa. I also missed most of the session “Embed Reporting Services Into Your Applications” delivered by Jessica Moss, but I saw enough to learn that SSRS can be used with Windows applications as well as web apps. I got a refresher course on recovery models in the session “What’s Simple about Simple Recovery Model” from Kalen Delaney, who is a wealth of information on SQL Server internals.
There were a few sessions I missed entirely that I wish I’d been able to dig into. There was a text mining session in the middle of the night (CDT) that looked very interesting. I know very little about SQLdiag, so I wish I’d been able to attend Brad McGehee’s session on that topic. MVPs and fellow SSC’ers Gail Shaw and Grant Fritchey discussed indexing and performance tuning, respectively, and I’m sure they both hit home runs.
There were a couple of things that could have gone better. First, several of the session the links listed on the PASS website were incorrect. This issue may have been minimized by the prolific use of Twitter, since people were able to ask for a valid link when the original one didn’t work. Brent Ozar also posted a correct set of links during the presentation, but many were still using the PASS site (as one would expect). I’m sure everyone had their hands full, but having a person on standby for those logistical issues is important when the link is the very gateway to the event.
Also, Tom LaRock had a feed of his own for the duration of the 24 hours; I started off watching both the session and Tom, but between the commentary and the echo on his audio became too districting, and I turned off his feed after just a couple of sessions. I like the idea behind this – it gives the event a more personal, interactive feel – but I’d suggest reserving more of the commentary for the Q&A time or the gap between sessions. And Tom, I’ll spring for a comfy set of headphones for next year to avoid the cursed echo... :)
All of the sessions were recorded, though that fact wasn’t widely advertised before the event – a wise move, in my opinion, since many would have skipped the live event if they’d known that they could watch the rerun later. Some of the recorded sessions will be available for viewing as early as next week, with all of them ready by late November or December. If you missed any of these sessions (or if – gasp! – you skipped the whole thing), keep an eye out for these recordings on the PASS website. I’ll definitely go through and catch the ones I missed, and will likely replay the ones I attended when I can fully focus and make notes for myself.
I have never heard of another group having this type of unique content delivery. Pushing out sessions for 24 hours straight shows the kind of fresh, community-focused ideas coming out of PASS these days, and will hopefully provide incentive to get engaged for SQL Server professionals who are not currently involved with PASS or a local chapter group. This organization is focused on growth, and it’s a very exciting time to be involved. If you’re not a member, join PASS today!
I’ve been mostly offline for the last ten days or so, cutting back on blogging and forum participation and mostly staying away from the Twitter/LinkedIn/Facebook circles. We’ve had 3 of 4 family members who have fallen ill in the last week (myself included), and we’ve been prepping for the baby with improvements to the house and lots of pink purchases. But I am still around and engaged, doing some real work behind the scenes; as Mark Twain said, “The reports of my death are greatly exaggerated”.
I do have several SQL Server-related projects in the pipeline. I’m working some articles for SQL Server Central, including a series on how to get started with scripting in SSIS (thanks to Steve Jones for the suggestion). I’ve volunteered to be a technical editor for the SQL Server Standard, the recently revived print magazine that is now run by PASS and will be published as an online magazine. I’ve also volunteered to work on a CodePlex project spearheaded by Jorge Segarra that will help to identify Protected Health Information (PHI) assets within healthcare organizations to insure compliance with HIPAA. Also in the mix is an add-on for the PASS website that will allow the aggregation of SQL Server-related blog posts, a project that will hopefully be ready for testing this week.
Looking forward, I've volunteered to be a speaker at the SQL Lunch series, and for my first presentation I will be discussing ways to integrate Report Builder 2.0 into your SSRS environment on October 12. I'm also on the lookout for speaking opportunities for 2010, and am actively working to bring SQL Saturday to Dallas in January or February of next year.
More to come soon!
As you’ve no doubt heard or read, the call for nominations for the PASS Board of Directors is open until September 3. If you’re active in the community, have leadership skills, and are committed to growing the SQL Server community, a position on the board may be right for you. Terms are 2 years in length and do require some travel, mostly to Seattle. It does require commitment, but you’d have the opportunity to shape the future of the PASS organization and the SQL Server communities that are a part of PASS. If you’re up for the challenge, I’d encourage you to submit your name for consideration.
I’ve spent some time talking to Andy Warren, a current board member, and he’s encouraged me to run for the board. I am very excited about the possibility of being a part of the PASS leadership, and do plan to make a run for the board – just not this year. I’ve got a lot of irons in the fire right now, on the home front (a baby due in October and a possible move to a new home) and in my profession (job uncertainly due to my company being bought out). I’m certain that I could make a worthwhile contribution as a PASS board member, but as this is a huge responsibility, I have to insure that other goings-on in my life will not keep me from giving 100% to that effort. I don’t think this is the year for that. (I know, excuses, excuses…)
I’m looking forward to seeing who is selected by the nominating committee. Those who I would like to see have either already self-nominated, have said they aren’t interested this year, or recently went to work for Microsoft (I won’t mention any names on that last one).
Well, perhaps not a whole new identity, just a consolidation of multiple facets of my online persona. For several years now, I’ve maintained a personal website (www.timmitchell.net) and a professional website (www.bucketofbits.com). The personal site, an old Community Server install, consisted of a neglected personal blog and a few family pictures, but sadly had been untouched for nearly a year. On the BucketOfBits.com website, I had published links to my recent blog posts and downloads from my presentations.
So, to simplify administration and to consolidate my online footprint, I’ve decided to merge both sites under the TimMitchell.net domain. This site will be a (mostly) professional site: I’ve imported the professional content and links from BucketOfBits, and I’m cross-posting my SQLServerCentral blog there as well. For the content that is purely personal, including pictures of the kids and some random thoughts that have nothing to do with SQL Server/technology/career, I’ll maintain my “personal” identity on Facebook, which I’ve found is better suited for that purpose.
I reviewed several different platforms, including the Mojo Portal, DotNetNuke, and WordPress for this consolidation project, and decided to roll out BlogEngine.net, a C#/SQL Server blogging platform. It was easy to deploy, is relatively simple to configure or re-engineer, and supports multiple themes for when I get tired of the same old layout. It supports cross posting of blogs, and the setup to cross post my SSC blog over to this new site was very easy. Thanks to fellow Dallas-area SQL guy Lee Everest for giving me some pointers on this platform. The visual layout is as plain-vanilla as it gets, so I may spice it up with a new theme once the dust has settled.
As always, feedback is welcome. Let me know if you find any problems or have suggestions.
