itrain-home  
home
 Aktuelle Seite:  knowhow sql transfer dts datafact.asp 
 



 

Import von Textdateien mit DTS (Data Transformation Services)

In diesem kurzen DTS Tutorial erfahren Sie, wie man Textdateien mit fester Spaltenbreite in den SQL Server importieren kann.

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:

  1. 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)
  2. Zerlegen der Rohdaten in Einzeldateien (Eine Datei je Zieltabelle)
  3. Festlegen der jeweiligen Datenquelle(Textdatei) und der Zieltabelle und Festlegen der Transformationen
  4. 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)" Icon 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).

Dateiformat festlegen

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 Spaltenpositionen

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.

Trim-String

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" Icon 'SQL Ausfü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.

Das komplette Paket mit Workflow

Viel Spaß beim Ausprobieren.


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


Valid XHTML 1.0!

Leerraum

Dokument zum Drucken anzeigen
English Pages
Link-Tipp zum Thema "Visual InterDev": www.kamath.com/

Viele Artikel und Komponenten zum Download. Sehr umfangreiche Site