Find out which Stored Procedure is Currently Executing in Execute SQL Task in SSIS Package - SQL Server Integration Services (SSIS) Tutorial

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



How to get Execution information and Execution Time for Multiple Stored Procedures used in Execute SQL Task in SSIS Package

1 comment:

  1. TODAY I GOT MY DESIRED XMAS LOAN AMOUNT $520,000.00 FROM A RELIABLE AND TRUSTED LOAN COMPANY. IF YOU NEED A LOAN NOW EMAIL CONTACT drbenjaminfinance@gmail.com

    Hello, I'm here to testify of how i got my loan from BENJAMIN LOAN FINANCE(drbenjaminfinance@gmail.com) I don't know if you are in need of an urgent loan to pay bills, start business or build a house, they offer all kinds of loan. So feel free to contact Dr. Benjamin Owen he holds all of the information about how to obtain money quickly and painlessly without cost/stress via Email: drbenjaminfinance@gmail.com

    Consider all your financial problems tackled and solved ASAP. Share this to help a soul right now THANKS.

    ReplyDelete