In this video we will explore what happen if we have duplicate records in reference data set and we are choosing some values from reference data. Which Record values will be chosen when we will match with Source data and produce matching output.
What we will learn in this video
Source file used for this demo
CustomerName,SaleAmt
Aamir,1000
M Raza,500
Lookup Table used for this demo with duplicate records by CustomerName
What we will learn in this video
- How to create data from flat file in SSIS Package
- How to have duplicate records by Customer Name and different data Points for other columns in SQL Server Table and use that in Lookup Transformation as reference data set.
- Observe the output to understand which reference record will be used if we will have multiple records in reference data set.
Source file used for this demo
CustomerName,SaleAmt
Aamir,1000
M Raza,500
Lookup Table used for this demo with duplicate records by CustomerName
--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] UNION ALL SELECT N'6' AS [CustomerId] ,'Aamir' AS [CustomerName] ,N'1234 Street' AS [StreetAddress] ,N'Charlotte' AS [City] ,N'NC' AS [State] ,N'XX' AS [Region] ) t; Select * From dbo.Lkp_Customer
Duplicate Records in Lookup Data Set in SSIS Package and Possible Output - SSIS Tutorial
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 to Handle "Row yielded no match during lookup". Error in SSIS Package
- 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)
I feel SSIS is the best platform which can ultimately help in solving the most complex database problems.
ReplyDeleteSSIS Upsert