Use Common Table Expressions (CTE) with Parameters in OLE DB Command Transformation in SSIS Package - SQL Server Integration Services(SSIS) Tutorial

In this video we will learn how to use Common Table Expressions in OLE DB Command Transformation with Parameters in Data Flow task inside SSIS Package.

We will learn following items in this video.

1. How to create a Table and insert some duplicate records
2. How to write Common Table Expression to find duplicate records
3. How to use CTE to delete duplicate records 
4. How to create an SSIS Package from basic
5. How to read data from Flat file to delete matching records in SQL Server Table and leave one 
6. How to use CTE in OLE DB Command Transformation to Delete Duplicate records
7. How to Map Common Table Expression(CTE) parameters to input columns for Delete Duplicate Record.

Script to Delete Duplicate duplicate records by using Common Table expressions in OLE DB Command Transformation in SSIS Package.

USE [Test] 

go 

--drop table [dbo].[Customer] 
CREATE TABLE [dbo].[customer] 
  ( 
     [customerid]    [INT] NULL, 
     [customername]  [VARCHAR](100) NULL, 
     [streetaddress] [VARCHAR](100) NULL, 
     [city]          [VARCHAR](100) NULL, 
     [state]         [CHAR](2) NULL, 
     [createdon]     [DATETIME] NOT NULL, 
     [regioncode]    [VARCHAR](100) NULL 
  ) 

INSERT INTO dbo.customer 
SELECT * 
FROM   (SELECT N'1'                       AS [CustomerId], 
               N'Aamir'                   AS [CustomerName], 
               N'MyStreet'                AS [StreetAddress], 
               N'Charlotte'               AS [City], 
               N'NC'                      AS [State], 
               N'2015-04-30 13:38:08.413' AS [CreatedOn], 
               N'Asia'                    AS [RegionCode] 
        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'2015-04-25 13:21:03.197' AS [CreatedOn], 
               N'Asia'                    AS [RegionCode] 
        UNION ALL 
        SELECT N'1'                       AS [CustomerId], 
               N'Aamir'                   AS [CustomerName], 
               N'MyStreet'                AS [StreetAddress], 
               N'Charlotte'               AS [City], 
               N'NC'                      AS [State], 
               N'2015-04-24 13:38:39.497' AS [CreatedOn], 
               N'Asia'                    AS [RegionCode] 
        UNION ALL 
        SELECT N'1'                       AS [CustomerId], 
               N'Aamir'                   AS [CustomerName], 
               N'MyStreet'                AS [StreetAddress], 
               N'Charlotte'               AS [City], 
               N'NC'                      AS [State], 
               N'2015-04-23 13:38:39.497' AS [CreatedOn], 
               N'Asia'                    AS [RegionCode] 
        UNION ALL 
        SELECT N'1'                       AS [CustomerId], 
               N'Aamir'                   AS [CustomerName], 
               N'MyStreet'                AS [StreetAddress], 
               N'Charlotte'               AS [City], 
               N'NC'                      AS [State], 
               N'2015-04-30 13:40:18.063' AS [CreatedOn], 
               N'Europe'                  AS [RegionCode] 
        UNION ALL 
        SELECT N'1'                       AS [CustomerId], 
               N'Aamir'                   AS [CustomerName], 
               N'MyStreet'                AS [StreetAddress], 
               N'Charlotte'               AS [City], 
               N'NC'                      AS [State], 
               N'2015-04-28 13:40:18.063' AS [CreatedOn], 
               N'Europe'                  AS [RegionCode] 
        UNION ALL 
        SELECT N'1'                       AS [CustomerId], 
               N'Aamir'                   AS [CustomerName], 
               N'MyStreet'                AS [StreetAddress], 
               N'Charlotte'               AS [City], 
               N'NC'                      AS [State], 
               N'2015-04-29 13:40:18.063' AS [CreatedOn], 
               N'Europe'                  AS [RegionCode]) t; 



SELECT * 
FROM   dbo.customer 


--Write Command Table Expression(CTE) to Find Duplicate 
; 
WITH cte_Customer 
     AS (SELECT customerid, 
                customername, 
                streetaddress, 
                city, 
                state, 
                createdon, 
                regioncode, 
                Row_number() 
                  OVER ( 
                    partition BY customerid, customername 
                    ORDER BY createdon) AS Rn 
         FROM   dbo.customer 
         WHERE  regioncode = 'Asia'
   ) 
SELECT * 
FROM   cte_Customer 
WHERE  customerid = 1 
       AND rn > 1 



-- Delete Duplicate Records by using Common Table Expression in OLE DB Command Transformation 
; 
WITH cte 
     AS (SELECT customerid, 
                customername, 
                streetaddress, 
                city, 
                state, 
                createdon, 
                regioncode, 
                Row_number() 
                  OVER ( 
                    partition BY customerid, customername 
                    ORDER BY createdon ) AS Rn 
         FROM   dbo.customer 
         WHERE  regioncode = ?) 
DELETE FROM cte 
WHERE  customerid = ?
and CustomerName=?
       AND rn > 1 

--USE all the parameters inside the First Query
; 
WITH cte 
     AS (SELECT customerid, 
                customername, 
                streetaddress, 
                city, 
                state, 
                createdon, 
                regioncode, 
                Row_number() 
                  OVER ( 
                    partition BY customerid, customername 
                    ORDER BY createdon ) AS Rn 
         FROM   dbo.customer 
         WHERE  regioncode = ? 
                AND customerid = ? 
                AND customername = ?) 
DELETE FROM cte 
WHERE  rn > 1 

Use Command Table Expressions in OLE DB Command Transformation with Parameters in SSIS Package

  Related Posts / Videos on OLE DB Command Transformation 

• Introduction to OLE DB Command Transformation( Perform Update/Delete)
• How to use Stored Procedure Output Parameter in OLE DB Command Transformation in SSIS Package?
• OLE DB Command Transformation - Insert Operation Demo
• OLE DB Command Transformation - Delete Operation Demo
• OLE DB Command Transformation - Update Operation Demo
• OLE DB Command Transformation - Call Stored Procedure with Input Parameters Demo
• OLE DB Command Transformation - Use Sub Query with Parameters in OLE DB Command Transformation to update records in a Table
• OLE DB Command Transformation ( How to Call Multiple Stored Procedure with input parameters in OLE DB Command Transformation in SSIS Package)
• OLE DB Command Transformation ( How to Run Multiple Statements Update/Insert/Delete with parameters in OLE DB Command Transformation in SSIS Package)
• OLE DB Command Transformation ( How to build Dynamic SQLCommand for OLE DB Command Transformation in SSIS Package)