How to Handle "Row yielded no match during lookup". Error in SSIS Package - SQL Server Integration Services(SSIS) Tutorial

Scenario: 

We load data from Source File to SQL Server Table on  daily basis. Before we load the records to SQL Server Table we lookup our records with reference table by using Lookup Transformation in SSIS Package, Our SSIS Package often fail with "Row yielded no match during lookup". How can we fix this error.

In this video we will learn following items

1. How to create an SSIS Package from basics
2. How to read data from Flat File in SSIS Package
3. How to use Lookup Transformation with Default Settings
4. How to produce error "Row yielded no match during lookup" in SSIS Package
5. Discuss Possible solutions for "Row yielded no match during lookup" error in SSIS Package

Script used in the video for demo of Lookup Transformation 

--drop table [dbo].[Customer_Dst]
CREATE TABLE [dbo].[Customer_Dst](
    [CustomerId] [int] NULL,
    [SalesAmt] int
   ) 


--drop table [dbo].[Lkp_Customer]
CREATE TABLE [dbo].[Lkp_Customer](
    [CustomerId] [int] NULL,
    [CustomerName] [varchar](100) NULL,
    [StreetAddress] [varchar](100) NULL,
    [City] [varchar](100) NULL,
    [State] [char](2) NULL,
    [Region] [varchar](2) NOT NULL
) 

insert into  [dbo].[Lkp_Customer]

SELECT *
FROM (
    SELECT N'1' AS [CustomerId]
        ,N'Aamir' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'Sahiwal' AS [City]
        ,'PJ' AS [State]
        ,N'AS' AS [Region]
    
    UNION ALL
    
    SELECT N'2' AS [CustomerId]
        ,N'M Raza' AS [CustomerName]
        ,N'Test Street Address' AS [StreetAddress]
        ,N'Lahore' AS [City]
        ,N'PJ' AS [State]
        ,N'AS' AS [Region]
    
    UNION ALL
    
    SELECT N'3' AS [CustomerId]
        ,N'Robert' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'FrankFurt' AS [City]
        ,'NA' AS [State]
        ,N'EU' AS [Region]
    
    UNION ALL
    
    SELECT N'4' AS [CustomerId]
        ,N'John' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'Paris' AS [City]
        ,N'NA' AS [State] 
        ,N'EU' AS [Region]
    
    UNION ALL
    
    SELECT N'5' AS [CustomerId]
        ,N'Sam' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'Charlotte' AS [City]
        ,N'NC' AS [State]
        ,N'NA' AS [Region]
    ) t;


    Select Customerid,CustomerName,Region from dbo.Lkp_Customer
    
    select * from dbo.Customer_Dst

Test

Source File used in the Demo
CustomerName,CustomerRegion,SaleAmt
Aamir,AS,1000
Raza,AS,500

How to Handle "Row yielded no match during lookup". Error in SSIS Package

  Related Posts / Videos on Lookup Transformation 

• How To Use Dynamic Query in Lookup Transformation in SSIS Package
• Use Stored Procedure with Parameters in Lookup Transformation ( Full Cache Mode) in SSIS Package 
• How to Force Lookup Transformation to Work as Case In-Sensitive in SSIS Package
• Does Lookup Transformation perform Left Outer Join or Not in SSIS Package
• How Null values will be matched in Lookup Transformation in Diff Modes(Full Cache,Partial or No Cache)
• How Lookup Transformation works for Duplicate Records in Reference Data Set
• How to Load Slowly Changing Dimension Type 1 by using Lookup Transformation ( UpSert Operation)
• Detailed Demo on Lookup Transformation Modes ( Full Cache, Partial, No Cache) by using SQL Server Profiler
• Diff between Inner join ( Merge Join Transformation) and Lookup Transformation ( Matched Output)