[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SSIS - Lookups on a column of nulls

Posted on 2007-07-24
Medium Priority
Last Modified: 2013-11-30
I'm doing a lookup in SSIS. My original source was an excel spreadsheet. The column I'm looking up contains all null values. This is an optional field in our database and may or may not have values in it. If it does have a value, I want to perform a lookup operation.

The problem I'm running into is that when I perform this lookup as the lookup yielded no results. This lookup has the default cache settings. I've read on technet that if the operation is pre-cached that it handles nulls. I sure doesn't seem like it to me.

I could just ignore error within that operation and the job completes as normal, but I do want it to fail if the column actually has a value that doesn't have a match in the lookup table.

Does anybody know how I can handle this null column? There will be a whole bunch of these columns in this package.
Question by:i2mental
  • 2
  • 2

Expert Comment

ID: 19562054
I found this useful for lookup misses.

Author Comment

ID: 19564943
I read that article as well. It, however, does not address my issue. That article talks about replacing nulls in columns where there shouldn't be any. I want my columns to remain null unless there is a proper value.

Accepted Solution

i2mental earned 0 total points
ID: 19594886
I ended up doing a conditional split to remove the null values, process the non-nulls in a seperate thread and then union them together afterwards.

Expert Comment

ID: 19597189
Great. This solution looks cool.
I also load a file which has some Null columns. Conditional split removes these rows, and allows me to process the valid rows. SSIS is quite powerful. You can do almost everything that you wish to do with this cool tool.

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question