Scenario:
We are getting the source records from a flat file and we need to lookup against SQL Server Table. We know the by default Lookup behave as case sensitive when we match the records. Can we make the Lookup to work as case insensitive when match the source records to destination. Will it be good practice, What other options we have to perform case insensitive match?In this video we will learn
- How to read data from Flat File Source
- How to perform lookup with Full Cache and see limitations
- How to perform Lookup with No Cache and Partial Cache and see if it works as case insensitive
- Find as solution to perform Case insensitive match with Full Cache mode
Script used in this video demo for Lookup Transformation in SSIS Package
--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
Source Sample File used in the demo
CustomerName,CustomerRegion,SaleAmt
Aamir,AS,1000
Raza,AS,500
Understand Case Sensitivity of Lookup Transformation in SSIS Package - 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
- 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 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)
superb
ReplyDeletehttps://www.buyprimeaccount.com/
ReplyDeletehttps://www.buyprimeaccount.com/
https://www.buyprimeaccount.com/
https://www.buyprimeaccount.com/
When you log in to the webpage, you may appearance greater than 2 hundred alternatives to select from. Also, as there may be terrific customer support to be had 24/7.
This is really helpful post, very informative there is no doubt about it. I found this one pretty fascinating.
ReplyDelete메이저사이트
온라인경마
You always comes with great stuff for your user. Thanks for being user friendly and Keep posting such great stuff
ReplyDeletemajortotositecom
racesitepro
oncasinositenet
totopickpro