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
Related Posts / Videos on Execute SQL Task
- Execute SQL Task Demo for Max Size of SQL Statement in Execute SQL Task Query Editor and Solution
- Execute SQL Task Demo - How to Execute SQL Queries from a SQL Server Table in SSIS Package ( SQLStatementType= Variable)
- Execute SQL Task Demo - How to Execute SQL Queries from Excel file in SSIS Package ( Record Set Destination, Object Type Variable and Foreach Loop Container Demo as well)
- Execute SQL Task Demo - How to use Single Row Result Set in Execute SQL Task and Control the Flow Tasks by using that value ( Precedence Constraint Demo as well).
- Execute SQL Task Demo - How to use Insert Query in Execute SQL Task and Map the variables to Parameters ( Insert File Name and Record Count after Loading the File).
- Execute SQL Task Demo - How to use Stored Procedure with Input/Output Parameters in Execute SQL Task in SSIS Package( Load File Name, Package Name , Record Count and File Load Time in SQL Server Table).
- Execute SQL Task Demo - How to build Query by using Variables in Execute SQL Task ( File Name validation and move files to Archive folder and Bad File Folder if does not validation correctly)
- Execute SQL Task Demo - How to use Full Result Set in Execute SQL Task ( Get Folder Path and File Names from SQL Table and Load those files)
- Execute SQL Task - How to Load Files from Specific Folder Paths Saved in a SQL Table By using SSIS Package
- Execute SQL Task ( use Variable value in Expressions Demo) - How to Reject Already Loaded Files and Load Only New Files in SSIS Package
- Execute SQL Task ( Handle Single Row Result Set Blank Error) -Single Row result set is specified, but no rows were returned in Execute SQL Task in SSIS Package
- Execute SQL Task ( Save Result Set to Variable Demo) - How to Return Deleted and Update
- Record Count from Execute SQL Task and Write to Flat File in SSIS Package
- Execute SQL Task ( Build Dynamic Query in Variable and use as Source ) - How to Save Query in Variable and Use in Execute SQL Task
- Execute SQL Task Expression's Demo - Using Variable in IF Clause in Execute SQL Task in SSIS Package
- Execute SQL Task ( Parameter Mapping Demo for Stored Procedures) - How to Run multiple Stored Procedure in Execute SQL Task with Input Parameters
- Read Single Cell Value from an Excel File in SSIS Package