I had a situation where I was using the Lookup transformation and then loading this into the SSIS Cache, but I wanted all rows to be inserted using the SSIS Cache, even if there was a NULL Value. Below explains how I overcame this.

 

Example:

·         In our Source data we have Products, but they might not be in our Product Dimension table, due to the source systems not having the required data.

o    NOTE: While this is not ideal and we either should fail the component or redirect it, for this example this is fine.

·         We have created a row in our Products Table for Products that we do not find.

o    EG:

§  ProductsSK ‘-1’

§  ProductsName ‘Product not Found’

·         So when we insert data we want to ensure that even when products are not found, they will still exist in the Lookup Transformation and cache.

 

1.       I created a Lookup Transformation in SSIS which I then configured as the following explained below.

2.       On the General Tab I configured it with the following as shown below:

a.        clip_image002

b.       NOTE: The reason that I selected the Redirect rows to no match output in the Specify how to handle rows with no matching entries, is because due to the configuration of our Lookup transformation all the rows WILL find a match.

3.       Next click on the Connection on the left hand side.

a.        clip_image004

b.       Now you will select your OLE DB connection to your database where your Product Dimension is located.

c.        Then select Use results of an SQL Query.

d.       Then what we did was to put in the following SQL Query which we will explain why we did this after the query

  SELECT[ProductsSK],[ProductsSK]asProductsSKToUse      

  FROM[dbo].[Mart_TD_Products]with (nolock)

 

  UnionAll

 

  Selectnullas[ProductsSK],–1 asProductsSKToUse

                                                               i.      Now as you can see above we have create a duplicate column names from our Dimension Table.

                                                              ii.      The reason for this is in the second part of the query with the Union All

                                                            iii.      Now you can see that we have actually put in a NULL value, and then given this NULL value a value of -1

1.       NOTE: This corresponds to the data that we already have in our Products Dimension table.

                                                            iv.      So now when you load your query into the SSIS Cache it will also have a value of the following:

1.       clip_image006

e.       Now when the lookup is running in SSIS, when it finds a NULL value it will assign it a value of ‘-1’ to the ProductsSKToUse

4.       Then click on Columns and create the lookup

                                                               i.      clip_image008

                                                              ii.      As you can see above we dragged the ProductsSK to our ProductsSK in our Available Lookup Columns

                                                            iii.      And then below you can see that our Output Alias is ProductsSKToUse

5.       This will then be used in our insert into our Fact Table.

6.       And in this way when there is a NULL Value it will still be matched.

a.        So as you can see for our 2 rows, we had 2 matches even when 1 row was NULL

b.       clip_image010