Eine gut gepflegte Datumsdimension gehört heutzutage zur Standardausstattung in jedem DWH. Analysen über Zeiträume hinweg sind beliebt – doch was wenn diese Zeiträume recht weit in die Vergangenheit gehen? Innerhalb welcher Grenzen können wir uns mit dem SQL Server-Datentyp datetime bewegen und wie verhält sich das SSIS Pendant?
Um diesen Fragen auf den Grund zu gehen stellen wir uns folgende Ausgangsituation vor: unsere Quelltabelle (s.u. TestDateSource) liefert drei Einträge im Quelldatentyp date welche wir über verschiedene Wege mittels eines SSIS Datenfluss in den Zieldatentyp datetime konvertieren und wegschreiben wollen.
Die erste Konvertierung soll beim Selektieren aus der Quelle geschehen, die zweite mittels einer Abgeleiteten Spalte und die dritte über die Standard-Konvertierungskomponente. Der Versuchsaufbau sieht daher wie folgt aus.
Gleich der erste Versuch mittels CONVERT(DATETIME, TestDate) AS TestDatetimeSource beim Selektieren aus der Quelle schlägt fehl. Schuld daran ist der Eintrag “0013-01-01”.
Die Fehlermeldung schreit nach einer Recherche wo genau denn die Grenze für “out-of-range” Fehler beim SQL Server-Datentyp datetime liegt. Hier hilft (wie so oft) ein Blick in die MSDN Library. Der Artikel zum Thema datetime(http://msdn.microsoft.com/de-de/library/ms187819%28v=sql.105%29.aspx) liefert die Information, dass nur Datumswerte “zwischen dem 1. Januar 1753 und dem 31. Dezember 9999” erlaubt sind. Das erklärt den oben auftretenden Fehler.
Bevor wir uns nun mit weiteren Konvertierungen ins Unglück stürzen, könnte eine Recherche sinnvoll sein mit welchen Datumsangaben die Integration Services umgehen können. Der Artikel dazu (http://msdn.microsoft.com/de-de/library/ms141036%28v=sql.105%29.aspx) liefert die Information, dass der SSIS Datentyp DT_DBTIMESTAMP dem SQL Server-Datentyp datetime am ehesten entspricht. Zusätzlich lernen wir: “DT_DBTIMESTAMP wird dem SQL Server-Datentyp datetime mit dem kleineren Bereich vom 1. Januar 1753 bis zum 31. Dezember 9999 zugeordnet”. Bei der Konvertierung der date Spalte mit dem Wert “0013-01-01” mittels Abgeleiteter Spalte oder Konvertierungskomponente sollten wir also in dasselbe Problem laufen:
Tun wir aber nicht! Hier zeigt sich einmal mehr der Unterschied zwischen Theorie und Praxis bzw. die Diskrepanz zwischen der SQL Server-DB Engine und den Integration Services. Beide Konvertierungen klappen problemlos und wir können auch mit einem Datumswert arbeiten der eigentlich “out-of-range” sein müsste. Lediglich das Einfügen in die Zieltabelle schlägt aus verständlichen Gründen fehl. Warum sich die Integration Services so verhalten ist mir derzeit noch unklar – sollte da draußen jemand eine Erklärung haben bitte ich um Erleuchtung!
Anmerkung 1: der SQL Server-Datentyp datetime2 würde an dieser Stelle alle Probleme lösen, da hier weitaus kleinere Werte erlaubt sind (ab 01. Januar 0001; siehe auch den Hinweis auf den SQL-Standard im datetime MSDN Artikel).
Amerkung 2: wer das Beispiel für zu konstruiert hält möge einmal mit den Integration Services auf eine Oracle Datenquelle zugreifen, in der Timestamps wie “0012-01-01 12:00:00.000” nicht unüblich sind (und ohne Frage Datenfehler darstellen). Laut Dokumentation (http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm) sind hier (und jetzt anschnallen!) Werte “from January 1, 4712 BC to December 31, 9999 AD” erlaubt – was man eben so braucht… Dieser Wert lässt sich übrigens auf das Julianische Datum zurückführen, aber das führt jetzt wahrscheinlich zu weit 😉