When NULL equals NULL

When NULL equals NULL

• Email

 

When does Null = Null?

...I love nulls because they are the ultimate unknown quantity. They don't match, they don't exist, they are pure existential beings singularly apart from each other. Each is a self- contained entity, not referencing or referent to anyone, anything or anywhere. They are a lot like cats.  -Howard F. Arner, Jr. 

Not so fast Jr. We’ve all been taught that NULL <> NULL, and that’s true within the confines of SQL Server. However, in the wonderful, wacky world of SSIS NULL does indeed equal NULL.

 

We’ve all run into the situation where we need to match an incoming value to a reference value. For example, our source data may have values which have to be matched against a foreign key table. However, the foreign key column in the destination table is nullable. Thus, the value for the destination column must either be a value from the foreign key table, or null.

Example

This example will use the AdventureWorks database. The goal will be to fill in the Production.Product.ProductSubcategoryID column. This is a nullable column that is foreigned keyed to the Production.ProductSubcategory table.

         Let’s setup the demonstration. The following code will create a table in AdventureWorks. This table will serve as the source. 

 

Database setup

-- ************** Source Query *******************************************
-- Setup and populate a table that will serve as a source for updating the -- productSubcategory in the Production.Product table
IF  EXISTS 	(SELECT * 
FROM 	sys.objects WHERE object_id = OBJECT_ID(N'[Production].[_NullLookupSource]') 
AND type in (N'U'))
DROP TABLE [Production].[_NullLookupSource]
GO

CREATE TABLE [Production].[_NullLookupSource](
[ProductName] [nvarchar](50) NULL,
		[SubCategory] [nvarchar](50) NULL
) ON [PRIMARY]
GO

-- Populate the "Source" table		
INSERT	INTO Production._NullLookupSource 
		(ProductName,
		SubCategory)
		
SELECT	p.Name AS ProductName,
		ps.Name AS SubCategory
		
FROM	Production.Product p
		LEFT JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID 
-- ************** Source Query *******************************************

  

SSIS package setup

Now that the source table has been created, it’s time to create the SSIS package. The package will ultimately look like this:     

The query for the OLE DB source simply selects from the table previously created.

 

SELECT ProductName, SubCategory 
FROM   Production._NullLookupSource

Next, add the lookup transform with the following reference query.

 

SELECT Name, ProductSubcategoryID 
FROM Production.ProductSubcategory

Finally, add the recordset destination and choose all the columns to go into some dummy Object variable. The recordset destination simply serves as a destination. It’s not relevant to the purpose of this demonstration.

 

Try it

Now that the package and source table are in place, run the package. As expected an error occurs.

The dreaded “Row yielded no match during lookup” error occurred. In the data view you can see that the “Rear Derailleur Cage” has a SubCategory of NULL.

The solution is simple. In the reference query of the Lookup transform add a UNION ALL. The new query is:

 

SELECT Name, ProductSubcategoryID 
FROM Production.ProductSubcategory 
UNION ALL 
SELECT NULL, NULL 

 

Now, rerun the package. Notice that the Lookup transform doesn’t have an error. The “Rear Derailleur Cage” still has a SubCategory of  NULL, however the ProductSubCategoryID is now NULL.

 

As you can see the package now succeedes.