Scenario:Let's consider that we are running multiple Stored Procedure by Execute SQL Task in SSIS Package. We want to know which Stored Procedure is currently running? Also we want to capture the time taken by each of the Stored Procedure to Execute?
Items you will leaven in this video:
- How to Create an SSIS Package from Scratch
- How to Create Stored Procedure in TSQL
- How to Log information in Audit Table Before and After Execution of Stored Procedure.
- How to Call Multiple Stored Procedures in Execute SQL Task in SSIS Package
- How to use Scope_identity() in TSQL
Script to Log Stored Procedure Starttime and End Time from Execute SQL task in SSIS Package
--Create Table to Load Stored Procedure Execution Information create table dbo.LogSPExecution ( id int identity(1,1), SPName VARCHAR(100), StartTime datetime, EndTime datetime) go --Create Couple of Sample Stored Procedures Create procedure dbo.usp_SP1 as BEGIN WAITFor Delay '00:00:10' END Create procedure dbo.usp_SP2 as BEGIN WAITFor Delay '00:00:15' END --Script to use inside Execute SQL Task to Log Information Declare @id int insert into dbo.LogSPExecution( SPName,StartTime) values ('dbo.usp_SP1',getdate()) Select @id=SCOPE_IDENTITY() EXEC dbo.usp_SP1 update dbo.LogSPExecution set EndTime=getdate() where id=@id GO Declare @id int insert into dbo.LogSPExecution( SPName,StartTime) values ('dbo.usp_SP2',getdate()) Select @id=SCOPE_IDENTITY() EXEC dbo.usp_SP2 update dbo.LogSPExecution set EndTime=getdate() where id=@id