Last week, I had lunch with an old friend who is, by his own definition, technologically ignorant.  While we caught up, he asked me to explain in terms he could understand what I do for a living.  I went through one of my spiels (the one usually reserved for relatives who only know that I work “with computers”), delivering a high-level talk about systems integration and ETL and some of the challenges involved in intersystem communications.  Since most of my time is spent in the healthcare sector, our discussion wrapped back around to healthcare data integration and the initiatives to integrate personal health data electronically across platforms and providers.  My friend, being a non-technologist and an outsider to the healthcare world, asked a revealing question: 

“So, you’re just talking about lining up the different fields to make sure that they match?  If so, what’s the big deal?”

Alas, if only it was that easy.

There is a great deal of noise surrounding electronic health records right now, and for good reason.  Everything is electronic these days: my local grocery outlet can analyze my purchasing history to generate customized coupons at check-out; local law enforcement systems are integrated with auto insurers which allows officers to immediately verify insurance coverage; my local dealership tracks my Explorer’s mileage and, based on my estimated milestones, automatically sends me reminders to change my filters and have my transmission checked.  Common sense would dictate that our healthcare systems would have at least as much capability as the local Ford dealer, but in practice it seems that this isn’t the case.  A visit to the local clinic often requires that I provide the same information several times at various points of care, and this drill must be repeated at each encounter.  My family doctor is unaware of any information gathered or treatment performed by my podiatrist, and vice versa.  Further, and most irritating, is that a single doctor visit or trip to the hospital will generate a handful of different bills from numerous entities, which often cross over and bill several times for the same specific procedure.

While it may sound like there’s a real dysfunction with healthcare information systems (HIS), the truth is that the situation is not as bad as it appears.  I’ve dealt with some outstanding HIS products, and, while they all have their quirks and shortcomings, many of them are mature and highly stable.  In my analysis, there are two key issues that cause the most headaches: lack of adoption and system interoperability, each of which bring along their own problems and opportunities.  I’ll discuss each of these in turn in the next 2 posts in this series.

As an aside, it’s clear that the electronic health record challenge is not new.  I recently developed a networking friendship with a retired physician who spent several years as a hospital chief of staff, and he tells me that he was providing counsel to EHR vendors almost 25 years ago.  Even though the issue of electronic health records spans the generations, it’s still a very exciting time to work in this field because of the challenges and opportunities to be a part of some creative solutions.

In my next post on this topic, I’ll discuss the issue of technology adoption, and talk about when an electronic record really isn’t.

So you’ve got some packages that regularly extract data to one or more text files, but you know that from time to time some of the queries will not return any data.  However, you find in SSIS that, in a flat file export package, the output file is created regardless of whether any rows are written to the file, and in cases where there are no rows returned from the source, you’ll end up with empty data files in your output directory.  Although not a critical failure, having empty output files can be a nuisance, and unfortunately, there aren’t any native settings in the Flat File Destination or the Flat File Connection Manager objects that will clean up empty files.  Fortunately, SSIS does provide a combination of other tools that you can use to emulate this behavior.

In our example, I’m going to create a package to extract data from a table using a query for which I know that no rows would be returned.  When I connect this data source to a flat file destination and execute the package, I’ll see an empty data file in my export directory.  Next, to demonstrate the intended behavior, I’ll add a Row Count transformation to store the number of affected rows in a variable, and create a File System Task object to delete the output file.  Finally, I’ll use an expression constraint to only delete the file if the variable attached to the row count is zero.

First, let’s create a test table to query:

USE testdb
GO

CREATE TABLE invoices (
  invoiceid     INT    IDENTITY ( 1 , 1 ),
  vendorid      INT,
  invoicedate   DATETIME,
  invoiceamount DECIMAL(10,2))

INSERT invoices
VALUES(12, '8/3/2009', 4125.50),
      (53, '8/13/2009', 1095.25),
      (46, '8/15/2009', 729.50),
      (33, '8/23/2009', 3421.50)

Now, I’ll create a basic package that will export to text the invoices for the past 30 days, a reasonable business requirement.  Since we don’t currently have any invoices matching that criteria, we’ll end up with an empty output file. The original package is shown below:

Now for the new-and-improved version, I’m going to drop a Row Count transformation into the data flow to save the number of affected rows to a variable:

Finally, I’ll create a File System Task and configure it to delete the output file.  To insure that a valid data file is not deleted, I’ll create a precedence constraint using an expression to only execute the delete if the row count variable is equal to 0:

When you execute this package, you’ll see that the File System Task object is executed because there are no rows matching our query.  You can test the package by inserting another row into the database that will be returned by the query, and you’ll see that the data file is exported but not deleted.

I’ve attached the before and after SSIS packages if you’d like to take it for a test drive.  Enjoy!

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.

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!

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/.

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.

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 wrote last month about purchasing a new Toshiba netbook to supplement my mobile computing arsenal.  Forty days later, I’m still quite happy with the purchase, and have gotten as much out of this unit as I had hoped.

My biggest surprise was battery life.  It was rated at 9 hours of runtime, which I assumed was a theoretical spec and not accurate in everyday use.  I haven’t run it straight through from a full charge to full discharge, but my testing indicates that my battery life is at least 9 hours of runtime.

The performance is more than adequate, and apart from some occasional heavy disk I/O, I haven’t run into any problems.  I had installed the SQL Server management tools on it immediately, but didn’t install the SQL Server engine until earlier today.  I ran the install, which took about 45 minutes, and even with SQL Server and Integration Services running, I’ve found no performance problems so far.

I have changed my mobile habits since buying this device.  I used to carry my big laptop only where I thought I might need it, and left it at home for casual trips.  These days I’ve almost always got the netbook in tow; it’s easy to carry and handy to have for when I find myself with some unexpected downtime while out and about.  Even a trip to the doctor or a haircut offers 20 or 30 minutes of waiting, and I can now use my netbook to turn the downtime into productive time.

If you’re a mobile professional, I highly recommend that you consider purchasing a netbook.  At this rate, mine will have literally paid for itself in billable time by the end of the year, not to mention the immeasurable convenience it offers.

Baton Rouge SQL Server group leader and recent SQL Saturday host Patrick LeBlanc is putting together a new learning series.  Starting in September, the SQL Lunch series will commence, providing brief (30 minutes or so) online presentations on various SQL Server-related topics. 

I am currently scheduled to present at the SQL Lunch on October 12th.  I’ll be discussing ways to provide end-user reporting capability using SQL Server Report Builder 2.0 and SQL Server Reporting Services 2008. 

More details, including the full schedule and connection information, will be forthcoming shortly.

I ran into a situation this week that brought to light a subtle syntactical error I’d made in creating an SSIS package.  I’ve got a client that has given me access to their development server to create some complex extraction queries, which will eventually be rolled into SSIS packages.  Since I’m working with read-only access and cannot create stored procedures during the development phase, I’m running these queries in an ad-hoc manner.

So, the queries are built and returning a reasonable set of data.  I copy the entire text of the queries into a series of OleDB Data Sources in SSIS, and run my newly created package.  The execution takes only seconds, which, considering the volume of data I’m moving, tells me something has gone wrong.  The package had completed successfully, but the destination files were all empty.  I tested the queries in SSMS again and confirmed the results, but the same query returns no results in SSIS.

The cause of this was a simple but subtle oversight.  When I copied the query text into the command window in the OleDB Data Source, I had inadvertently also copied the USE [DATABASE_NAME] declaration included in each query.  The inclusion of the USE [DATABASE_NAME] statement caused each data source to fire without error, but returned no rows from the source.

It is important to note that this *should* be a rare problem, since stored procedures are preferable to maintaining complex queries outside the database.  If you have the appropriate permissions and organizational authority to wrap your logic into SPROCs, by all means do so.

So the takeaway is that if you find yourself copying an SQL statement directly into the query window of a data source, make sure you remove any USE [DATABASE_NAME] directives.  Failing to do so can create a bug in your package that is easily overlooked.

[Edited to add SPROC disclaimer 8/23]

The North Texas SQL Server User Group (NTSSUG) monthly meeting will be held this Thursday, August 20th, at the Microsoft campus in Irving.  The speaker for the evening will be my friend Brian Knight, a well-known author, co-founder of SQLServerCentral.com, and principal of Pragmatic Works.  He’ll be presenting “Introduction to SQL Server Analysis Services”.  If you’re able to make it on Thursday night, you won’t be disappointed – Brian is an excellent speaker and a heckuva nice guy.  As always, admission is free, and dinner (usually pizza) and soft drinks will be provided. 

FYI, I’ll be a little late to the meeting due to a family commitment, but I will be there.  See you on Thursday!

For SSIS developers, the need for proper documentation is crucial.  However, the built-in object for documentation, the annotation, is difficult to use.  It doesn’t wrap text, doesn’t support varying font styles in a single instance, and doesn’t offer spell checking.  Further, all annotations are “at large” and are not attached to a particular object – they are associated with a specific task or component only by the location in which you place them.

If you’re like me and would like to see improvements to the SSIS annotation tool, consider visiting Microsoft Connect and offering your vote and feedback on a couple of items:

Connect Item 483132 – Suggestion to improve the SSIS annotation tool by adding rich-text capability.  I added this item this morning.

Connect Item 216927 – Suggestion to allow linking of annotation to a specific object (task, component, etc.).  This one has been out there for a while but only has a few votes.

I received my evaluation summary from the SQL Saturday event in Baton Rouge earlier this month.  This was the first event in which I did more than just one session (and back-to-back sessions at that), and I’d just gotten over the flu as well, so I was a little nervous about how I’d present, but all told it worked out well.  I’d like to see the “Average” column empty with respect to session content, but was glad to see the majority of instructor ratings in the “Excellent” column.  I was fortunate to have Steve Jones in my scripting session, and he’s planning to send me a few notes on things I can do to improve.

Session Title: SSIS: Beyond the Basics

Summary Comments

Session Title: SSIS Scripting

Summary Comments