Custom Logging in SSIS

 

Logging In SSIS (Integration Services) is een handige methode die je standaard in iedere package zou moeten inbouwen. Door te loggen kun je (indien nodig) altijd terugzoeken hoe laat packages zijn uitgevoerd en of alles goed is gegaan wat auditen ten goede komt. Dit kan noodzakelijk zijn wanneer er bepaalde issues zijn opgetreden.

Standaard kun je een aantal events instellen die je kunt loggen. Denk daarbij aan warnings, errors, task failed, etc.

Vaak is het niet voldoende om enkel de standaard events als “starttime en endtime” te loggen maar wil je meer zaken gaan vastleggen.Op internet zijn diverse artikelen te lezen over custom logging van Integration Services packages. Dit artikel gaat in op een methode die ik zelf heb gebruikt bij een project. Wat ik bijvoorbeeld wil loggen zijn het aantal records die zijn geladen vanuit de bron richting staging area.Onderstaande afbeelding toont de standaard logging-events.

Wat ik ook wil weten is hoeveel records er zijn ge-update van staging naar datawarehouse. Standaard worden dit soort items niet gelogd, vandaar deze methode.
Items om te loggen
•    Startdatum
•    Einddatum
•    Inserted Row Count
•    Read Row Count
•    Updated Row Count
•    Error Row Count

… en nog enkele informatie over het ETL-package zoals de naam, procesID, versie, etc. De opbouw van het package bestaat uit een pre-execute en een post-execute gedeelte. Pre-execute wordt uitgevoerd voordat de echte data-load begint. Post-execute vindt daarna plaats. De gegevens die in de Post-execute worden weggeschreven worden in variabelen bewaard. Zodra de data is geladen wordt het aantal records bijvoorbeeld geteld die door de dataflow gaan en in de variabele gezet. Tot slot wordt dit weggeschreven.
Stap 1 – Aanmaken meta-data tabellen
Alle data die we gaan opslaan zal bewaard worden in meta-data tabellen. Deze zullen eerst aangemaakt moeten worden.
{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}
In SSISMapping worden de namen van de packages bewaard. Deze is verbonden met SSISMappingProces mbt MappingNummer. Mappingnummer is het GUID uit de ETL-package.

Stap 2 – Pre Execute

Hier wordt eerst de huidige datum/tijd bepaald. Daarna wordt er enkele metadata aan de datastroom toegevoegd:
{highslide type=”img” url=”artikelen/ssis/customlogging/post_add_metadata.jpg” width=600 captionText=’Metadata toevoegen’}{/highslide}
Daarna wordt er gekeken in SSISMapping of het package al eerder is uitgevoerd. Zoja worden er enkele metavelden geupdate zoals de laatste runtime. Zoniet wordt er een records aangemaakt. De andere kant van de dataflow gaat naar de SSISMappingProces die informatie alvast wat data wegschrijft over de execution zoals packageid en versionbuild:

Stap 3 – Dataflowtask
In de task waar het om gaat, de task waarin de data van bron naar bestemming wordt vervoerd zullen het aantal rijen worden geteld. Hiervoor gebruik je twee count row taken. Deze twee waarden worden weggeschreven in variabelen.

Stap 4 – Post Execute
Eerst wordt de einddatum bepaald. Daarna worden er weer metagegevens toegevoegd aan de datastroom.

Tot slot worden deze metagegevens weggeschreven in SSISMappingProces.

 CustomLogging