Startseite
Impressum
Skills
Referenzen
pfeil Produkte
Links
pfeil Tips
Benutzerverwaltung 1
Benutzerverwaltung 2
Das ideale Paßwort
Datum und SQL-DB
DSL-Probleme
Dr. Watson Teil 1
Dr. Watson Teil 2
DTS Automatisierung
Installation autom.
Kalender und Zeit
Mail Automatisierung
Office Automation
Stack Frame
SQL Server
Strings + Unicode
VB 6 Tips & Tricks
Windows-Uhrzeit

 

Drucker - freundliche Darstellung


  zalando.de - Schuhe und Fashion online

 

Letzte Aktualisierung: 14.11.2015



DTS Automation mit Office

Mit DTS - Paketen kann man Daten spielend leicht von einem Format in ein anderes transformieren. Ob Microsoft Excel oder Access, ob SQL Server, CSV- oder Textdatei: Es sind praktisch keine Grenzen gesetzt. Dabei kann man die Daten auch noch Filtern, Erweitern oder Formatieren. Dieser Artikel beschreibt an einem Beispiel, wie es geht.


DTS - Pakete erstellen

DTS - Pakete aus Visual Basic heraus aufrufen

Globale Variablen im DTS - Paket einsetzen

SQL Server 2005

Buchtips

Links


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.

DTS - Pakete aus Visual Basic heraus aufrufen

Der Folgende Codeschnipsel zeigt grundsätzlich, wie es geht:

Public Sub ExecuteDTSPackage(ByVal strPackageName As String) Dim oPKG As New DTS.Package Dim oStep As DTS.Step On Error GoTo ErrorHandler oPKG.LoadFromSQLServer SRVNAME, , , DTSSQLStgFlag_UseTrustedConnection, _ , , , strPackageName ' Alles im Main Thread ausführen For Each oStep In oPKG.Steps oStep.ExecuteInMainThread = True Next oPKG.Execute oPKG.UnInitialize Set oPKG = Nothing Exit Sub ErrorHandler: Fehlerbehandlung "ExecuteDTSPackage", Err.Number, Err.Description, _ strPackageName End Sub

Wichtig dabei: Der Paket - Name sollte keine Leerzeichen enthalten, sonst funktioniert es nicht.

Wenn auf den Maschinen, auf denen diese VB - Applikation nachher laufen soll, keine SQL Server Client-Tools installiert sind, dann muß man sich die Mühe machen, auch einige DTS - DLLs zu verteilen, denn sonst wird es beim Aufruf dieser Funktion zu Laufzeitfehlern kommen.

Welche DLLs das sind steht bei sqldts.com

Globale Variablen im DTS - Paket einsetzen

Manchmal ist es sinnvoll, globale Variablen innerhalb eines DTS - Pakets einzusetzen. Eine regelmäßig wiederkehrende Aufgabe z.B. könnte jeweils ein Datum als Grundlage haben. Mit diesem Datum werden bestimmte Daten gefiltert vor der Selektion, und eine Ergebnisdatei könnte dieses Datum im Namen beinhalten.

Dafür gibt es in den Paketeigenschaften einen Reiter mit Namen "Gloabale Variablen", in dem man (von Hand) solche globalen Variablen festlegen kann. Der Folgende Dialog erscheint, wenn man im DTS - Designer auf "Paket / Eigenschaften" drückt:

Im gezeigten Beispiel wird eine globale String-Variable mit Namen "GlobalDatum" angelegt, und als Wert bekommt sie das Datum 14.09.2005 zugewiesen (ohne Anführungszeichen, reiner Echt-Inhalt!).

Jetzt soll die Variable auch verwendet werden. In einer Transformationstask habe ich als Datenquelle ein SQL - Statement eingebaut, welches die Daten nach dem Datum filtern soll. Dort sieht man folgenden Code:

Hier sieht man auf der linken Seite den SQL-Befehl zur Datenfilterung. An einer Stelle kann man das freistehende Fragezeichen sehen, und dies ist für den DTS-Designer ein Symbol für eine globale Variable. Drückt man unten auf den Knopf "Parameter", so kann man eine Zuordnung zwischen diesen Fragezeichen und den bislang definierten globalen Variablen vornehmen, denn das rechts zu sehende Fenster erscheint dabei.

Das Statement funktioniert auch ohne die üblichen Begrenzer (Apostroph), die in diesem Fall (String-Filter!) normalerweise im SQL nötig wären.

Auch in ActiveX - Script - Tasks kann man globale Variablen prima einsetzen. Dort gibt es auf der linken Seite einen Browser, mit dem man durch Doppelklick die globalen Variablen ins Script einbinden kann:

SQL Server 2005

SQL Server 2005 bricht mit der Kompatibilität zu SQL Server 2000 in bezug auf die bekannten DTS Tasks, die im Datenbankserver hinterlegt sind. Microsoft bietet einen kostenlosen DTS Designer für SQL Server 2005 zum Download an, mit dem ich aber noch kein DTS-Paket fehlerfrei Öffnen geschweige denn ausführen konnte.

Buchtips

cover


cover


Die Reihe "Das Handbuch" von Microsoft Press ist eine sehr gelungene Serie zu den unterschiedlichen Office-Produkten. Für den Anwender, ob Anfänger oder Profi, bleibt keine Frage offen, wenngleich für den Programmierer die Themen VBA und Automation nicht detailliert genug behandelt werden. Trotzdem halte ich es für ein Muß für jeden, der professionell mit Office - Produkten arbeitet.

Informationen zum Excel-Handbuch
Informationen zum Word-Handbuch

cover


"Microsoft SQL Server 2000 Data Transformation Services DTS" von Timothy Peterson

Meiner Meinung nach das beste Buch auf dem Markt, um SQL Server DTS zu erklären. Geeignet sowohl für Einsteiger wie auch Fortgeschrittene, als Referenz- und Nachschlagewerk sehr sinnvoll.

Informationen zum Buch


cover


"SQL Server DTS" von Jim Samuelson, M. Santos, S. Miller, S. Hughes, B. Sullivan

Eine Einführung für Neulinge, die lernen möchten, was es mit DTS - Paketen auf sich hat, und was man damit alles bewerkstelligen kann.

Informationen zum Buch

Links (meine DTS - Favoriten)

SQL DTS von Darren Green und Allan Mitchell, eine sehr gute Seite zum Starten und als Referenz (englisch)

Execute a package from Visual Basic (VB) von Darren Green (englisch), wie man DTS - Pakete aus Visual Basic aufrufen kann.

DTS and the Data Warehouse von Itzik Ben-Gan (englisch). Lassen Sie sich von der Überschrift nicht täuschen - Es ist meiner Meinung nach der einzig auffindbare Artikel, der brauchbar über data driven tasks in DTS Auskunft gibt ("Datengesteuerte Abfragen")

How to use an ActiveX script task to import data into a new Excel file


Support, Feedback, Anregungen

Alles verstanden ? Oder noch Fragen ? Wir freuen uns über Feedback zu den Tips, auch über Verbesserungsvorschläge. Schreiben Sie an support@a-m-i.de.