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 

1 comment:

  1. I think this was a solution to a very important and complex problem which many professionals have always struggled with.

    SSIS Upsert

    ReplyDelete