April 20, 2011 at 11:46 pm
[font="Tahoma"]
Hello Friends,
I was going through several blogs and found a way to log the package run details to a user defined table. Following is the code for the same.
CREATE TABLE AuditPackage (Id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
PackageName VARCHAR(100) NOT NULL,
PackageGuid UNIQUEIDENTIFIER,
ExecutionGuid UNIQUEIDENTIFIER,
StartTime DATETIME,
EndTime DATETIME,
ElapsedTime INT,
Status VARCHAR(100));
GO
CREATE PROCEDURE dbo.sp_dts_addlogentry @event sysname,
@computer-2 nvarchar(128),
@operator nvarchar(128),
@source nvarchar(1024),
@sourceid uniqueidentifier,
@executionid uniqueidentifier,
@starttime datetime,
@endtime datetime,
@datacode int,
@databytes image,
@message nvarchar(2048)
AS
BEGIN
INSERT INTO sysdtslog90 (event, computer, operator, source, sourceid, executionid, starttime, endtime, datacode, databytes, message)
VALUES (@event, @computer-2, @operator, @source, @sourceid, @executionid, @starttime, @endtime, @datacode, @databytes, @message);
INSERT INTO AuditPackage (PackageName, PackageGuid, ExecutionGuid, StartTime, ElapsedTime)
SELECT @source, @sourceid, @executionid, GETDATE(), 0
WHERE (@event = ‘PackageStart’);
UPDATE AuditPackage
SET EndTime = GETDATE(),
ElapsedTime = DATEDIFF(ms, StartTime, GETDATE()),
Status = ‘Complete’
WHERE (@event = ‘PackageEnd’
AND PackageGuid = @sourceid
AND ExecutionGuid = @executionid);
UPDATE AuditPackage
SET Status = ‘Error’
WHERE (@event = ‘OnError’
AND PackageGuid = @sourceid
AND ExecutionGuid = @executionid);
END
However, I am unable to locate the sp mentioned above in SQL Server 2008. I also believe that the values mentioned as parameters can be taken from sysssislog table in SQL Server 2008. However, I am not really sure how and from where to call this SP inorder to insert the values in the user defined table.
Please help me out on this.
[/font]
April 21, 2011 at 4:08 am
rmkmurali (4/20/2011)
[font="Tahoma"]Hello Friends,
I was going through several blogs and found a way to log the package run details to a user defined table. Following is the code for the same.
CREATE TABLE AuditPackage (Id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
PackageName VARCHAR(100) NOT NULL,
PackageGuid UNIQUEIDENTIFIER,
ExecutionGuid UNIQUEIDENTIFIER,
StartTime DATETIME,
EndTime DATETIME,
ElapsedTime INT,
Status VARCHAR(100));
GO
CREATE PROCEDURE dbo.sp_dts_addlogentry @event sysname,
@computer-2 nvarchar(128),
@operator nvarchar(128),
@source nvarchar(1024),
@sourceid uniqueidentifier,
@executionid uniqueidentifier,
@starttime datetime,
@endtime datetime,
@datacode int,
@databytes image,
@message nvarchar(2048)
AS
BEGIN
INSERT INTO sysdtslog90 (event, computer, operator, source, sourceid, executionid, starttime, endtime, datacode, databytes, message)
VALUES (@event, @computer-2, @operator, @source, @sourceid, @executionid, @starttime, @endtime, @datacode, @databytes, @message);
INSERT INTO AuditPackage (PackageName, PackageGuid, ExecutionGuid, StartTime, ElapsedTime)
SELECT @source, @sourceid, @executionid, GETDATE(), 0
WHERE (@event = ‘PackageStart’);
UPDATE AuditPackage
SET EndTime = GETDATE(),
ElapsedTime = DATEDIFF(ms, StartTime, GETDATE()),
Status = ‘Complete’
WHERE (@event = ‘PackageEnd’
AND PackageGuid = @sourceid
AND ExecutionGuid = @executionid);
UPDATE AuditPackage
SET Status = ‘Error’
WHERE (@event = ‘OnError’
AND PackageGuid = @sourceid
AND ExecutionGuid = @executionid);
END
However, I am unable to locate the sp mentioned above in SQL Server 2008. I also believe that the values mentioned as parameters can be taken from sysssislog table in SQL Server 2008. However, I am not really sure how and from where to call this SP inorder to insert the values in the user defined table.
Please help me out on this.
[/font]
Hi
Did you check under msdb/programability/Stored Procedures/System Stored Procedures ?
The SP is called sp_ssis_addlogentry... note that the dts was replaced with ssis. I think that the dts is from an SQL2005 enviroment.
This SP is called automatically when/if SSIS is logging into an SQL table, i.e. the 'SSIS Log Provider for SQL Server' has been selected. The triggering is determined by the developer from the 'Details' page.
Regards
Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -
April 21, 2011 at 5:55 am
[font="Tahoma"]
Hello Brian,
Thanks for your reply. That was really helpful. However, i was able to locate the sp in the database which i am configuring the Logging page. I made the changes and its executing perfectly.
Thanks for your help.
[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply