How to Enable Custom Logging for an SSIS Script Task

At times I like to capture certain errors and events that occur in a Script Task in an SSIS package, and include them in the package logging.  In order to make this happen simply include the appropriate statements in your Script Task and turn on some custom logging within the package logging configuration.

Add a Dts.Log statments to your vb Script Task.  For example:

            Dim dataBytes(0) As Byte
            Dts.Log("Did not find expected database", 0, dataBytes)

In order for this message to be included in the [sysssislog] table simply right click on the package Control Flow surface, and select Logging. Within the Containers window, drill down to your Script Task.

image

Check the box beside the Script Task until it has a black check mark, instead of a greyed out check mark. In the Providers and Logs tab select the log you want to write to.

image

On the Details tab select the Events you wish to log, and be sure to select the ScriptTaskLogEntry.

image

Click OK and you’re done.  Your custom messages will be included in the package logs.

Configure SSIS Logs

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.

image

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.

image

I like to log errors in SQL Server.  To do this, change the Provider Type to SSIS log provider for SQL Server.

image

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.

image

Check the box beside the Package_Template container and then check the box beside the log you have just created.

image

Select the Details tab.  Specify which events you would like to capture.

image

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.