Thursday, February 18, 2016

Error logging in SSIS

A very cool feature where the error from SSIS can be logged with few steps. Right click on the control flow canvas and select Logging.
In the Add a new log section you can select a Provider type. I have selected the SSIS log provider for SQL Server since I want to log the error to the database.  The ErrorLogConn  is the Environment variable that I have created in Project level. ( This points to the msdb database where I want to log the error)


In the following screen you get to select the events that you want to log for. I have selected only the following events keeping in mind that selecting all events might fill up the database soon.

OnError
OnTaskFailed
OnWarning


Once you hit OK an save the project, deploy it and that's all.
 
 
You can run the following to see the last error
 
 
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
 
(The above query was taken from the blog http://dataqueen.unlimitedviz.com/2012/08/logging-in-ssis/ - an awesome blog!)

1 comment: