SQLBlog Nederland

Je zoekt een bepaalde kolomnaam in een van je tabellen in je SQL Server database maar kan hem even niet vinden. Dit kan een tijdrovende klus zijn als je onderstaande truuk niet kent.  In SQL Server 2005 ontbreekt een zoekfunctionaliteit om snel een kolom te kunnen vinden in databases. Dit kan nog weleens handig zijn als je een bepaalde kolom zoekt maar niet weet in welke tabel deze zich bevindt. De oplossing hiervoor is een vrij eenvoudige Query.

SELECT O.NAME Tabelnaam,
C.NAME Kolomnaam
FROM sys.columns C
INNER JOIN sys.objects O ON C.object_id = O.object_id
WHERE C.NAME LIKE '%DebiteurNaam%'
ORDER BY O.NAME,
C.NAME

Resultaat

Het is natuurlijk niet erg handig om iedere keer dat je een kolom zoekt deze hele query in te typen, daarom is er ook de volgende Stored-Procedure die de taak wel heel eenvoudig maakt:

CREATE PROCEDURE [dbo].[pcdGetColumns]
@kolomnaam nvarchar(256)
-- Zoek een kolomnaam in een database
-- EXECUTE pcdGetColumns '%naam%'
AS
BEGIN
 
SELECT O.NAME Tabelnaam,
C.NAME Kolomnaam
FROM sys.columns C
INNER JOIN sys.objects O ON C.object_id = O.object_id
WHERE C.NAME LIKE @kolomnaam
ORDER BY O.NAME, C.NAME
 
END

Iedere kent ze wel, hierarchieën in Analysis Services 2005. Met een hierarchy kun je bepaalde data op verschillende niveau’s aan de eindgebruiker tonen. Een bekend voorbeeld is:

Jaar -> Kwartaal -> Maand -> Dag

Omdat ik het nogal onduidelijk vind hoe je dit precies moet configureren leek het mij handig hier een blogje over te schrijven.

Lees meer…

Wanneer er een fout ontstaat in je ETL-package wil je als Datawarehouse beheerder ook graag weten wat en wanneer er iets fout is gegaan. Wanneer je gebruik maakt van een event handler wordt er bijvoorbeeld een e-mail afgevuurd met de melding dat er een fout is opgetreden maar waar het fout is gegaan is dan onbekend.

Dit artikel laat zien hoe je een event handler kunt configureren in je masterpackage die een rapport mailt met errordetails vanaf het startpunt van de laatste run. Ik ga er even vanuit dat er een masterpackage aanwezig is die alle subpackages aanroept en dat logging is geactiveerd. Logging is namelijk nodig om te kunnen bepalen wanneer de laatste run gestart is.

Stap 1: Event Handler configureren
Het eerste dat moet worden gedaan is het aanmaken van een event handler die een aantal stappen doorloopt op het moment dat er iets fout gaat. Klik op het tabblad “Event Handlers” en kies het niveau waarop de event handler moet afgaan. In onderstaand voorbeeld is de scope van de event handler het gehele package. Je kunt een event handler ook op een individuele task configureren. Kies in de tweede dropdown box de optie “OnTaskFailed”. Dit zorgt ervoor dat deze event handler wordt aangeroepen op het moment dat de package failed.

Vervolgens sleep je de volgende taken in het scherm: dataflowtask, send mail task en een file system task. Vervindt deze in dezelfde volgorde aan elkaar met de precedence contraint (groene pijl).

Stap 2: Data-flow task configureren
Vervolgens gaan we een dataflow task aanmaken die een dataset voor ons op gaat halen met informatie over de foutmeldingen. Dit is handig om vast te kunnen stellen waar de fout daadwerkelijk is ontstaan. Standaard worden alle fouten gelogd in de sysdtslog90 tabel.

{highslide type=”img” url=”artikelen/ssis/SSIS_Errorlog_Email_01.JPG” width=350 captionText=’SSIS’}{/highslide}

Dubbelklik op de dataflowtask, je komt nu in de dataflowtask editor. Sleep een OLEDB-Source op het scherm en een Flat File Destination en verbindt deze met elkaar met de groene pijl. Geef de twee taken direct betekenisvolle namen zoals: OLEDBSRC Sysdtslog90 en FFD ErrorLogETL zodat voor andere mensen ook duidelijk is wat er gebeurd zonder in de codes te neuzen. Je dataflowtask ziet er ongeveer zo uit:

{highslide type=”img” url=”artikelen/ssis/SSIS_Errorlog_Email_02.JPG” width=350 captionText=’SSIS’}{/highslide}

Nu moeten we de OleDB Source gaan instellen zodat alleen de foutmeldingen worden opgehaald die zijn ontstaan tijdens de laatste run. Omdat SSIS start- en eindtijden van packages logt wordt zo ook de starttijd van het masterpackage. Wat we nu eigenlijk willen weten is:
- > Geef me een lijstje van alle foutmeldingen die zijn ontstaan tijdens de laatste run.

Het eerste dat we dus willen weten is wanneer de laatste run is gestart. Dit kunnen we vinden door te zoeken naar de laatste keer dat het event “PackageStart” is opgetreden voor het masterpackage. Daarna gaan we kijken welke foutmeldingen er zijn ontstaan na dit tijdstip. De volgende query is hiervoor te gebruiken. Het enige dat je moet veranderen is de naam van de package.

————————————–
SELECT ROW_NUMBER() OVER (ORDER BY Starttime ASC) AS FoutNr,
source AS Bron,
starttime AS Starttijd,
[message] AS Foutmelding
FROM sysdtslog90 AS S
WHERE starttime >= (SELECT TOP 1
starttime
FROM sysdtslog90
WHERE source = ‘VUL_HIER_JE_PACKAGENAAM_IN’
AND Event = ‘PackageStart’
ORDER BY starttime desc
)
AND [event] LIKE ‘%error%’
————————————–
Voer bovenstaande query in bij het configureren van je oledbsource en kies als OLE DB Connection Manager een connectie naar de database waarin de sysdtslog90 tabel te vinden is en kies als Data Acces Mode voor “SQL command”

{highslide type=”img” url=”artikelen/ssis/SSIS_Errorlog_Email_03.JPG” width=350 captionText=’SSIS’}{/highslide}

Nu gaan we de verkregen data wegschrijven in een textfile die we straks gaan mailen. Dubbelklik op de eerder aangemaakte Flat File Destination en configureer deze naar wens. Maak hierbij dus ook een connection manager aan met bijvoorbeeld een delimited text file, net wat je het makkelijkst vindt.

{highslide type=”img” url=”artikelen/ssis/SSIS_Errorlog_Email_04.JPG” width=350 captionText=’SSIS’}{/highslide}

De dataflowtask is nu gereed. Er wordt een dataset opgehaald met alle foutmeldingen en deze worden netjes weggeschreven in een textfile.

Wat we nu nog moeten doen is een e-mail laten versturen naar de beheerder.

Stap 3: E-mail task Configureren
Keer terug naar je event handler tabblad en dubbelklik daar op de Send Mail Task. Configureer daar alle instellingen die nodig zijn dus: SMTP Server (eerst aanmaken), From, To, Subject, MessageSource. Bij attachment kies je de textfile die eerder is aangemaakt tijdens de dataflowtask. Dit path moet dus overeenkomen, bijv: d:SQLLogErrorLogETL.txt

Het kan zijn dat hij nu een warning geeft omdat het textbestand immers nog niet bestaat. Tijdens het uitvoeren van het package zal dit bestand automatisch gegenereerd worden dus je kunt deze foutmelding negeren. Let ook even op McAfee virusscan die standaard port25 blokkeert. Dit kun je uitschakelen.

Stap 4: Logfile opruimen
Het is wel zo netjes om na afloop de aangemaakte logfile weer op te ruimen. Dubbelklik op de File System Task. Kies daar voor Operation: Delete File. Bij SourceConnection kies je de Flatfileconnection die eerder aangemaakt is en die de log bevat.

{highslide type=”img” url=”artikelen/ssis/SSIS_Errorlog_Email_05.JPG” width=350 captionText=’SSIS’}{/highslide}

We zijn nu klaar. Test of het werkt door een error ergens in te bouwen en het package te runnen. Voor opmerkingen of vragen, laat een bericht achter!