DTS - Pakete erstellen
DTS heißt ausgeschrieben "Data Transformation Services", es gehört zu den Client - Tools des MS SQL Server.
Mit dem sogenannten Enterprise Manager, dem wichtigsten Verwaltungstool von SQL Server, kann man sogenannte DTS -
Pakete anlegen; dabei handelt es sich um Pakete, welche eine oder mehrere Anweisungen enthalten, die man
mit einer grafischen Schnittstelle erstellen kann.
Aufgabe eines DTS - Pakets ist das Transformieren von Daten jeglicher Art, wozu auch das Erstellen oder Befüllen
von Dateien gehört. Dieser Artikel beschreibt nicht die Grundlagen von DTS, dazu wird auf die gesonderte Literatur
verwiesen (siehe Buchtips). Außerdem verweise ich auf die hervorragende Seite www.sqldts.com, die sehr viele
nützliche Tips bietet (aber ebenfalls keine Grundlagen vermittelt).
Im Folgenden stelle ich anhand eines Beispiels dar, wie man eine ganz alltägliche Aufgabe mit einem DTS - Paket lösen kann:
Anhand von vorhandenen Daten im SQL Server soll eine Berechnung durchgeführt werden, und das Ergebnis der Berechnung
soll in eine neue Microsoft Excel - Datei geschrieben werden, außerdem soll die Excel - Datei ansprechend formatiert werden.
Diese Aufgabe muß in verschiedene Schritte unterteilt werden, und ich erkläre im Verlauf auch, warum. Die ersten beiden
Schritte können gleichzeitig ausgeführt werden: Das Erzeugen der neuen Excel - Datei, sowie die Aufbereitung der einzufügenden
Daten. Danach wird in einem dritten Schritt die Excel - Datei befüllt, und am Ende wird die Excel - Tabelle noch ansprechend
formatiert.
Erstellen der neuen Excel - Datei
[Vorwort: Der im Folgenden aufgeführte Code funktionierte auf einem Rechner einmal nicht, daraufhin
hatte ich eine Frage in Experts-Exchange aufgemacht.
Im Microsoft-Artikel "How to use an ActiveX script task to import data into a new Excel file" steht wohl ebenfalls ein Muster, wie es geht, ich habe es noch nicht geprüft.]
In meinem Beispiel ist der Name der zu erstellenden Excel - Datei statisch fest vorgegeben. Wenn er dynamisch sein soll (also zur Laufzeit ermittelt),
so muß eine Technik wie z.B. globale Paketvariablen eingesetzt werden (siehe Abschnitt "globale Variablen").
Zur Sicherheit wird eine Excel - Datei, die möglicherweise am gleichen Ort unter gleichem Namen bereits existiert,
vorher gleich gelöscht. Diese Aufgabe bewerkstelligt man mit einer sogenannten "ActiveX - Scripttask", welches man aus der Auswahl
von DTS - Elementen auswählen kann. In dieser Scripttask stellt man die Sprache auf "VB - Script", und gibt folgenden Code ein:
'************************************************************************
' Visual Basic-ActiveX-Skript
'************************************************************************
Option Explicit
Function Main()
Dim oFSO
Dim catDB
Dim catTBL
Dim strFilename
strFilename = "C:\Statistik.xls"
' FSO - Objekt erstellen
Set oFSO = CreateObject("Scripting.FileSystemObject")
' Test, ob Datei bereits existiert. Gegebenenfalls löschen.
If oFSO.FileExists(strFilename) Then
oFSO.DeleteFile strFilename
End If
' FSO - Objekt aufräumen
Set oFSO = Nothing
Set catDB = CreateObject("ADOX.Catalog")
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Extended " _
& "Properties=""Excel 8.0"";Mode=Share Deny None;Data Source=" & strFilename
Set catTBL = CreateObject("ADOX.Table")
With catTBL
.Name = "Statistik" ' Name des Excel - Arbeitsblattes
Set .ParentCatalog = catDB
With .Columns
.Append "Menge", 3 ' adInteger
.Append "Bezeichnung", 202 ' adVarWChar
.Append "Gesamt", 3 ' adInteger
.Append "Datum", 7 ' adDate
.Append "Quote", 5 ' adDouble
End With
End With
catDB.Tables.Append catTBL
Set catTBL = Nothing
Set catDB = Nothing
Main = DTSTaskExecResult_Success
End Function
Das Skript ist recht einfach zu verstehen, trotzdem noch einige Anmerkungen dazu.
Für die Dateioperationen (Test auf Existenz, Löschen) wird ein FileSystemObject - Objekt verwendet, weil es so einfach
in der Handhabung ist.
Die Excel - Datei selbst wird über ADO erzeugt, mithilfe des Microsoft - Jet - Providers. Dies hat einen bestimmten
Grund: auf diese Art und Weise kann man Excel genau wie eine Datenbank behandeln. Man definiert und erzeugt eine Tabelle
(entspricht dem Arbeitsblatt), und darin definiert man Spalten sowie deren Datentyp. Dies ist der einzige Weg, den ich
herausgefunden habe, um eine Eigenschaft von Excel zu überwinden, die ich selbst als Bug ansehe: Was immer ich auf konvetionellem
Wege in die Felder einfüge, wird als "Text" betrachtet, und selbst wenn die Spalte vorher anders formatiert worden ist, wird sonst
beim Befüllen stets ein Hochkomma (') vorne angefügt, um das Feld zwingend zu einem "Text" zu machen.
Auf dem Weg, der hier beschrieben wird, wird dieses Problem vermieden, und das Excel - Arbeitsblatt behandelt die Daten
exakt mit dem Typ, der in der Spaltendefinition vorgegeben wird.
Man sieht, daß ich die Datentyp - Konstanten (adInteger, adDouble, ...) in die entsprechenden numerischen Werte auflösen mußte,
weil DTS - ActiveX - Skripts diese Konstantenbezeichner nicht kennen. Hier hilft im Zweifel das MSDN weiter.
Vorbereiten der Daten, und Umfüllen
Ich gehe im Folgenden davon aus, daß schlicht eine Kopie einer SQL Server - Tabelle in das Excel - Arbeitsblatt eingefügt werden soll.
Dann reicht es aus, eine einfache Task "Daten Transformieren" zwischen ein SQL - Server - Objekt und ein Excel - Objekt zu schalten.
Sind die Daten das Ergebnis einer komplexeren Abfrage oder einer stored procedure, so kann man diesen Aufruf ebenfalls in den Transformations-
Eigenschaften hinterlegen (unter "Quelle").
Um das Excel - Objekt richtig im DTS - Paket einzurichten, muß bereits eine Excel - Datei existieren, deren Dateiname und Pfad exakt mit
der später zu erstellenden Excel - Datei übereinstimmt, und deren Datenspalten bereits fertig eingerichtet sind.
Dies ist aber kein Problem, nachdem wir den eben beschriebenen Schritt durchgeführt haben: Man geht mit der rechten Maustaste auf das
eben erzeugte Activex - Skriptobjekt und sagt "Schritt ausführen". Dann hat man bereits die gewünschte Datei vorliegen, ohne Dateninhalte zwar,
doch das ist hier noch nicht nötig. Auf dem Weg hat man seine ActiveX - Skripttask auch gleich getestet.
Wenn das Excel - Objekt also richtig eingestellt ist, so kann man die Transformationen konfigurieren.
Im Transformationsobjekt geht man zunächst auf "Ziel", und dort wählt man in der Ausklapp-Box den Namen,
den man bereits im vorherigen Schritt für das Arbeitsblatt festgelegt hatte. Dies erscheint hier automatisch
zur Auswahl, weil die Datei ja bereits vorhanden ist. Auch die Datentypen werden an dieser Stelle schon richtig
angezeigt; das ist wichtig! Werden sie hier falsch angezeigt, dann sind nach Ausführung des Pakets wieder nur
Texte in den Zellen.
Bei den Transformationen reicht es im einfachsten Fall, von den Spalten aus der SQL Server - Tabelle direkt einen
Pfeil zur Spalte der Excel - Tabelle zu machen. Stimmen die Namen überein, dann schlägt DTS hier schon richtig alle
Pfeile korrekt vor, denn es versucht, so gut wie möglich die Arbeit atomatisch zu erledigen. Ansonsten reicht es meist,
einen neuen Pfeil zu erschaffen (mit dem Pfeil "Neu"), dann "Copy Column" auszuwählen, und die Quell- und Zielspalte
festzulegen.
Formatieren der Excel - Datei
Eine frisch erzeugt Excel - Datei ist in der Regel nie besonders schön formatiert; alle Spalten haben die gleiche, einheitliche
Spaltenbreite, und auch die Zellenformate sind meistens noch nicht so wie gewünscht.
Daher ist es nach der Transformation ratsam, nochmal eine Extra - Task "Formatierung der Excel - Datei" durchzuführen,
um diese Restarbeiten zu erledigen. Ich verwende dafür ebenfalls wieder eine neue ActiveX - Skripttask, die
ich mit folgendem VB - Skript befülle:
'************************************************************************
' Visual Basic-ActiveX-Skript
'************************************************************************
Function Main()
Dim oExcel
Dim oWorkbook
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open "C:\Statistik.xls"
Set oWorkbook = oExcel.ActiveWorkbook
' Spaltenbreiten und Formate einstellen
oWorkbook.Sheets(1).Range("A:A").ColumnWidth = 9
oWorkbook.Sheets(1).Range("B:B").ColumnWidth = 68
oWorkbook.Sheets(1).Range("C:C").ColumnWidth = 7
oWorkbook.Sheets(1).Range("D:D").ColumnWidth = 16
oWorkbook.Sheets(1).Range("E:E").ColumnWidth = 7
oWorkbook.Sheets(1).Range("F:F").ColumnWidth = 7
oWorkbook.Sheets(1).Range("G:G").ColumnWidth = 11
oWorkbook.Sheets(1).Range("G:G").NumberFormat = "0.00 %"
oWorkbook.Close 1 ' Die 1 sorgt dafür, daß die Frage "Wollen Sie die
' Änderungen speichern" automatisch mit Ja beantwortet wird.
Set oWorkbook = Nothing
Set oExcel = Nothing
Main = DTSTaskExecResult_Success
End Function
In diesem Beispiel werden alle Spaltenbreiten festgelegt, und für eine Spalte (die Quote, eine
Spalte mit dem Datentyp Double) wird als Formatierung eine Prozent - Formatierung mit zwei Nachkommastellen
vorgegeben. Einfache Spalten wie "Integer", "Long" und "Text" werden von Excel automatisch sinnvoll
dargestellt.
Wer sich für die genaueren Feinheiten der Excel - Nachbearbeitung interessiert, sei verwiesen auf meinem Artikel
Office Automation
sowie auf die Buchtips, die unten angegeben sind.
|