How to use SubQuery with Parameters in OLE DB Command Transformation in SSIS Package - SQL Server Integration Services ( SSIS) Tutorial

OLE DB Command Transformation can be used for multiple purposes inside Data Flow Task in SSIS Package. If we need to Update/Delete/Insert or event Run a Stored Procedure inside the Data Flow Task, we can use OLE DB Command Transformation. Today we are going to learn how to use Sub Query in OLE DB Command Transformation with parameters inside Data Flow Task in SSIS Package.

In this video we going to learn following items

How to create SSIS Package from basics

  1. How to read data from Flat File Source by using Flat File Source
  2. How to Write Update Statement with SubQuery in TSQL
  3. How to use SQL Statement with Sub query in OLE DB Command Transformation
  4. How to map the Parameters involving Sub Query Parameters to Input Columns
  5. How to test if Update query with sub query part is working fine in OLE DB Command Transformation in SSIS Package

Script used How to use Sub Query in OLE DB Command Transformation and Map the Parameters.


USE [Test]
GO

CREATE TABLE [dbo].[Customer_AS](
    [CustomerId] [int] NULL,
    [CustomerName] [varchar](100) NULL,
    [StreetAddress] [varchar](100) NULL,
    [City] [varchar](100) NULL,
    [State] [char](2) NULL
) 
go
CREATE TABLE [dbo].[Customer_NA](
    [CustomerId] [int] NULL,
    [CustomerName] [varchar](100) NULL,
    [StreetAddress] [varchar](100) NULL,
    [City] [varchar](100) NULL,
    [State] [char](2) NULL
) 


insert into [dbo].[Customer_AS]
SELECT * 
FROM   (SELECT N'1'                   AS [CustomerId], 
               N'Aamir shahzad'       AS [CustomerName], 
               N'Test Street Address' AS [StreetAddress], 
               N'Charlotte'           AS [City], 
               N'NC'                  AS [State] 
        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] 
        UNION ALL 
        SELECT N'1'                   AS [CustomerId], 
               N'Aamir shahzad'       AS [CustomerName], 
               N'Test Street Address' AS [StreetAddress], 
               N'Charlotte'           AS [City], 
               N'NC'                  AS [State]) t; 


insert into [dbo].[Customer_NA]
SELECT * 
FROM   (SELECT N'1'                   AS [CustomerId], 
               N'Aamir shahzad'       AS [CustomerName], 
               N'Test Street Address' AS [StreetAddress], 
               N'Charlotte'           AS [City], 
               N'NC'                  AS [State] 
        UNION ALL 
        SELECT N'3'                   AS [CustomerId], 
               N'John Smith'          AS [CustomerName], 
               N'Test Street Address' AS [StreetAddress], 
               N'New York City'       AS [City], 
               N'NY'                  AS [State]) t;
               
               
               
Select * From [dbo].[Customer_AS]
Select * from [dbo].[Customer_NA]   


Update CSA
set CustomerName=?
From Dbo.Customer_AS CSA
WHERE Customerid=?
AND Exists ( Select 1 from dbo.Customer_NA NA
WHERE NA.StreetAddress=?
AND      NA.City=?
AND State=?)



File I used as Source for OLE DB Sub Query Demo
 CustomerId,CustomerName,StreetAddress,City,State

1,Aamir shahzad Modified,Test Street Address,Charlotte,NC



Use SubQuery with Parameters in OLE DB Command Transformation in SSIS Package



  Related Posts / Videos on OLE DB Command Transformation