As a continuation of Creating an SSIS Package Template with Predefined Package Configurations I wanted to share with you another good habit for your package template. I like to add logging to my packages for the purposes of troubleshooting, especially when there are complex processes with multiple packages running at once. It helps me track how long packages take to run and gives me the ability to see if there are changes over time, as well as the obvious error logging.
It is very simple to turn on the logging. Simply right click on your Control Flow canvas and select Logging.
This is another place where having a sequence containers within your package are a good idea. I generally have the entire package within one master sequence container, and then divide up the tasks within other sequence containers as necessary. You can set the logging for the main Sequence Container within your package and you will notice that the other items within that container will inherit the logging configuration. You can deselect certain tasks or containers if required.
I like to log errors in SQL Server. To do this, change the Provider Type to SSIS log provider for SQL Server.
Click the Add button. In the Configuration column use the dropdown to select the connection to the SQL database where you would like to store the logs.
Check the box beside the Package_Template container and then check the box beside the log you have just created.
Select the Details tab. Specify which events you would like to capture.
Keep in mind that each event will take up space in your database. Depending on how many packages you are logging and how often, you may with to only track important events, or you may wish to delete some or all of the events on a scheduled basis.
Here are a few views I like to create for use when checking the logs.
CURRENT ERRORS
CREATE VIEW [dbo].[ssislog_ErrorsCurrent]
AS
SELECT TOP (100) PERCENT PKG.PackageName, PKG.starttime AS PackageStartTime, LG.source AS TaskName, LG.starttime AS StartTime, LG.endtime AS EndTime,
LG.message
FROM dbo.sysssislog AS LG INNER JOIN
(SELECT LG1.executionid, LG1.source AS PackageName, LG1.starttime
FROM dbo.sysssislog AS LG1 INNER JOIN
(SELECT source, MAX(starttime) AS starttime
FROM dbo.sysssislog
WHERE (event = ‘PackageStart’)
GROUP BY source
HAVING (MAX(starttime) > DATEADD(dd, – 1, GETDATE()))) AS CUR ON CUR.source = LG1.source AND CUR.starttime = LG1.starttime
WHERE (LG1.event = ‘PackageStart’)) AS PKG ON LG.executionid = PKG.executionid
WHERE (LG.event IN (‘OnError’))
ORDER BY PackageStartTime DESC, StartTime
CURRENT LOG ENTRIES
CREATE VIEW [dbo].[ssislog_LogEntriesCurrent]
AS
SELECT TOP (100) PERCENT PKG.PackageName, PKG.starttime AS PackageStartTime, LG.source AS TaskName, LG.starttime, LG.endtime, LG.message
FROM dbo.sysssislog AS LG INNER JOIN
(SELECT LG1.executionid, LG1.source AS PackageName, LG1.starttime
FROM dbo.sysssislog AS LG1 INNER JOIN
(SELECT source, MAX(starttime) AS starttime
FROM dbo.sysssislog
WHERE (event = ‘PackageStart’)
GROUP BY source
HAVING (MAX(starttime) > DATEADD(dd, – 1, GETDATE()))) AS CUR ON CUR.source = LG1.source AND CUR.starttime = LG1.starttime
WHERE (LG1.event = ‘PackageStart’)) AS PKG ON LG.executionid = PKG.executionid
ORDER BY LG.endtime DESC
PACKAGE DURATION
CREATE VIEW [dbo].[ssislog_PackageDurationCurrent]
AS
SELECT TOP (100) PERCENT source AS PackageName, MIN(starttime) AS StartTime, MAX(starttime) AS EndTime, DATEDIFF(MI, MIN(starttime), MAX(starttime))
AS DurationInRoundedMinutes, DATEDIFF(ss, MIN(starttime), MAX(starttime)) AS DurationInTotalSeconds
FROM dbo.sysssislog
WHERE (event IN (‘PackageEnd’, ‘PackageStart’)) AND (starttime > DATEADD(dd, – 1, GETDATE()))
GROUP BY executionid, source
ORDER BY starttime DESC
I hope you find this useful.
