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