The book was very useful when I first began developing in SSIS 2005/2008 packages. The original code can be is available to downloand from Wrox's website:
Microsoft SQL Server 2008 Integration Services: Problem, Design, Solution.
There were two things missing which my client wanted - record counts and the ability to restrict the level of information recorded. For the logging level, I created 3 levels 0, 1 and 2.
Level 0 - Logging is off
Level 1 - Logging is minimal (excludes variable logging)
Level 2 - Logging is verbose
There are 6 stored procedures for logging events from the SSIS packages:
- LogPackageEnd
- LogPackageError
- LogPackageStart
- LogTaskPostExecute
- LogTaskPreExecute
- LogVariableValueChanged
The @LoggingLevel int parameter was added to each of the procedures to capture the user variable LoggingLevel passed in by the SSIS package. A check was added to each stored procedure to reflect the levels above.
Each of the SQL tasks in the SSIS package was amended to include the User::LoggingLevel in the Parameter Mapping section and the SQL statement amended to include @LoggingLevel = ? statement.
For the record count, the PackageTaskLog table was extended to include RecordsAffected and the LogTaskPostExecute stored procedure extended to include @RecordsAffected int parameter defaulting to 0. Changes were made in the SSIS package to pass this information in.
In Data Flow tasks, I used the Row Count component to record record counts to the User::RecordsAffected variable. For SQL tasks, I returned RecordsAffected whenever a stored procedure was called and saved this information to user variable via the Result Set.
Code is below and a sample package will follow:
/* Extend the PackageTaskLog table */
IF NOT EXISTS (
SELECT * FROM information_schema.COLUMNS
WHERE column_name='RecordsAffected' and table_name='PackageTaskLog' and table_schema='dbo'
)
BEGIN
ALTER TABLE dbo.PackageTaskLog ADD [RecordsAffected] [int] NULL
END
GO
/* Alter the stored procedures */
ALTER PROCEDURE [dbo].[LogVariableValueChanged]
( @PackageLogID int
,@VariableName varchar(255)
,@VariableValue varchar(max)
,@LoggingLevel int
)
AS
BEGIN
SET NOCOUNT ON
/* Check if logging is verbose */
IF @LoggingLevel = 2 and (select 1 from dbo.PackageLog where PackageLogID = @PackageLogID) = 1
BEGIN
INSERT INTO dbo.PackageVariableLog(PackageLogID, VariableName, VariableValue, LogDateTime)
VALUES (@PackageLogID, @VariableName, @VariableValue, getdate())
END
END
GO
ALTER PROCEDURE [dbo].[LogTaskPreExecute]
( @PackageLogID int
,@SourceName varchar(64)
,@SourceID uniqueidentifier
,@PackageID uniqueidentifier
,@LoggingLevel int
)
AS
BEGIN
SET NOCOUNT ON
/* Check if logging is verbose = 1 */
IF @PackageLogID > 0
BEGIN
IF @PackageID <> @SourceID
AND @SourceName <> 'SQL LogPackageStart'
AND @SourceName <> 'SQL LogPackageEnd'
INSERT INTO dbo.PackageTaskLog (PackageLogID, SourceName, SourceID, StartDateTime)
VALUES (@PackageLogID, @SourceName, @SourceID, getdate())
END
SELECT 0 AS RecordsAffected
END
GO
ALTER PROCEDURE [dbo].[LogPackageEnd]
( @PackageLogID int
,@BatchLogID int
,@EndBatchAudit bit
,@LoggingLevel int
)
AS
BEGIN
SET NOCOUNT ON
/* Check if logging is disabled */
IF @LoggingLevel > 0
BEGIN
UPDATE dbo.PackageLog
SET Status = 'S'
, EndDatetime = getdate()
WHERE PackageLogID = @PackageLogID
IF @EndBatchAudit = 1
BEGIN
UPDATE dbo.BatchLog
SET Status = 'S'
, EndDatetime = getdate()
WHERE BatchLogID = @BatchLogID
END
END
END
GO
ALTER PROCEDURE [dbo].[LogPackageError]
( @PackageLogID int
,@BatchLogID int
,@SourceName varchar(64)
,@SourceID uniqueidentifier
,@ErrorCode int
,@ErrorDescription varchar(2000)
,@EndBatchAudit bit
,@LoggingLevel int
)
AS
BEGIN
SET NOCOUNT ON
/* Check if logging has been disabled */
IF @LoggingLevel > 0
BEGIN
INSERT INTO dbo.PackageErrorLog (PackageLogID, SourceName, SourceID, ErrorCode, ErrorDescription, LogDateTime)
VALUES (@PackageLogID, @SourceName, @SourceID, @ErrorCode, @ErrorDescription, getdate())
UPDATE dbo.PackageLog
SET Status = 'F'
, EndDatetime = getdate()
WHERE PackageLogID = @PackageLogID
IF @EndBatchAudit = 1
Begin
UPDATE dbo.BatchLog
SET Status = 'F'
, EndDatetime = getdate()
WHERE BatchLogID = @BatchLogID
End
END
END
GO
ALTER PROCEDURE [dbo].[LogPackageStart]
( @BatchLogID int
,@PackageName varchar(255)
,@ExecutionInstanceID uniqueidentifier
,@MachineName varchar(64)
,@UserName varchar(64)
,@StartDatetime datetime
,@PackageVersionGUID uniqueidentifier
,@VersionMajor int
,@VersionMinor int
,@VersionBuild int
,@VersionComment varchar(1000)
,@PackageGUID uniqueidentifier
,@CreationDate datetime
,@CreatedBy varchar(255)
,@LoggingLevel int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @PackageID int
,@PackageVersionID int
,@PackageLogID int
,@EndBatchAudit bit
/* Check if logging has been disabled: LoggingLevel = 0*/
IF @LoggingLevel > 0
BEGIN
/* Initialize Variables */
SELECT @EndBatchAudit = 0
/* Get Package Metadata ID */
IF NOT EXISTS (SELECT 1 FROM dbo.Package WHERE PackageGUID = @PackageGUID AND PackageName = @PackageName)
Begin
INSERT INTO dbo.Package (PackageGUID, PackageName, CreationDate, CreatedBy)
VALUES (@PackageGUID, @PackageName, @CreationDate, @CreatedBy)
End
SELECT @PackageID = PackageID
FROM dbo.Package
WHERE PackageGUID = @PackageGUID
AND PackageName = @PackageName
/* Get Package Version MetaData ID */
IF NOT EXISTS (SELECT 1 FROM dbo.PackageVersion WHERE PackageVersionGUID = @PackageVersionGUID)
Begin
INSERT INTO dbo.PackageVersion (PackageID, PackageVersionGUID, VersionMajor, VersionMinor, VersionBuild, VersionComment)
VALUES (@PackageID, @PackageVersionGUID, @VersionMajor, @VersionMinor, @VersionBuild, @VersionComment)
End
SELECT @PackageVersionID = PackageVersionID
FROM dbo.PackageVersion
WHERE PackageVersionGUID = @PackageVersionGUID
/* Get BatchLogID */
IF ISNULL(@BatchLogID,0) = 0
Begin
INSERT INTO dbo.BatchLog (StartDatetime, [Status])
VALUES (@StartDatetime, 'R')
SELECT @BatchLogID = SCOPE_IDENTITY()
SELECT @EndBatchAudit = 1
/* Update previous batches to 'E' - error if 'R' - Running */
UPDATE dbo.BatchLog
SET [Status] = 'E'
WHERE BatchLogID != @BatchLogID
AND [Status] = 'R'
End
INSERT INTO dbo.PackageLog (BatchLogID, PackageVersionID, ExecutionInstanceID, MachineName, UserName, StartDatetime, [Status])
VALUES(@BatchLogID, @PackageVersionID, @ExecutionInstanceID, @MachineName, @UserName, @StartDatetime, 'R')
SELECT @PackageLogID = SCOPE_IDENTITY()
END
SELECT @BatchLogID as BatchLogID, @PackageLogID as PackageLogID, @EndBatchAudit as EndBatchAudit
END
GO
ALTER PROCEDURE [dbo].[LogTaskPostExecute]
( @PackageLogID int
,@SourceID uniqueidentifier
,@PackageID uniqueidentifier
,@LoggingLevel int
,@RecordsAffected int = 0
)
AS
BEGIN
SET NOCOUNT ON
/* Do not log if logging is off */
IF @LoggingLevel > 0
BEGIN
IF @PackageID <> @SourceID
UPDATE dbo.PackageTaskLog
SET EndDateTime = getdate(), RecordsAffected = @RecordsAffected
WHERE PackageLogID = @PackageLogID AND SourceID = @SourceID
AND EndDateTime is null
END
END
