SSIS - Lookups on a column of nulls

Posted on 2007-07-24
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
    LVL 1

    Expert Comment

    I found this useful for lookup misses.
    LVL 8

    Author Comment

    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.
    LVL 8

    Accepted Solution

    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.
    LVL 1

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now