Execute Multiple Statements Update/Delete/Insert 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 Multiple Statement in OLE DB Command Transformation with parameters.

We will learn

• How to read the data from Flat file source in SSIS Package
• How to Write Insert statement with Parameters for OLE DB Command Transformation
• How to Write Update Statement with Parameter/s for OLE DB Command Transformation
• How to Write Delete Statement with Parameter/s for OLE DB Command Transformation
• How to Execute Multiple Statements in OLE DB Command Transformation in SSIS Package
• How to Map Multiple Statements Parameters to Input Columns in SSIS Package

Script for video demo: How to Call Multiple Statements in OLE DB Command Transformation in SSIS Package

USE [Test]
GO

--Create Sample Tables for SSIS Package for OLE DB Command Transformation Demo
CREATE TABLE [dbo].[Customer_AS](
 [CustomerId] [int] NULL,
 [CustomerName] [varchar](100) NULL,
 [StreetAddress] [varchar](100) NULL,
 [City] [varchar](100) NULL,
 [State] [char](2) NULL
)

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



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


--Select Statement To check the data in tables
Select * From dbo.Customer_AS
Select * from dbo.Customer_AS_Hist
Select * from dbo.Customer_NA



--Insert/ Update/Delete Statement those we used in OLE DB Command Transformation
Insert into dbo.Customer_AS
Values (?,?,?,?,?)


--Update Statement for OLE DB Command Transformation
Update dbo.Customer_AS_Hist
set CustomerName=?
,StreetAddress=?
,City=?
,State=?
where CustomerId=?

--Delete Statement
Delete from dbo.Customer_NA
where CustomerId=?

Execute Multiple Statements Update/Delete/Insert 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 Sub Query with Parameters in OLE DB Command Transformation to update records in a Table
• 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 build Dynamic SQLCommand for OLE DB Command Transformation in SSIS Package)