Checklist Overview before Installing SQL Server in Cluster Mode - SQL Server DBA Tutorial

This video illustrates an overview of a checklist before installing SQL Server in cluster mode. Following points are explained in this checklist.
1- Cluster configuration overview of SQL Server
2- Importance of Nodes being online before installing SQL Server in cluster mode
3- Each instance requires a separate static IP address while installing SQL Server in clustering mode
4- Overview of Shared disks and best practice of shared disk in SQL Server
5- How MSDTC plays a role in SQL Server and why is it optional in SQL Server 2008 and above




Checklist Overview before Installing SQL Server in Cluster Mode - SQL Server DBA Tutorial
3 comments:

How to Configure Static IP Address of SQL Server Instance in Cluster - SQL Server DBA Tutorial

In this video you will learn how to configure static IP address during installation of SQL Server in clustering mode, video provides step by step an overview of existing SQL Server static IP configuration. It also demonstrate pre-requisites as well as number of static IP addresses per SQL Server instance.



How to Configure Static IP Address of SQL Server Instance in Cluster - SQL Server DBA Tutorial 
2 comments:

How to Set Preferred Node Order for SQL Server Failover - SQL Server DBA Tutorial

In this video you will learn following:
1- What is preferred node order in SQL Server cluster?
2- How to configure preferred node setting in SQL Server cluster?
3- How to change Failover settings (manual or Automatic)?
4- How does preferred node settings work in SQL Server cluster?




How to Set Preferred Node Order for SQL Server Failover - SQL Server DBA Tutorial
1 comment:

How to Failover Main Node or Active Node Resources to Another Node or Passive Node - DBA Tutorial

In this video you will learn following:
1- An overview of existing cluster configuration
2- How to look at he current node of different resources in SQL Server cluster
3- How to failover resources from one node to another
4- How to observe the sequence of failover of different resources



How to Failover Main Node or Active Node Resources to Another Node or Passive Node -  DBA Tutorial
No comments:

Manage SQL Server 2012 Failover Cluster Overview - SQL Server DBA Tutorial

In this video you will learn following:
1- Failover cluster settings overview using Failover over cluster manager?
2- How to manage windows 2012 Failover cluster?
3- How to find which SQL Server instance is running on what node?
4- How to find which node the other rsources are running such as Shared disks, networks and MSDTC.
5- How to look at critical errors in Failover cluster Manager?
6- Other important settings overview in Windows 2012 Cluster Manager





Manage SQL Server 2012 Failover Cluster Overview - SQL Server DBA Tutorial
3 comments:

How to Verify or validate Cluster configuration - SQL Server DBA Tutorial

In this video you will learn following:
1- How to validate or verfiy cluster configuration?
2- How to run all tests during cluster validation?
3- How to run specific Tests during cluster validation?
4- When it is important to run cluster validation such as
     a) When you add or delete resources from SQL Server cluster
     b) When you add a new node to the cluster
     c) If you change Network settings of an existing cluster





How to Verify or validate Cluster configuration - SQL Server DBA Tutorial
1 comment:

How to Install Service Packs, Hot Fixes and Windows Patches in Cluster - SQL Server DBA Tutorial

In this video you will learn following:
1- How to install Service Packs, hot fixes and/or windows Patches in SQL Server cluster?
2- Determining which node you decide to apply patches or install hotfixes on?
3- Move resources from one node to another node in SQL Server cluster
4- Best practice to get a node ready for installing patches or hotfixes
5- Failing resources back to patched node after patch or hotfix installation
6- How to validate cluster functionality after installing patches or hotfixes
7- Brief overview of Cluster Aware Updates (CAU) in windows 2012 R2 configuration



How to Install Service Packs,Hot Fixes and Windows Patches in Cluster - SQL Server DBA Tutorial
3 comments:

How to Add a New Node to an Existing Cluster - SQL Server DBA Tutorial

In this video you will learn following:
1- How to prepare a node to be added in an existing cluster
2- What windows features are mendatory to install on new node in SQL Server clustering?
3- How to add a new node to an existing SQL Server cluster using Windows Failover manager?
4- How to run cluster validation on new node?




How to add a New Node to Existing Cluster - SQL Server DBA Tutorial
1 comment:

How to Evict a Node from Cluster - SQL Server DBA Tutorial

In this video you will learn following:
1- Consideration before you Evict a node from an existing cluster
2- How to Evict a node from an existing cluster using Windows Failover Cluster manager?
3- How to run cluster validation after node eviction from an existing cluster?



How to Evit a Node from Cluster - SQL Server DBA Tutorial
5 comments:

How to Load Lookup Data Parallel in SSIS Package ( Cache Transformation) - SQL Server Integration Services (SSIS) Tutorial

In this video we will learn how Lookup Transformations work in sequence to load the data into memory by performing a demo.

In second part of demo , we will learn how to use the Cache Transformations to load Lookup Data to Memory parallel and then use those Cache Transformation connection managers in the Lookup Transformations.

In scenarios where we have to use multiple lookup Transformations in Data Flow Task and those look up Transformation can take long time to load data on pre execution as they load data into memory one after one.

By using Cache Transformations, we can read data from multiple sources in parallel and then load into Memory so we can make use of Cache Transformations in other Data Flow Tasks and avoid sequential loading of Lookup data into memory.


Script used in " Load Lookup Data Parallel in SSIS Package"

--Create table [dbo].[dbo.Src_Customer]
CREATE TABLE [dbo].[dbo.Src_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].[dbo.Src_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;

CREATE TABLE dbo.LkpCustomer (
    CustomerId INT
    ,CustomerName VARCHAR(100)
    )
GO

INSERT INTO dbo.LkpCustomer
VALUES (
    1
    ,'Aamir'
    )
    ,(
    2
    ,'M Raza'
    )
    ,(
    3
    ,'Robert'
    )

CREATE TABLE dbo.LkpSale (
    CustomerName VARCHAR(100)
    ,SaleAmt INT
    )

INSERT INTO dbo.LkpSale
VALUES (
    'Aamir'
    ,100
    )
    ,(
    'M Raza'
    ,200
    )
    ,(
    'Robert'
    ,300
    )


3 comments:

How to Use Excel Data in Lookup Transformation in SSIS Package - SQL Server Integration (SSIS) Tutorial

Scenario:

We get our reference data or lookup data an Excel File. Business users can make any changes to the data. They can add new records or delete or update them in Excel. Every time we need to load the data from Source Flat File, we want to perform a lookup against Excel file and load only the records which have the matching records in Excel File.
How can be use Excel File in Lookup Transformation?


What we will learn in this video
How to read data from an Excel File and Load into Memory by using Cache Transformation
How to read data from Flat File Source in SSIS Package
How to use Derived Column or Data Conversion Transformation to perform Data Conversion if Required in SSIS Package
How to use Cache Transformation Connection Manager in Lookup Transformation in SSIS Package
How to get only Matching Records from Lookup output in SSIS Package



2 comments:

Cache Transformation In Depth Demo by using CAW File in SSIS Package - SQL Server Integration Services (SSIS) Tutorial

In this video we will learn how to save the data from Flat file source to Cache File( CAW) in one SSIS Package and then use in different Data Flow Tasks in same SSIS Package or use in different SSIS Packages.




Cache Transformation File Cache( CAW) feature - SQL Server Integration Services(SSIS) Tutorial



  Related Posts / Videos on Cache Transformation 

1 comment:

Use Flat File Data in Lookup Transformation in SSIS Package ( Cache Transformation) - SQL Server Integration Services (SSIS) Tutorial

Scenario:

Let's say we get our reference data in a flat file and before we load data from source to destination we have to match with our reference data. If data matches we will load otherwise ignore the non matching source data.

What we will learn in this video

  1. How to read data from Flat File Source and Load into Memory by using Cache Transformation
  2. How to  use Cache Transformation Connection Manager in Lookup Transformation
  3. How to Read Source Data from SQL Table and Compare by using lookup Transformation and load only matching records to destination in SSIS Package
  4. How to use OLE DB Destination in SSIS Package


2 comments:

Diff between Lookup Transforamtion and Merge Transformation (Inner Join Type) in SSIS Package - SQL Server Integration Services( SSIS) Tutorial

Scenario:

In this video we will perform a demo or using Merge Join with Inner Join option and Lookup and see how the both produce different results for same input rows.


What you will learn in this video

  1. How to write TSQL Statement for Inner Join
  2. How to use Merge Join Transformation in SSIS Package with Inner Join type
  3. How to use Lookup Transformation in SSIS Package with Matched Output 
  4. Find out how Inner join returns duplicate records for duplicate records



Script used in the Demo of : Difference between Merge Join Transformation ( INNER JOIN Type) and Lookup Transformation


Select * into #Temp
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]
    )t

--Insert into dbo.SourceTable and dbo.LookupTable
    Select * into dbo.lookupTable from #Temp
    Select customerid,CustomerName into dbo.SourceTable from #Temp



--Insert duplicate record
insert into dbo.lookupTable    
    SELECT N'5' AS [CustomerId]
        ,N'Aamir' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'Charlotte' AS [City]
        ,N'NC' AS [State]
        ,N'NA' AS [Region]



What is the difference between Inner Join in Merge Join Transformation and Lookup Transformation in SSIS Package



  Related Posts / Videos on Lookup Transformation 

1 comment:

Understand Lookup Transformation Modes ( Full Cache, Partial Cache and No Cache) Demo - SQL Server Integration Services(SSIS) Tools

In this video we will do perform some experiments to understand following

What is Full Cache Mode in Lookup Transformation and How it works in SSIS Package
What is Partial Cache Mode in Lookup Transformation and How it works in SSIS Package
What is No Cache Mode in Lookup Transformation and How it works in SSIS Package


we will use SQL Server Profiler to capture live SQL Statements to while changing the mode from Full Cache to Partial or from Partial Cache to No Cache.


Sample Data used in the Test File

CustomerId,CustomerName,StreetAddress,City,State,RegionCode
1,Aamir,1234 Street,Sahiwal,NY,NA
1,Aamir,Test Address,Charlotte,NC,NA
1,Aamir,1234 Street,Sahiwal,NY,NA
2,M Raza,Test Address,Charlotte,NC,NA
2,M Raza,Test Address,Charlotte,NC,NA
2,M Raza,Test Address,Charlotte,NC,NA
3,Aamir,1234 Street,Sahiwal,NY,NA
1,Aamir,1234 Street,Sahiwal,NY,NA

Lookup Table

SELECT *
INTO dbo.Customer2
FROM (
    SELECT N'3' AS [CustomerId]
        ,N'Aamir' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'Sahiwal' AS [City]
        ,NULL AS [State]
        ,N'EU' AS [Region]
    
    UNION ALL
    
    SELECT N'2' AS [CustomerId]
        ,N'M Raza' AS [CustomerName]
        ,N'Test Street Address' AS [StreetAddress]
        ,N'Charlotte' AS [City]
        ,N'NC' AS [State]
        ,N'AS' AS [Region]
    
    UNION ALL
    
    SELECT N'4' AS [CustomerId]
        ,N'Aamir' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'Sahiwal' AS [City]
        ,NULL AS [State]
        ,N'EU' AS [Region]
    
    UNION ALL
    
    SELECT N'1' AS [CustomerId]
        ,N'Aamir' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'Sahiwal' AS [City]
        ,NULL AS [State]
        ,N'AS' AS [Region]
    ) t;


In depth Demo What are Lookup Transformation Modes ( Full Cache, Partial Cache and No Cache) in SSIS Package - SSIS Tutorial




  Related Posts / Videos on Lookup Transformation 





5 comments:

Perform Upsert ( Update/Insert SCD1 ) by using Lookup Transformation - SQL Server Integration Services(SSIS) Tutorial

Scenario:

Let's say we have to load a dimension table from text file. Our business Key is SSN. We need to insert new records depending upon values of SSN column, If any new then we need to insert this records. If SSN already existing in Table then we need to find out if any other column is changed from Source columns values. If that is true then we have to update those values.

What we will learn in this video

  1. How to Read the data from Flat file in SSIS Package
  2. How to perform Lookup to Find out Existing or Non Existing Records in Destination Table From Source
  3. How to Insert new Records by using OLE DB Destination
  4. How to update existing Records by using OLE DB Command Transformation in SSIS Package



Solution:

Le't create a table that need Update/Insert operation

CREATE TABLE dbo.DimCustomer
  (
     CustomerID INT IDENTITY(1, 1),
     SSN        VARCHAR(11),
     FirstName  VARCHAR(50),
     LastName   VARCHAR(50),
     Address    VARCHAR(100),
     LoadDate   DATETIME,
     UpdateDate DATETIME,
     LoadedBy   VARCHAR(50),
     ModifiedBy VARCHAR(50)
  )



Sample Data we will use in Source File

SSN,FirstName,LastName,Address

000-000-001,Aamir,Shahzad,NJ USA

000-000-002,John,River,NC USA





Query used in the OLE DB Command Transformation




Update dbo.DimCustomer
SET FirstName=?,
LastName=?,
Address=?,
UpdateDate=?,
ModifiedBy=?
WHERE SSN=?














How to load Slowly Changing Dimension Type 1 by using Lookup Transformation in SSIS Package - SSIS Tutorial









  Related Posts / Videos on Lookup Transformation 




















2 comments:
  






































Duplicate Data in Lookup Transformation and Possible Outcomes in SSIS Package - SQL Server Integration Services(SSIS) Tutorial












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




    

  1. How to create data from flat file in SSIS Package
    2. 

  2. 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.
    3. 

  3. Observe the output to understand which reference record will be used if we will have multiple records in reference data set.
    4. 






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 




















1 comment:
  





























          

        

          

        

          

        










How Null values will be matched in Lookup Transformation in Diff Modes(Full Cache,Partial or No Cache) - SQL Server Integration Services(SSIS) Tutorial














Scenario:

In this video we will learn how Lookup Transformation deal with Null values those we get from source and try to match with Reference data set in lookup Transformation.

We will experiment with three modes of lookup transformation and see the output.



What we will learn in this video




    

  1. How to read data from flat file source with Null values
    2. 

  2. How to read data in Lookup Transformation as Reference Data Set from a Table
    3. 

  3. How to Map the columns in Lookup Transformation and see the matching output
    4. 

  4. How to change the modes of Lookup Transformation from Full Cache, Partial Cache and No Cache Modes and observe the output.
    5. 






Source File used in this demo

CustomerName,CustomerRegion,SaleAmt

Aamir,AS,1000

M Raza,AS,500

,EU,400









SQL Script for Lkp_Customer Table


--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'0' AS [CustomerId]
        ,NULL AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'Charlotte' AS [City]
        ,N'NC' AS [State]
        ,N'XX' AS [Region]
        
    ) t; 


















How Null values are handles in Lookup Transformation in SSIS Package with Default Settings - SSIS Tutorial









  Related Posts / Videos on Lookup Transformation 




















1 comment:
  






































Does Lookup Transformation perform Left Outer Join or Not in SSIS Package - SQL Server Integration Services( SSIS) Tutorial














Scenario:

We often hear from developers that we can perform Left Outer Join by using Lookup Transformation instead of using Merge Join.

In this video we will learn if that is possible or not?



Items we will learn in this video

How to Create Two tables with some Sample Data to perform Left Join in TSQL

How to use two tables to perform Left Join by using Merge Join Transformation

How to use Lookup Ignore Failure feature to ignore errors if source record does not match with lookup reference data.

Analyize the output of TSQL Code output, Merge Join output and Lookup Output to see if all three produce same results?



Sample Tables with Sample Data




--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;








--Create New Sample Tables




Select * into dbo.Customer1 from dbo.lkp_customer
Select top 4 * into dbo.Customer2 from dbo.lkp_customer

Select * from dbo.Customer1
Select * from dbo.Customer2

--Write Left Join TSQL Query
Select L.CustomerId,L.CustomerName,L.Region AS LeftRegion,R.Region AS RightRegion
from dbo.Customer1 AS L
Left outer join 
dbo.Customer2 AS R
on L.Customerid=R.Customerid






















Can Lookup Transformation perform Left Outer Join operation in SSIS Package - SSIS Tutorial















  Related Posts / Videos on Lookup Transformation 


















3 comments:
  






































How to Force Lookup Transformation to Work as Case In-Sensitive in SSIS Package - SQL Server Integration Services(SSIS) Tutorial














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




    

  1. How to read data from Flat File Source
    2. 

  2. How to perform lookup with Full Cache and see limitations
    3. 

  3. How to perform Lookup with No Cache and Partial Cache and see if it works as case insensitive
    4. 

  4. Find as solution to perform Case insensitive match with Full Cache mode
    5. 








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 


























4 comments:
  





























        

      









Subscribe to:
Posts (Atom)