Conditional Split fails if value is NULL in SSIS

ssis
InTheWorldOfCodingApplications picture InTheWorldOfCodingApplications · May 21, 2013 · Viewed 49.8k times · Source

I am passing result of FULL Outer join to Conditional Split and Filtering Records on the basis of following rules . Basically both tables has same schema and Primarykey values are same.

a. If Primary key of Source is NULL
b. If Primary Key of Destination is NULL
c. If Source and Destination key matches.

It works fine for (a) and (b) but fails for (c)

Source.Id == Destination.Id

and throws exception that condition evaluated as NULL where Boolean was expected. How i can make this work?

Conditional Split gets input from Merge Join and it's a FULL OUTER JOIN as i need FULL OUTER join results here

Answer

Milen Kindekov picture Milen Kindekov · May 21, 2013

Your third condition should start with a ISNULL check again before you compare your values. Like the following:

!ISNULL(Source.Id) && !ISNULL(Destination.Id) && Source.Id == Destination.Id

You need to handle every column that can be NULL in your condition. Since you are comparing Id's, another option would be:

(ISNULL(Source.Id) ? 0 : Source.Id) == (ISNULL(Destination.Id) ? 0 : Destination.Id)

If comparing strings, you can replace the zeroes with blank spaces.

Related questions

How to pass variable as a parameter in Execute SQL Task SSIS?

I have ssis package in that I'm taking values from flat file and insert it into table. I have taken one Execute SQL Task in that creating one temptable CREATE TABLE [tempdb].dbo.##temptable ( date datetime, companyname nvarchar(50), price decimal(10,0), …

Read More
SSIS Convert Between Unicode and Non-Unicode Error

I have an ssis package where I am using an OLEDB source linking to SQL Server 2005 table. All columns except a date column are NVARCHAR(255). I am using an Excel destination and using a SQL statement to create the sheet …

Read More
What is the SSIS package and what does it do?

Can someone tell me what the SSIS package is all about? I am totally new to data-warehousing and I am going crazy with the terms SSIS, DTL, ETL and so on. I am looking for a big picture and how …

Read More