How to Execute List of Stored Procedure those names are Saved in a SQL Server Table - SSIS Tutorial

In this video we will learn How to Execute list of Stored Procedures those names are saved in a SQL Server Table.

The video will cover following items

  • How to store Stored Procedure names with Schema in SQL Server Table
  • How to Read the List of Stored Procedures from SQL Server Table by using Execute SQL Task and save them into Object Type Variable
  • How to Loop through List of Stored Procedures By using Foreach Loop Container
  • Save values from Foreach Loop Container to variables
  • Execute Stored Procedure by using Execute SQL Task
  • How to built Dynamic SQL in Execute SQL Task by using Variable values in SSIS Package


Scenario:

Think about a situation where your developers has special requirement and they want to run stored procedure from a table and they have capability to update/insert the table records. Or there is possibility that you want to have the flexibility to run more or less Stored Procedure on daily basis without changing the SQL Server Agent Job or SSIS Package.


Script that can be used for your Test SSIS Package to Run Stored Procedures from SQL Server Table.

--Create a table to store the names of Stored Procedures which need to be 
--Executed by using SSIS Package
use Test
  go
    Create table dbo.SPList(
  DatabaseName VARCHAR(100),
  SchemaName VARCHAR(100),
  SPName VARCHAR(100))


  --Create Sample Tables
  Create Table Test.dbo.Customer_AS (id int, name varchar(100))
  Create table TestDB.dbo_Customer_EU (Id int, Name VARCHAR(100))
--Create couple of Stored Procedure in Different Databases

--Create SPs in different Database with different Schemas
  --drop procedure dbo.LoadCustomerAS
  use Test
  go
  Create procedure dbo.LoadCustomerAS
  AS
  BEGIN
  Insert intodbo.Customer_AS
  values (1,'Test_As')
  END

 --drop procedure Sales.LoadCustomerAS
 use TestDB
  go
  Create procedure Sales.LoadCustomerEU
  AS
  BEGIN
  Insert into dbo.Customer_EU
 values (2,'TEst_EU')
  END

  --Insert the names of Stored Procedures with Database and Schema Name in dbo.SPList Table
  insert into dbo.SPList values ('Test','dbo','LoadCustomerAS')
  go
  insert into dbo.SPList values ('TestDB','Sales','LoadCustomerEU')

  --Query to be used in Execute SQL Task 
  Select DatabaseName,SchemaName,SPName from dbo.SPList
  
  
  --After Executing The SSIS Package validate your records if SPs ran successfully
  Select * from Test.dbo.Customer_AS 
  Select * from TestDB.dbo_Customer_EU



Call Stored Procedures from a Tables in SSIS Package - SSIS Tutorial



 Related Posts / Videos on Execute SQL Task