Archiv der Kategorie: MS Integration Services

Microsoft Integration Services 2005, 2008, 2008 R2, 2012, 2014

SELECTS beim Lookup dynamisch zusammenstellen

Manchmal reicht es nicht aus, feste SQL-Statements als Quelle oder bei Lookups zu hinterlegen.

Bei einer OLE-DB-Quelle kann man ganz einfach das SQL-Statement in einer Variable ablegen.

Wie man das auch bei Lookups machen kann, zeigt dieser Eintrag.

Zunächst zur Motivation:

Man könnte sich vorstellen, dass innerhalb eines Datenflusstasks immer nur Daten eines Tages in eine Tabelle geschrieben werden. Der Tag steht dabei in einer Variablen. Nun soll bei jedem Lookup überprüft werden, ob der Primary Key der Daten an diesem Tag schon in einer Tabelle steht. Da der Lookup alle Daten cacht, macht es Sinn nur die Daten des Datums aus der Variablen zu lesen.

In unserem Beispiel heißt die Variable „Tag_deutsch“ und speichert das Datum als String mit deutschem Datumsformat.

Das Lookup-SQL müsste also so aussehen:

select * from ZahlenProTag
Where Tag = CONVERT(date, '7.5.2010', 104)

oder allgemein

select * from ZahlenProTag
Where Tag = CONVERT(date, '<Inhalt der Variablen Tag_deutsch>', 104)

Deswegen legen wir eine berechnete (EvaluateAsExpression:=true) Variable „Lookup-SQL“ an, mit der Expression:

"select * from ZahlenProTag
Where Tag = CONVERT(date, '" + @[User::Tag_deutsch] + "', 104)"

Jetzt bauen wir erst mal unseren Data Flow Task, noch mit dem kompletten Lookup auf ZahlenProTag:

BeispielDataFlow

Zu den einzelnen Komponenten:

  • alle Zahlen von 1 bis 10 habe ich bereits in meinem letzten Blogeintrag verwendet (s. http://csopro.de/biblog/2010/04/temporaere-tabellen-in-ssis-verwenden/). Es liefert alle Zahlen von 1 bis 10
  • Eine abgeleitete Spalte mit dem Namen Datum wird hinzugefügt, mit folgender Formel:
    (DT_DBDATE)@[User::Tag_deutsch]
    ACHTUNG. Dabei muss als LocaleID deutsch eingestellt sein, da der String ja in deutschem Datumsformat steht
  • Der Lookup sieht auf dem ersten Tab so aus:
    LookupAllgemein
    und so auf dem zweiten:
    LookupVerbindung
    und so auf dem dritten:
    LookupSpalten
    Man beachte, dass zum Lookup nur die Zahl und nicht das Datum verwendet wird.
    Letzteres könnte man natürlich machen. Dann würde aber die gesamte Tabelle in den Cache geladen werden, obwohl wir wissen, dass nur ein kleiner Bruchteil benötigt wird.
  • Das OLE-DB-Ziel ist wieder naheliegend:
    Tab 1:
    ZielVerbindung
    Tab 2:
    ZielZuordnungen

Soweit so gut.

Nun wollen wir das Lookup-SQL auf die oben angelegte Variable umstellen. Dazu gehen wir in die Ablaufsteuerung / Workflow und selektieren den Datenfluss-Task, so dass wir im Eigenschaftsfenster dessen Eigenschaften sehen.

Dort fällt auf, dass es unter Sonstiges eine Eigenschaft mit dem Titel [Zahl an diesem Tag schon da?].[SqlCommand] gibt:

SqlCommand

[Zahl an diesem Tag schon da?] ist ja der Name der Lookup-Komponente.

SqlCommand gibt das SQL an, das zum Befüllen des Caches verwendet wird (wir hatten ja den Standard Vollcache belassen)

SqlCommandParam würde man nur bei den anderen Cache-Typen benötigen.

Nun können wir über Expressions des Data Flow Tasks diese Eigenschaft überschreiben:

ExpressionsÜberschreiben

So jetzt geht’s!

Im Status bzw. Protokoll kann man kontrollieren, wieviele Zeilen der Lookup gecacht hat:

[Zahl an diesem Tag schon da? [13]] Informationen: ‚Komponente ‚Zahl an diesem Tag schon da?‘ (13)‘ hat insgesamt 0 Zeilen zwischengespeichert.

Diese Zeile kommt, wenn man die Variable auf ein neues Datum stellt.

Wenn die Variable auf einem bereits verwendeten Datum steht, kommt:

[Zahl an diesem Tag schon da? [13]] Informationen: ‚Komponente ‚Zahl an diesem Tag schon da?‘ (13)‘ hat insgesamt 10 Zeilen zwischengespeichert.

Also ist alles OK!

temporäre Tabellen in SSIS verwenden

Für manche Aufgaben erscheint es sinnvoll, Daten zunächst in temporäre Tabellen zu übertragen, um sie dann zum Beispiel per Insert oder Update in die Zieltabelle zu überführen (damit kann man ein „Bulk Update“ durchführen).

Dazu kann man natürlich im SQL-Server beliebige Tabellen anlegen, die dann nur „logisch“ temporär sind, da sie ja ständig in der Datenbank sind.

Der SQL Server bietet aber auch temporäre Tabellen an, die sessionweit (# – sobald die Connection geschlossen wird, verschwindet die Tabelle) oder global (## – sobald die letzte Connection geschlossen wird, die diese Tabelle verwendet, verschwindet sie) gelten.

Diese in SSIS zu verwenden, mag z.B. Sinn machen, wenn man keine Tabellen-Erstellungs-Rechte auf dem Zielserver hat.

Sie zu verwenden, ist nicht ganz einfach:

Nehmen wir an, wir wollen eine temporäre Tabelle mit einer einzigen numerischen Spalte anlegen, so geht das so:

CREATE TABLE [dbo].[#ZielTabelle]
(
[zahl] [int] NOT NULL
)

Randbemerkung:
Man kann auch Primary Keys vergeben, z.B. mit folgendem Statement:

CREATE TABLE [dbo].[#ZielTabelle]
(
[zahl] [int] NOT NULL,
CONSTRAINT ZielTabelle_PK PRIMARY KEY CLUSTERED
(
[zahl] ASC
)
)

Das wäre aber nicht so gut, da dann ein Vorteil verloren geht: Eine solche temporäre Tabelle (#) kann mehrfach existieren (je Connection einmal). Verwendet man aber einen festen Primary-Key-Namen, so geht das nicht mehr. Man könnte in den Namen des Primary Key eine GUID einbauen und hätte wieder Eindeutigkeit.

Dieses Create-Table-Statement baut man in eine Execute-SQL-Task und kann dann folgenden Workflow abbilden:

Workflow

Den DROP TABLE braucht man nicht unbedingt, da nach Schließen der Connection die Tabelle eh verschwindet. Aber aus Gründen der Hygiene empfehle ich es.

Unser Datenflusstask soll im Beispiel einfach alle Zahlen von 1 bis 10 in die temporäre Tabelle schreiben:

DataFlow

Die Skript-Komponente hat folgenden Code:

public override void CreateNewOutputRows()
{
for (int i = 1; i <= 10; i++)
{
AusgabeBuffer.AddRow();
AusgabeBuffer.Zahl = i;
}
}

Nun haben wir aber ein Problem: Die temp-Tabelle taucht nicht in der Liste der Tabellen auf. Deswegen empfiehlt es sich, auf dem Entwicklungsrechner eine Tabelle mit der selben Struktur anzulegen, und diese dann auszuwählen (hier „ZielTab_gibtsNetProduktiv“):

Ziel

Nun kann man auf dem nächsten Reiter die Zuordnungen – wie gewohnt – einstellen (was in diesem Beispiel sehr einfach ist 🙂 ):

Zuordnungen

Noch haben wir aber nichts gewonnen, da die temporäre Tabelle nicht verwendet wird.

Es müssen deshalb noch ein paar Einstellungen vorgenommen werden:

  • Unter Eigenschaften des OLEDB-Ziels muss man in OpenRowset die temporäre Tabelle eintragen:
    Vorher:
    nachher
    Nachher:
    nachher
  • Die weitere Eigenschaft „ValidateExternalMetaData“ des OLEDB-Ziels muss auf „false“ gestellt, da zum Zeitpunkt der Entwicklung bzw. des Starten des Pakets die temporäre Tabelle noch nicht existiert.

Jetzt funktioniert das Paket immernoch nicht. Es tritt folgender Fehler auf:

[Speichern in temp Tabelle [16]] Fehler: Fehler beim Öffnen eines FastLoad-Rowsets für ‚#ZielTabelle‘. Überprüfen Sie, ob das Objekt in der Datenbank vorhanden ist.

Offensichtlich kennt der DataFlow Task die Tabelle nicht. Dies liegt daran, dass der CREATE TABLE-Befehl und der Datenfluss-Task nicht in der selben Connection ausgeführt werden. Deswegen müssen wir noch die Eigenschaft der Connection „RetainSameConnection“ auf true setzen:

RetainSameConnection

Jetzt funktioniert es.

Eine Anmerkung:
Man kann die Zuordnungen des OLEDB-Ziels nicht mehr mit dem normalen Editor bearbeiten, da zum Design-Zeitpunkt die temporäre Tabelle nicht existiert. Man kann das durch das oben gezeigte Umstellen auf eine nur auf dem Entwicklungsserver existierende Tabelle umgehen.

Unerwartetes Nicht-Logging SSIS

Dieser Artikel widmet sich nicht dem Logging, das man selbst in einem Paket aktivieren kann und über Log-Provider detailliert steuern kann.

Gemeint ist mehr das Logging, das SSIS automatisch ohne Zutun des Entwicklers oder Administrators durchführt, s. dazu auch „Understanding Events Logged by an Integration Services Package„.

Standardmäßig loggt jedes SSIS-Paket den Start, das erfolgreiche Ende oder das Fehlschlagen oder Abbrechen eines SSIS-Pakets:

EventLog Start und Failure eines SSIS Pakets

(Zum Vergößern auf das Bild klicken)

Details des Eventlogs beim Scheitern eines Pakets

(Randbemerkung: Das Logging von Start und erfolgreichem Ende lässt sich abschalten – in der Registry HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server100SSIS, Wert von „LogPackageExecutionToEventLog“ auf 1 setzen – die anderen Einträge lassen sich nicht verhindern)

In dem Beispiel hatte ich ein SSIS-Paket erstellt, das nur aus einer Script-Task bestand, die immer einen Fehler schmeißt:

Script-Task zum Fehler schmeißen

Dort ist folgender Code enthalten:

public void Main()
{
Dts.Events.FireError(999, „SubKomponente A“, „Fehler – Test fehler“, „“, 0);
Dts.TaskResult = (int)ScriptResults.Success;
}

Das Ergebnis ist oben zu bewundern.

Nun hatten wir die Idee, dieses Eintrag im Event Log zu überwachen (mit NetIQ oder einem anderen Werkzeug zur Überwachung von Servern, Diensten, etc.) und so zu erfahren, wenn ein Paket gescheitert ist.

Das funktioniert aber leider nicht.

Der Grund ist, dass ein solcher Eintrag im Eventlog nur erzeugt wird, wenn das Paket bereits angelaufen ist. Wenn aber schon bei der Validierung ein Fehler auftritt, wird kein Eintrag im Eventlog erzeugt.

Dazu habe ich das Paket erweitert um einen Data Flow Task, bei dem ich in der Quelle „SELECT 1 as Zahl“ eingetragen habe. SSIS Paket mit Fehler brerits beim Validieren

Dieses Paket habe ich dann im SQL Server Agent als Job eingerichtet. Dabei habe ich eingestellt, dass im Fehlerfall der SQL Server Agent das Ergebnis im Eventlog festhalten soll:

Properties des SQL Server Agent Jobs zum Protokollieren im Event Log

(Zum Vergößern auf das Bild klicken)

Solange die Quelle des SSIS-Pakets bekannt ist, ist alles wie erwartet. Im Eventlog werden folgende 3 Events festgehalten (da ja mein Script-Task dazu führt, dass das Paket auf einen Fehler läuft):

Die Events, die durch ein Paket mit vorhandener Connection erzeugt werden

Die Meldungen sind:

  1. Starten des SSIS Pakets (Information)
  2. Fehler des SSIS Pakets (Fehler)
  3. Fehler des SQL Server Agent Jobs (Warnung)

Letzte Meldung sieht im Detail so aus:

Ereignis-Details der SQL Server Agent-Meldung, dass der Job nicht erfolgreich war

Wenn ich jetzt aber die Connection des Statements „SELECT 1“ ändere, so dass der dort angegebene Computer nicht existiert, passiert folgendes, wenn das Paket im SQL Server Agent gestartet wird:

Ereignisanzeige, wenn die Connection des SSIS Pakets nicht validiert werden kann

Man sieht, dass die SSIS-Eventlog-Einträge nicht vorhanden sind. Dies liegt daran, dass der Fehler beim Validieren auftritt und deshalb das Paket noch gar nicht angelaufen ist.

Deswegen macht es keinen Sinn, die SSIS-Eventlog-Einträge für eine Überwachung der Jobs zu verwenden.

Stattdessen können die SQL Server Agent-Einträge überwacht werden. Dabei ist aber zu beachten, dass dies bei jedem SQL Server Agent Job – wie oben gezeigt – eingestellt werden muss.

MDX-Skripte eines Cubes über C#-Code anpassen

Zu dem Standard-Aufgaben bei SSAS-Projekten gehören KPIs, wobei Ist- und Planwerte verglichen und danach der Status einer KPI berechnet wird. Heute möchte ich mich auf den Status fokusieren.

Eine normale Regel könnte sein:

  • Wenn Ist >= Plan, dann Status grün
  • Wenn Ist >= 90% des Plans, dann Status gelb
  • Sonst Rot

Das sähe im MDX-Skript in etwa so aus:

CREATE MEMBER CURRENTCUBE.[Measures].[OPE_Status]
AS iif( [Measures].[OPE] >= [Measures].[OPE_Plan] , 1,
iif( [Measures].[OPE] >= [Measures].[OPE_Plan] * 0.9, 0, -1)),
VISIBLE = 1;

oder

OPE_Status

Dabei steht verabredungsgemäß +1 für grün, 0 für gelb, -1 für rot (wenn es natürlich auch andere Möglichkeiten gibt).

Möglicherweise will man nun aber den Faktor 90% für die Schwelle zwischen Gelb und Rot (oder auch den Schwellwert 100% für die Grenze zwischen Grün und Gelb) dynamisch gestalten – zum Beispiel durch die Eingabe in einer Administrationskonsole. Dann wäre es schön, wenn man dieses MDX dynamisch anpassen könnte.

Deswegen beschreibe ich hier, wie das geht:

Als erstes muss in C# der Verweis Microsoft.AnalysisServices eingebunden werden. Verwirrenderweise findet man diesen nicht unter Microsoft…, sondern unter Analysis Management Objects (kurz AMO):

Verweis

Diesen Namespace verwendet man mit

using SSAS = Microsoft.AnalysisServices;

Danach geht es recht einfach:

SSAS.Server server = new SSAS.Server();

try
{
server.Connect(„Data source=<SSAS-Servername>“);
SSAS.Database db = server.Databases.FindByName(„<SSAS-Datenbankname>“);
SAS.Cube cb = db.Cubes.FindByName(„<SSAS-Cubename>“);
}
catch (Exception e) …

Auf die MDX-Skripte hat man dann mit

cb.MdxScripts[0].Commands[0].Text

Zugriff. Diesen String kann man dann auch manipulieren. Damit die Veränderungen auf den Analysis Services gespeichert werden, muss man die Änderungen mit

cb.MdxScripts[0].Update();

speichern.

Ich empfehle die dynamischen MDX-Anteile von den statischen durch Kommentare wie

/*Beginn Statusberechnungen*/n/* Den Text zwischen diesen Markierungen NICHT verändern, da er autogeneriert ist*/n

/* Ende Statusberechnungen */

voneinander zu trennen.

Dann kann man auch durch einfache String-Manipulation den zu ändernden Teil herausfischen, ihn ändern und wieder zurückschreiben, ohne den kompletten Cube zu zerstören 🙂

Natürlich muss obiges nicht in einem eigenen C#-Programm programmiert werden, es kann auch als Teil eines SSIS-Pakets verwendet werden.

In einem meiner Projekte verwendete ich einen Data Flow Task, in dem ich die einzelnen Status-Formeln berechnete und in einer Skriptkomponente (als Ziel) die MDX-Skripte in dem Cube aktualisierte. Den Code für die Skriptkomponente habe ich hier als Anlage beigefügt. (Das ist offensichtlich SQL Server 2008, da C# ja erst dann verwendet werden kann 🙂 )

Automatisierung von Analysis Services-Aufgaben über XMLA

Alle Aufgaben, die man im SQL Server Management Studio für den Betrieb eines SQL Server Analysis Services vornehmen kann, können auch über XMLA gesteuert werden.

Das gilt zum Beispiel für das Aufbereiten (Verarbeiten, process) von Cubes oder dem Backup von Datenbanken. Ich werde mich heute mit dem Backup einer Analysis Services – Datenbank beschäftigen.

Das Schöne ist, dass wir gar nicht die Syntax der XMLA nachschlagen müssen. Das SQL Server Management Studio erstellt uns nämlich den notwendigen XMLA-Code automatisch (und das gilt für beide Versionen 2005 und 2008). Dazu wählen wir zunächst im Kontextmenü die gewünschte Aktion aus:

BackUp in SQL Server Management Studio

Dann startet sich ein Popup-Fenster, in dem wir die gewünschten Einstellungen durchführen, aber nicht auf OK klicken.

Script Button oben

Im oberen Bereich befindet sich eine Script-Button. Wenn man auf diesen klickt, wird das zugehörige XMLA erstellt:

Backup-XMLA

Dies kann man jetzt sogar direkt im SQL Server Management Studio ausführen (Execute!).

Bei Erfolg liefert die Ausführung folgendes Ergebnis:

<return xmlns=“urn:schemas-microsoft-com:xml-analysis“>
<root xmlns=“urn:schemas-microsoft-com:xml-analysis:empty“ />
</return>

Das Problem (auf das wir später noch zurückkommen werden) ist, dass auch bei einem Fehler in der Regel kein Fehler geschmissen wird (bei Analysis Services 2008 kommen manchmal Fehler vor), sondern ein XML zurückgegeben wird, in der die XML-Knoten Exception oder Error oder ähnliches auftauchen. Wie gesagt, dazu später mehr.

Wie können wir nun ein solches XMLA automatisiert (zeitgesteuert) aufrufen?

Die einfachste Möglichkeit ist über den SQL Server Agent. Wir legen dazu einen neuen Job an. Der erste Schritt des neuen Jobs sieht so aus:

Step zum Backup via XMLA

Wie man in obigem Screen Shot sieht, muss als Typ „SQL Server Analysis Services Command“ ausgewählt, als Server dergewünschte SQL Server Analysis Services-Server (hier im Beispiel ssasentsql2008) eingetragen und das XMLA in die große Textbox Command kopiert werden.

Damit kann dieses XMLA im Rahmen eines SQL Server Agent Jobs ausgeführt werden.

Im Log File Viewer kann man nach der Ausführung das ERgebnis der Ausführung wie folgt erkennen:

Log File Ergebnis der Ausführung eines XMLA Befehles

Was ich markiert habe, ist genau das Ergebnis, das wir vorhin auch als Ergebnis bei der Ausführung im SQL Server Management Studio gesehen hatten. Hier erkennt man, dass die Ausführung erfolgreich war, weil das Ergebnis „empty“ ist. Natürlich wird der Job als erfolgreich beendet angezeigt.

Das Problem hierbei ist, dass bei Fehlern bei der Ausführung der Job ebenfalls als erfolgreich abgeschlossen angezeigt wird und die Fehlermeldung nur an dieser Stelle im Log sichtbar ist.

Ausgabe, wenn das XMLA einen Fehler geliefert hat

Man erkennt deutlich, dass der Step als erfolgreich markiert ist, aber offensichtlich nicht erfolgreich durchgeführt wurde. Die Fehlermeldung habe ich markiert. (In dem Beispiel handelte es sich um das Aufbereiten einer nicht existenten Datenbank)

Dies ist natürlich sehr ungünstig, da Administratoren auf das Fehlschlagen eines Jobs reagieren können, aber nicht auf irgendwo enthaltene Fehlermeldungen. Deswegen empfehle ich, in produktiven Umgebungen das XMLA nicht direkt im SQL Serevr Agent auszuführen, sondern, wie gleich beschrieben im SSIS. Im SQL Server Agent 2008 scheint dieses Problem behoben zu sein. Wenn man obigen fehlerhaften Job im SQL Server Agent 2008 anlegt (sogar auch wenn man als Ziel des XMLA sogar einen 2005er Analysis Services wählt), wird der Fehler im SQL Server Agent erkannt und sinnvoll protokolliert, wie man in folgendem Screen Shot sehen kann:

Fehler in XMLA korrekt erkannt

Nun aber zu einer anderen Möglichkeit, das XMLA auszuführen, als Integration Services Package im SSIS:

Im Control Flow gibt es dort eine Task mit Titel Analysis Services Execute DDL Task. Diese benötigt eine Cube-Connection und das zu automatisierende XMLA. Das XMLA kann dabei direkt eingegeben oder aus einer Variable oder aus einem File ausgelesen werden. Letzters wird bei großen XMLAs benötigt, da sonst die Größenbeschränkung auf ca. 4000 Zeichen besteht.

Bild

Unter diesem Link habe ich ein einfaches Paket zum Download bereit gestellt, dass nach Eingabe einiger Variablen das XMLA automatisch erstellt (über Expressions der Execute SSAS DDL Task) und dann ausführt. Die Variablen sind im beigefügten Config-File enthalten, so dass Sie das Paket auch einfach über die Anpassungen an dieser Config-Datei steuern können.

Als Variablen werden verwendet:

  • CubeDatenbank: Die SSAS-Datenbank, die gesichert werden soll.
  • CubeServer: Der Name des SSAS-Servers, auf dem sich die zu sichernde Datenbank befindet.
  • Dateiname: Name der zu erstellenden Datei
  • MitKompression: Soll die Datei komprimiert werden? (im Config-File 0 für nein, 1 für ja eintragen)
  • mitUeberschreiben: Soll evtl. eine bereiots existierende Datei überschrieben werden? (im Config-File 0 für nein, 1 für ja eintragen)
  • BackupPasswort: Geben Sie das an, wenn Sie Ihr Backup verschlüsseln wollen, sonst leer lassen (dann wird nicht mit leerem Passwort verschlüsselt 🙂 )

Variablen fürs SSIS Backup

Ganz analog können alle XMLAs mit SSIS ausgeführt werden.

Bug im Visual Studio 2008: SSIS Variablenänderung bei F5-Start Debug

Im Visual Studio 2008 tritt bei SSIS-Paketen ein kleiner – aber gemeiner – Bug auf.

Ändert man eine Variable und drückt, während man noch in der Variable steht, F5, um die Debug-Ausführung des Paktes zu starten, dann startet das Paket noch mit dem alten Wert der Variable. Wie man im Screenshot schön sieht, ist die Paketvariable auf „Neuer Text“ gesetzt worden, allerdings wird in der Messagebox noch der alte Inhalt „Hallo Welt“ angezeigt.

Variableninhalt falsch angezeigt bei F5

Das Phänomen tritt auf, egal ob man F5 drückt oder den grünen Button Grüner Pfeil betätigt, solange man zu diesem Zeitpunkt in der Spalte „Value“ der Variable steht. Sobald man diese Spalte verlassen hat (z.B. durch Anklicken der Spalte „Data Type“) funktioniert es korrekt.

Rollierendes Log-File in SSIS-Paketen

Bei Integration Services-Paketen ist es einfach, ein Logfile einzustellen (SSIS>Logging…), das gewünschte Events (Errors, Warnings, Information, …) protokolliert. Im Standard wird ein solches Logfile bei jedem Lauf des Pakets größer, da immer neue Zeilen angefügt werden. Dies hat Nachteile, da es Eingriffe durch einen Administrator erfordert.

In der Regel verwende ich deshalb in meinen Paketen Logfiles, die den Wochentag im Logfilenamen haben (datei_1.log für Montag, datei_2.log für Dienstag, …), so dass die Logfiles nach 7 Tagen überschrieben werden, so dass eine ausreichende Anzahl von Logfiles für die Fehlersuche zur Verfügung stehen, der belegte Plattenplatz aber nicht ständig steigt.

Dazu gehe ich wie folgt vor:

Es werden zwei Variablen definiert, deren Werte über Konfigurationsdateien (*.dtsconfig via SSIS>Package Configurations…) eingestellt werden:

  • setting_LogPath: Der Pfad, in dem die Logfiles liegen sollen, z.B. „c:temp“
  • setting_LogFileName: Name des Logfiles, z.B. testlog

Dann gibt es eine Variable „LogFile“, die über eine Expression berechnet wird:

Variable LogFile - einige Properties

@[User::setting_LogPath] + ((right(@[User::setting_LogPath],1)==“\“) ? „“ : „\“) + @[User::setting_LogFileName] + „_“ + (DT_WSTR, 1) DATEPART( „dw“, GETDATE() ) + „.log“

Einige Bemerkungen zu dieser Berechnung:

  • Falls der Pfad nicht auf endet, wird ein an den Pfad angefügt. Achtung muss wie in C# zu \ „escapet“ werden.
  • Der Wochentag wird über Datepart ermittelt. Statt GetDate() könnte auch das Startdatum des Pakets (System-Variable @[System::StartTime]) verwendet werden
  • Im deutschen SSIS heißen die Variablen @[Benutzer:: statt @[User::

Im Connection Manager wird dann eine File-Connection angelegt:

SSISLogFile im Connection Manager

Über die Expressions dieser Connection wird der Dateiname auf die Variable „Logfile“ gesetzt:

Eigenschaften der SSISLogFile Connection

Somit wird jeden Wochentag ein anderes Logfile verwendet. Da dabei immer angefügt wird, muss noch das morgige Logfile gelöscht werden.

Dazu wird noch eine Variable „LogFile2Delete“ angelegt, die über folgende Expression – analog zu LogFile – berechnet wird:

@[User::setting_LogPath] + ((right(@[User::setting_LogPath],1)==“\“) ? „“ : „\“) + @[User::setting_LogFileName] + „_“ + (DT_WSTR, 1) DATEPART( „dw“, dateadd(„day“, 1, GETDATE()) ) + „.log“

Über ein FileSystemTask wird diese Datei dann zum Ende des Pakets gelöscht. Falls die Datei nicht existiert, macht das nichts – die FileSystemTask schmeißt keinen Fehler.

Lösche morgiges Logfile über eine FileSystemTask

Einstellungen der FilesystemTask

SSIS – binäre Collation bei der Suche

In einem früheren Blog-Eintrag habe ich beschrieben, wie die Suche im SQL Server Integration Services (im Standard) abhängig von der Groß- und Kleinschreibung ist. Das liegt daran, dass der Cache im Integration Services binär angelegt werden, so dass „Martin“, „martin“ und „MARTIN“ 3 unterschiedliche ELemente sind, wohingegen bei einem SELECT DISTINCT Vorname FROM Personen dies (in der Standard-Collation) nur als einen Wert zurückliefern würde.

Dieses Phänomen tritt aber natürlich nicht nur bei Groß- und Kleinschreibung auf, sondern bei allen Strings, die laut Collation gleich sind.

Damit führt das Standard-Szenario nicht zum gewünschten Ergebnis, wenn ich zum Beispiel Vornamen in eine Dimensionstabelle umsetzen will:

Paket-Beispiel Vornamen auf IDs umsetzen

Laden der Dimensionen (Vorname)Beispiel für Faktenimport

 

In meinem Beispiel habe ich die Personen-Tabelle wie folgt gefüllt:

Inhalt der Tabelle Personen

Auf der Nachnamen-Spalte habe ich die Standard-Collation (SQL_Latin1_General_CP1_CI_AS) verwendet, die Vornamen-Spalte habe ich so eingestellt, dass auch Akzente ignoriert werden (Collation Latin1_General_CI_AI) (OK, das ist ein bisschen gestellt, aber in der Praxis hatte ich einen ähnlichen Fall).

Somit liefert select distinct vorname from person:

SELECT DISTINCT Vorname FROM Person

und select distinct nachname from person:

SLECT DiSTINCT Nachname FROM Person

Man beachte, dass bei der Standard-Collation ß wie ss behandelt wird.

Nun ist klar, dass beim Lookup nach Vorname im Faktenimport nicht alle Vornamen einen Treffer liefern und somit das ETL einen Fehler schmeißt.

Außerdem ist es vermutlich nicht sinnvoll, dass Michel und Michél gleiche Vornamen sind. Deswegen beitet es sich in diesem Fall an, dass alle unterschiedlichen Schreibweisen der Vornamen auch unterschiedliche Dimensionselemente werden. Dazu kann man im Dimensions-SELECT statt „SELECT DISTINCT Vorname FROM Personen“ besser „SELECT DISTINCT vorname COLLATE Latin1_General_BIN as Vorname from person“ verwendet. Damit instruiert man den SQL Server die binäre Collation zu verwenden und somit sind beim SQL Server selbst die einzelnen Vornamen nicht mehr gleich – und schon geht’s.

Eine andere Alternative wäre gewesen, über eine Funktion Strings, die gleich sein sollen, in einen eindeutigen String zu überführen (wie in meinem letzten Blog-Eintrag mit UPPER()). Bei Akzenten ist das aber nicht so einfach.

Eine weitere Alternative ist, den Lookup nicht mit vollem Cache zu machen, was aber negative Auswirkungen auf die Performance hat. Also könnte man auch zuerst einen Lookup mit vollem Cache und im Fehlerfall einen nachgelagerten Lookup ohne Cache ausführen. Im zweiten Lookup kann man dann über die Collation genau definieren, welche Strings gleich sein sollen und welche nicht.

In einem solchen Fall muss man also auf jeden definieren, welche Strings im DWH als gleich angesehen werden sollen. Über die COLLATION kann man das – wie gezeigt – sehr fein einstellen.

Natürlich sind solche Szenarien selten, da in der Regel nicht beliebige Freitextwerte in einer Dimension vorkommen – aber, wie in meinen Projekten geschehen, ab und zu gibt es dann doch solche Fälle.

Konfigurationsdateien im SSIS – Nutzen und Fallen

In SSIS-Paketen können alle möglichen Einstellungen in Konfigurationsdateien ausgelagert werden. Insbesondere können damit

  • die Eigenschaften von Verbindungen wie ConnectionString, User, Passwort
  • oder Variablen wie Name des zu durchsuchenden Datei-Ordners oder ähnliches

extern konfiguriert werden – ganz analog zur web.config in ASP.NET oder app.config in Windows-Programmen. Theoretisch ist es möglich, viel mehr Attribute als oben genannte in der Konfigurationsdatei zu pflegen – aus Übersichtlichkeitsgründen ist das aber nicht empfohlen.

Dies hat den Sinn, dass das Paket in der Entwicklungs-, Test- und Produktionsumgebung immer gleich ist und nur die Konfigurationsdateien bei einer Installation angepasst werden. Dadurch wird sichergestellt, dass wirklich das getestete Paket in Produktion geht und nicht aus Versehen beim Öffnen im Visual Studio irgendwelche Einstellungen angepasst wurden. Dies ist in meinen Augen – neben vielen anderen Features – ein wesentlicher Vorteil von SSIS gegenüber seinem Vorgänger DTS.

Konfigurationsdateien werden erstellt über SSIS > Package Configurations… :

Erstellen von Konfigurationsdateien

In der Regel werden XML-Konfigurationsdateien verwendet. Dort kann man den Ort der Konfigurationsdatei und die enthaltenen Attribute angeben. Dadurch erzeugt das Visual Studio die entsprechende Konfigurationsdatei. In dieser Konfigurationsdatei sind alle Werte der Attribute gespeichert, so wie sie derzeit (im Verbindungsmanager, Variablenfenster, etc.) definiert sind. Einzige Ausnahme: Falls im Verbindungsmanager ein Passwort verwendet wird, wird in der Konfigurationsdatei zwar dieses Attribut angelegt, aber nicht der Wert übernommen. (erste kleine Falle)

Nun ist die Idee, bei der Installation in der Produktiv-Umgebung das dtsx-Paket unverändert zu lassen und nur die Konfigurationsdatei anzupassen. Beim Starten des Pakets kann man dann die zu verwendende Konfigurationsdatei angeben (z.B. im SQL Server Agent oder beim direkten Aufruf von DTExec) und diese wird beim Ausführen des Pakets verwendet.

Beispiel für die Verwendung einer Konfigurationsdatei im SQL Server Agent

Allerdings gibt es ein paar Fallen, die man umschiffen sollte:

Falle 1: Änderungen an Variablen während der Entwicklung gehen beim Starten des Pakets im Visual Studio verloren:

Wenn man im Visual Studio Eigenschaften ändert, die bereits in die Konfigurationsdatei exportiert wurden, ist diese Änderung wirkungslos. Beim Start des Pakets wird ja die Konfigurationsdatei geladen und dessen Wert verwendet. Eine automatische Aktualisierung der Konfigurationsdatei bei Änderungen im Visual Studio findet (glücklicherweise) nicht statt.

Falle 2: Das Paket versucht beim Start die im Paket definierte Konfigurationsdatei zu laden

Im produktiven Umfeld liegt die Konfigurationsdatei wahrscheinlich nicht am selben Ort (im Dateisystem) wie in der Entwicklungsumgebung. Wenn man nun das Paket (wie unter Idee beschrieben) in der Produktion unter Angabe der Produktions-Konfigurationsdatei startet, so versucht das Paket zunächst die Datei zu laden, deren Pfad im Paket (s. obiger Screenshot) definiert ist. Diese Datei wird in der Regel nicht gefunden werden. Dies wird als Fehler protokolliert – das Paket bricht aber nicht ab. Danach wird die beim Aufruf mitgegebene Konfigurationsdatei geladen. Somit läuft das Paket sauber durch – allerdings ist ein Fehler protokolliert, der Verwirrung stiften kann. Deswegen ist es am sinnvollsten, den Haken bei „Enable package configurations“ (s. Screenshot) wieder zu entfernen. Durch diesen Haken hat man sich somit nur auf einfache Art und Weise eine Konfigurationsdatei erstellen lassen (anstelle sie komplett selbst manuell zu erstellen).

Nun könnte man sich wundern, ob dann trotzdem die beim Aufruf mitgegebene Konfigurationsdatei geladen wird. Die Bedeutung des Hakens ist in meinen Augen nämlich etwas verwirrend. Es besagt nämlich nicht „Dieses Paket hat eine Kofnigurationsdatei mit den dort eingestellten Attributen“. Egal ob der Haken gesetzt ist oder nicht, kann eine Konfigurationsdatei beim Start angegeben werden, die beliebige Attribute verändert, also auch Attribute, die nicht im Visual Studio zum Export in die Konfigurationsdatei markiert worden waren. Der Haken besagt somit, dass „Dieses Paket lädt beim Start automatisch die angegebene Konfigurationsdatei – danach werden die beim Start übergebenen Konfigurationsdateien geladen“.

Dieses Verhalten ist in meinen Augen wenig intuitiv – aber so ist es halt. Dadurch kann man sein eigenes Paket also auch nicht schützen, so dass bestimmte Attribute nicht von außen durch Konfigurationsdateien überschrieben werden können.