Call Multiple Stored Procedure in OLE DB Command Transformation with input Parameters in SSIS Package - SQL Server Integration Services (SSIS) Tutorial

In this video we will learn how to call Multiple Stored Procedures in OLE DB Command Transformation. First Stored Procedure will be used to Insert the values in a SQL Server Table and Second Stored Procedure will be used to Delete the record from History Table.


What you will learn in this video

1. How to read data from flat file in SSIS Package 
2. How to Create Stored Procedure with input Parameters for Insert Operation
3. How to Create Stored Procedure with Input Parameter for Delete Operation
4. How to Call the Multiple Stored Procedures in OLE DB Command Transformation
5. How to Map the input Columns to Stored Procedures Input Parameters in OLE DB Command Transformation
6. Learn if it is possible to map single input column to Multiple Parameters in OLE DB Command Transformation
7. Learn about the error if mapping is missing for a parameter in OLE DB Command Transformation

Scripts used in the video for demo: How to execute Multiple Stored Procedures in OLE DB Command Transformation in SSIS Package with Input Parameters

--Create Samples tables for Stored Procedure used in OLE DB Command Transformation
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
)

Select * From dbo.Customer_AS
Select * From dbo.Customer_AS_Hist

--Delete from dbo.Customer_AS


--Create Stored Procedure for Insert Operation that we will call in OLE DB CommandTransformation

Create procedure dbo.usp_Insert_Customer_AS
@CustomerId int,
@CustomerName Varchar(100),
@StreetAddress Varchar(100),
@City Varchar(100),
@State Char(2)
AS
BEGIN
insert into dbo.Customer_AS
values( @CustomerId,@CustomerName,@StreetAddress,@City,@State)
END

--EXEC dbo.usp_Insert_Customer_AS 1,'Aamir','test address','Test City','NC'



--Create Stored Procedure for Delete Operation that we will call in OLE DB Command Transformation
ALTER procedure dbo.usp_Delete_Customer_AS_Hist
@CustomerId_Hist int
AS
BEGIN
Delete from dbo.Customer_AS_Hist
where customerid=@CustomerId_Hist
END


--The way to call a Stored Procedure with Input Parameters in OLE DB Command Transformation
EXEC dbo.usp_Delete_Customer_AS_Hist ?
EXEC dbo.usp_Insert_Customer_AS ?,?,?,?,?

Flat File data that we used for this SSIS Package

CustomerId,CustomerName,StreetAddress,City,State
1,Aamir SHAHZAD,1090 Street Address,Charlotte,NC
3,John,1091 Street Address,Charlotte,NC

Execute Multiple Stored Procedures in OLE DB Command Transformation with input 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 ( Use Common Table Expressions to Delete Duplicate Records in OLE DB Command Transformation with Input Parameters)
• 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)