Modify the log information of the stored procedure insert custom ssis SQL Server

The log information of the package does not contain the logs when the package is successfully executed. Sometimes we only care about whether the package is successfully executed or fails.

Although the SQL Server SSIS log provider does not record successful packet execution information by default, you can modify the stored procedure it calls to record a successful message.

1. Right-click the blank space in the control flow of the package and choose j logging.

2. Add an SQL Server SSIS log Provider Program and configure the connection string in the Wizard.

3. Select the Onerror event in the details.

When the package is run for the first time, it is installed in the database to create a table and the stored procedure name is: sysdtslog90, sp_dts_addlogentry.

Next, we can modify the stored procedure to implement custom logging!

The following describes a stored procedure for successfully added information for your reference:

Alter procedure [dbo]. [sp_dts_addlogentry]
@ Event sysname, @ computer nvarchar (128), @ operator nvarchar (128 ),
@ Source nvarchar (1024), @ sourceid uniqueidentifier,
@ Executionid uniqueidentifier, @ starttime datetime,
@ Endtime datetime, @ datacode int, @ databytes image,
@ Message nvarchar (2048)

Insert into sysdtslog90
(Event, computer, operator,
Source, sourceid, executionid,
Starttime, endtime, datacode,
Databytes, message) VALUES
(@ Event, @ computer, @ operator,
@ Source, @ sourceid, @ executionid,
@ Starttime, @ endtime, @ datacode,
@ Databytes, @ message)
-- When the package execution ends, search. If the OnError event does not exist, it indicates that the package has been successfully executed.
If @ event = 'packageend'
Begin
If not exists (select * from sysdtslog90 where executionid = @ executionid and [event] = 'onerror ')
Begin
Insert into sysdtslog90
(Event, computer, operator,
Source, sourceid, executionid,
Starttime, endtime, datacode,
Databytes, message) VALUES
('Successed ', @ computer, @ operator,
@ Source, @ sourceid, @ executionid,
@ Starttime, @ endtime, @ datacode,
@ Databytes, 'the package is processed successfully! ')

End
End