Custom Logging in SSIS

Logging in SSIS (Integration Services) is a useful method that you should build into every package by default. With logging you can always look up (if needed) what time packages were run and whether everything went well, which helps auditing. You may need this when issues have occurred.

By default you can choose a number of events to log, such as warnings, errors, task failed, etc.

Often it is not enough to log only the standard events such as “starttime and endtime”, and you want to record more. There are several articles online about custom logging of Integration Services packages. This article covers a method I used myself on a project. For example, I want to log the number of records loaded from the source into the staging area. The image below shows the standard logging events.

I also want to know how many records were updated from staging to the data warehouse. By default, items like these are not logged, hence this method.
Items to log
• Start date
• End date
• Inserted Row Count
• Read Row Count
• Updated Row Count
• Error Row Count

… and some more information about the ETL package, such as the name, processID, version, etc. The package has a pre-execute part and a post-execute part. Pre-execute runs before the real data load starts. Post-execute takes place after that. The data written in the Post-execute part is kept in variables. As soon as the data is loaded, the number of records that go through the data flow is counted, for example, and put in the variable. Finally this is written to the table.
Step 1 – Creating the metadata tables
All data we store will be kept in metadata tables. These have to be created first.
{geshi xml:lang=“tsql” lines=“false”}
CREATE TABLE [dbo].[SSISMapping](
[MappingNummer] [varchar](38) NOT NULL,
[Naam] [varchar](64) NOT NULL,
[LaatsteProcesDatum] [datetime] NULL,
[LaatsteProcesNummer] [varchar](38) NULL,
CONSTRAINT [PK_SSISMapping] PRIMARY KEY CLUSTERED
(
[MappingNummer] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[SSISMappingProces](
[MappingProcesNummer] [varchar](38) NOT NULL,
[MappingNummer] [varchar](38) NOT NULL,
[VersieBuildNummer] [int] NULL,
[ProcesDatum] [datetime] NULL,
[StartDatum] [datetime] NULL,
[EindDatum] [datetime] NULL,
[AantalGeladen] [int] NULL,
[AantalToegevoegd] [int] NULL,
[AantalGewijzigd] [int] NULL,
[AantalVerwijderd] [int] NULL,
[AantalUitgevallen] [int] NULL,
[AantalFouten] [int] NULL,
[AantalWaarschuwingen] [int] NULL,
CONSTRAINT [PK_SSISMappingProces] PRIMARY KEY CLUSTERED
(
[MappingProcesNummer] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]{/geshi}
SSISMapping stores the names of the packages. It is linked to SSISMappingProces through MappingNummer. MappingNummer is the GUID from the ETL package.

Step 2 – Pre Execute

Here the current date/time is set first. Then some metadata is added to the data stream:
{highslide type=“img” url=“artikelen/ssis/customlogging/post_add_metadata.jpg” width=600 captionText=‘Adding metadata’}{/highslide}
Then we check in SSISMapping whether the package has run before. If so, some meta fields such as the last runtime are updated. If not, a record is created. The other side of the data flow goes to SSISMappingProces, which already writes some data about the execution, such as packageid and versionbuild:

Step 3 – Data flow task
In the task that matters, the task that moves the data from source to destination, the number of rows is counted. For this you use two row count tasks. These two values are written to variables.

Step 4 – Post Execute
First the end date is set. Then metadata is added to the data stream again.

Finally this metadata is written to SSISMappingProces.

CustomLogging