Execute SQL Task (Log File Load Time and Record Count) by using Stored Procedure with Input and Output Parameters in SSIS Package - SSIS Tutorial

Scenario:

Let's consider this requirement for an SSIS Package  " We want to load multiple files to SQL server Table but we want to log the time when the process started and then update the End Time column in log table so we can measure how much time each file took to load"

  • How to Create an SSIS Package from Scratch
  • How to Load Multiple Files to SQL Server Table in SSIS Package
  • How to extract file name into variable in SSIS Package
  • How to Create Stored procedure with output parameter 
  • How to get Last identity value from stored Procedure
  • How to Insert File Load StartDateTime By using Execute SQL Task
  • How to update File Load  EndDatetime after file is loaded to SQL Server Table.


Script for the Demo to use Execute SQL Task with Stored Procedure Input and Output Parameter
--Create table to Log Load Time,FileName and PackageName
Create table dbo.FileLoadInformation (
PkgId int identity(1,1),
PackageName VARCHAR(100),
FileName VARCHAR(100),
RecordCnt Int,
StartDateTime DateTime,
EndDateTime DateTime)


Truncate table dbo.FileLoadInformation
Select * From dbo.FileLoadInformation

Truncate table  dbo.customer
Select * from  dbo.Customer
-----------------------------------


--Create Procedure To Insert Information Before Data Flow Task Start
Create procedure dbo.InsertLogInfo
@PackageName VARCHAR(100),
@fileName VARCHAR(100),
@PkgID int output
AS
BEGIN
insert into dbo.FileLoadInformation (PackageName,FileName,StartDateTime)
values ( @PackageName,@fileName,getdate())

Set @PkgID= SCOPE_IDENTITY()
return
END


--Test if Stored Procedure is working fine
Declare @id int
EXEC dbo.InsertLogInfo 'PackageName','TestFile',@id output
Print @id

--Script to use in Execute SQL task To Insert A Record
--and Return Identity Value to Output Parameter
EXEC dbo.InsertLogInfo ?,?,? output


--Create Procedure to Update the Same Row that we inserted before
--Data Flow Task Started to Update EndDatetime and RecordCount
Create procedure dbo.UpdateLogInfo
@pkgid int,
@RecordCnt int
AS
BEGIN
update dbo.FileLoadInformation
set EndDateTime=getdate()
,RecordCnt=@RecordCnt
where PkgId=@pkgid
END

--Script used in Execute SQL Task to Run the SP
EXEC dbo.UpdateLogInfo ?,?



How to use Stored Procedure with Input/Output Parameters in Execute SQL Task in SSIS Package



 Related Posts / Videos on Execute SQL Task