Als Beispiel dienen die deutschen Postleitzahlen . Die Deutsche Post vertreibt ein Produkt "Datafactory Postalcode", das alle deutschen Postleitzahlen sowie noch einige Zusatzinformationen (wie zum Beispiel die Kreisgemeindeschlüssel) in einer Textdatei enthält.

Weitere Informationen zu Datafactory Postalcode finden Sie auf der Web-Site der Deutschen Post AG.

Eine ausführliche Dokumentation zum Aufbau der Textdatei finden Sie auf der Datafactory CD.

In der Datei sind alle Informationen (Strassen, Orte, Postleitzahlen, Kreisgemeindeschlüssel, etc.) gespeichert. Die einzelnen Datensätze können über einen Code in den jeweils ersten beiden Positionen einer Zeile identifiziert werden. Die ersten Sätze (markiert durch das Kürzel 'XX' enthalten Prüfdaten, mit denen sich nach dem Import leicht überprüfen lässt, ob alle Daten importiert wurden.

Alle anderen Datensätze sind ebenfalls jeweils durch zwei Buchstaben gekennzeichnet:

GE - Kreisgemeindeschlüssel
OR - Orte
ST - Strassen
usw.

Um die Postleitzahlen in eine SQL Server Datenbank zu importieren sind folgende Schritte notwendig:

Erstellen der Tabellen auf dem SQL Server
(Theoritisch kann dieser Schritt bei der Definition des Imports erfolgen, aber etwas Vorarbeit macht den späteren Umgang mit den Daten einfacher)
Zerlegen der Rohdaten in Einzeldateien (Eine Datei je Zieltabelle)
Festlegen der jeweiligen Datenquelle(Textdatei) und der Zieltabelle und Festlegen der Transformationen
Ausführen des DTS-Pakets

Erstellen der SQL Server Tabellen

Die Dokumentation zu Datafactory Postalcode enthält eine umfangreiche Beschreibung zum Aufbau der einzelnen Tabellen. Falls Sie sich nicht die Mühe machen möchten, die Tabellen selbst zu erstellen, laden Sie doch einfach das fertige Script (CREATEDATAFACTORY.SQL ).

Ich habe die Tabellennamen und Spaltennamen möglichst ähnlich zur Originaldokumentation benannt - einige Änderungen (wie z.B. Ersetzen des Bindestrichs durch einen Unterstrich) wurden jedoch vorgenommen. Außerdem wurden einige Spalten als varchar-Spalten definiert, um Platz in der Datenbank zu sparen und mit den Abfrageergebnissen leichter umgehen zu können.

Zerlegen der Rohdaten-Datei

Die Rohdaten Datei (befindet sich auf der Datafactory CD im Unterverzeichnis Rohdaten) beinhaltet unterschiedliche Satztypen. Da die Sätze jedoch sortiert nach Typ in der Datei abgelegt sind, kann das folgende einfache VBScript verwendet werden, um die Datei zu zerlegen:

Ablauf

Öffnen der Rohdatendatei
Solange das Dateiende noch nicht errreicht ist
Satz einlesen
Falls neuer Satztyp: Neue Ausgabedatei zum Schreiben öffnen
Satz in entsprechende Ausgabedatei schreiben
Aus- und Eingabedateien schliessen

Das Script erzeugt die Ausgabedateien XX.TXT, KG.TXT, OE.TXT, OR.TXT, OT.TXT, PF.TXT, PL.TXT, ST.TXT und GE.TXT.

Dim fso

Dim inputStream

Dim strOutPfad

Dim strInputFile

Dim outputStream

Dim strGelesen

Dim strTyp

Dim strLastTyp

const forWriting = 2

Function Main()

Set outputStream = Nothing

strLastTyp = ""

strInputFile = DTSGlobalVariables("Rohdaten_Datei").Value

strOutPfad = DTSGlobalVariables("Ausgabe_Pfad").Value

Set fso = CreateObject("Scripting.FileSystemObject")

Set inputStream = fso.OpenTextFile(strInputFile)

Do Until inputStream.AtEndOfStream

strGelesen = inputStream.ReadLine

strTyp = UCASE(LEFT(strGelesen, 2))

If strTyp <> strLastTyp Then

If Not outputStream Is Nothing Then

outputStream.Close

Set outputStream = Nothing

End If

Set outputStream = fso.OpenTextFile(strOutPfad & "\" & strTyp & ".TXT", forWriting, true)

End If

outputStream.WriteLine

strGelesen strLastTyp = strTyp

Loop

outputStream.Close

Set outputStream = Nothing

inputStream.Close

Set inputStream = Nothing

Set fso = Nothing

Main = DTSTaskExecResult_Success

End Function

Falls Sie SQL Server 2000 einsetzen können Sie ein fertiges DTS-Paket auch gleich herunterladen.

Import der Textdateien

Nachdem die einzelnen Quelldateien erzeugt wurden, können die Daten mit Hilfe der Data Transformation Services (DTS) importiert werden.

Dazu wird als Datenquelle eine Textdatei (Source) und als Ziel eine SQL Server Tabelle angegeben. Bei der Definition der Textdatei muss nebem dem Dateinamen auch noch das Format und die Spaltenaufteilung definiert werden.

Dateiformat:	Feld fester Breite (Informationen sind in gleich breiten Spalten ausgerichtet)
Dateityp:	OEM (Die Dateien liegen nicht im Windows, sondern im DOS/OEM Zeichensatz vor)
Zeilentrennzeichen:	{CR}{LF}

Beispiel: Festlegen der Eigenschaften für den Import der Textdatei ST.TXT

Ziehen Sie aus der Leiste "Verbindungen" eine Verbindung vom Typ "Text File (Source)" in den Arbeitsbereich des DTS-Designers. Geben Sie als Namen für die "Neue Verbindung" "ST" ein.

Geben Sie im Feld "Dateiname" den Namen der Quelldatei (ST.TXT) ein.

Klicken Sie anschließend auf die Schaltfläche "Eigenschaften", um das Dateiformat festzulegen (s.a. obige Tabelle).

Nachdem Sie diese Angaben gemacht haben, gelangen Sie über die Schaltfläche "Weiter" zur Festlegung der Spaltenpositionen. Dabei hilft die Dokumentation. Wem das zu mühsam ist, der kann unser vorbereitetes DTS-Paket verwenden.

Festlegen der Zieltabelle und Transformationen

Im nächsten Schritt wird die jeweilige Zieltabelle auf dem SQL Server festgelegt und anschließend können die Transformationen definiert werden.

Für einige Spalten ist es sinnvoll die in der Rohdatendatei enthaltenen Leerzeichen zu entfernen (z.B. in Ortsnamen, Strassennamen oder Hausnummerbereichen). Mit dem SQL Server 2000 kann des leicht durch eine sogenannte "Trim String" Transformation erfolgen.

Falls Sie noch den SQL Server 7.0 verwenden, können diese Datenänderungen leicht durch eine "ActiveX Script" Transformation simuliert werden.

Ausführen des Pakets

Nach dem (in etwas Fleißarbeit) alle Transformationen definiert sind, kann der Import erfolgen.

Initialisieren der Tabellen

Falls Sie beabsichtigen, den Import mehrmals zu starten, empfiehlt es sich noch, ein weiteres Task zu definieren, das den Inhalt der Zieltabellen vor dem Import löscht.

Dazu eignet sich ein Task vom Typ "SQL auführen" .

Im Eingabefeld "SQL-Anweisung" werden die entsprechenden "TRUNCATE TABLE"-Anweisungen zum Löschen der Tabelleninhalte eingegeben (s. Abbildung).

Festlegen der Eigenschaften für das Task 'SQL ausführen'

Zum Abschluss muss jetzt noch der Ablauf (Workflow) festgelegt werden. Die einzelnen Transformationsschritte dürfen erst ausgeführt werden, wenn alle Tabellen erfolgreich initialisiert wurden.

Anschließend kann das DTS Paket gespeichert und gestartet werden.

Die folgende Abbildung zeigt das komplette Paket incl. des Workflows im DTS-Designer.

Viel Spaß beim Ausprobieren.

Fragen? Probleme? Bitte Email an sql@itrain.de.

Link-Tipp zum Thema "VB.net": www.eggheadcafe.com/default.asp

Artikel rund um .Net