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 

• 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 Common Table Expressions to Delete Duplicate Records in OLE DB Command Transformation with Input Parameters)
• 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)