I was working on an ETL solution, which was expected to run for hours. It has become extremely important to track the errors properly, so as to debug the error quickly.
During the initial phase, we had to struggle with the errors. I googled a lot to find a solution that can lead me directly to the actual error. This becomes very important when we work with the data flows, and wish to know which data row is causing the error.
I could not find a direct solution. But I built a workaround to make it happen.
We all know about the SSIS logging. Most of the times it happens that our ETL solution cannot survive just with the SSIS built-in logging. We need a custom logging solution to track the package execution effectively, and to be able to make the reporting out of it.
If you’ve worked on SSIS previously, then you would be aware of sysssislog table, which automatically gets created when we enable the logging in SSIS. You can log various SSIS events in this table. When you enable the logging, it will ask for the events you want to enable for the logging.
sysssislog table has 4 important columns in which I was most interested. They are – executionid, starttime, endtime, event and message.
- executionid: It is of type uniqueidentifier, and is unique for each execution, but will be same for all the events in a particular execution. So if you want to know what all things happened during a package run / execution, then this value is of the immense relevance.
- starttime & endtime: As the name suggest, they represent the start and end time of the event.
- event: This is the column which provides the name of the event which generated the log entry. For example PackageStart, PackageEnd, OnError etc.
- message: This column holds the message associated with the log entry. The value in the message column will represent the message which you generally see in the SSDT IDE under Debug -> Output window. If you want to trace what all things happened during the package execution, then get all the log entries against the executionid, sorted by starttime or id column (an identity column in the sysssislog table) in ascending order, and follow the message. This column gives you the detailed error information for all the errors occurred against the respective events.
In my custom log too, I had the executionID column of type uniqueidentifier. But the challenge was, it was by no mean linked with the executionid column of the sysssislog table. I thought to link my custom logging table with the sysssislog table, but could not figure out a direct solution.
Then I thought to link the executionid of the sysssislog with my custom log table. I could control the executionid to be inserted in the custom log, but I can’t control the executionid value of sysssislog table. I then realize, I can refer the executionid of sysssislog and insert the custom log against it.
I was very happy to see that finally I was able to link my custom log with the SSIS built-in log i.e. sysssislog table. It was a simple trick.
In my SSIS package, I was writing a custom log entry at the start of the package using a Execute SQL Task. I created the following procedure, trigged it in the Execute SQL Task, and returned the @ExecutionID as an output parameter, by assigning it to a user variable. Since now I had the ExecutionID in a variable, I could use it all across in my package.
/*
DECLARE @ExecutionIDUNIQUEIDENTIFIER
EXEC [dbo].[usp_Get_ETL_ExecutionID]
@ExecutionID=@ExecutionIDOUTPUT
SELECT @ExecutionID
*/CREATE PROCEDURE [dbo].[usp_Get_ETL_ExecutionID]
(
@ExecutionIDUNIQUEIDENTIFIER OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
IF OBJECT_ID('sysssislog') IS NOT NULL
BEGIN
IF EXISTS (SELECT 1 FROM sysssislog WHERE [event] = 'PackageStart' AND starttime BETWEEN DATEADD(SECOND, -5, GETDATE()) AND GETDATE())
BEGIN
SELECT @ExecutionID = SSISLOG.ExecutionID
FROM sysssislog SSISLOG
LEFT JOIN log_Jazz_Package_Execution ETLLOG
ON ETLLOG.ExecutionID = SSISLOG.ExecutionID
WHERE SSISLOG.[event] = 'PackageStart'
AND SSISLOG.starttime BETWEEN DATEADD(SECOND, -5, GETDATE()) AND GETDATE()
AND ETLLOG.ExecutionID IS NULL
END
END
IF @ExecutionID IS NULL SET @ExecutionID = NEWID();
END
There may be a simple and direct way, which I’m not aware of. So though to make my own way, and was successful!
If you know a simple and direct way, please post it in the comment. Someone would be definitely be benefitted with your inputs!