stored procedure loggin

Deleted
Not applicable

SSIS package calls stored proc (exec abc(with parameters) )

 

PROCEDURE [dbo].[ETL_FactSalesProfitability](
@inPackageLogID INT =1
,@UpdateArchive VARCHAR(MAX) = "False"
,@inBypassLog INT = 1
,@Partial INT =0
)
AS
BEGIN TRY
Declare @DimExtractLogID int , @ExtractCount int

 

EXEC dbo.DWTable1;
EXEC dbo.DWTable2;

EXEC dbo.DWTable3;

..

..

...

dbo.DWTable15;

RETURN;
END CATCH

 

this is my code. I want to create a table which will log 

1. start and end time of stored proc DWTable1 to dwtable15 

 

now the main sp is being called by SSIS package which in turn called 15 stored proc. 

I want it in such a way that it logs the 15 detailed (child) stored proc entries so i know how much time is it taking for each stored procedure to execute

 

Kindly let me know. I donot have profiler. I would prefer to create a table. 

please let me know the steps of how to create and add the table 

and edit the main stored procedure

 

Thanks in advance 

Ronak 

 

 

 

0 Replies