SSIS Paket – Dynamisches Excel Ziel erzeugen

Ich stand vor einigen Tagen wieder einmal vor einem „kleinerem“ Problem:

SQL Server Integration Services (SSIS)
Mit diesem Dienste lassen sich einfach und schnelle Abfolgen von Prozessen eines SQL Servers abbilden, ein Beispiel- Statemant auf eine Datenquelle ausgeführt wird (select * from Tabelle) diese Ausgbae lässt sich einwandfrei im Designer eintragen. Hier ein kleiner Screenshot.

 

Das Problem ist, dass das Ergebnis der Abfrage immer in die gleiche Excel-Datei geschrieben wird und diese somit für eine bsp. monatliche neue Auswertung unnütz ist. Somit habe ich folgende Lösung entwickelt um für jeden Monat eine Datei bereitzustellen.

1. Task ‚SQL‘ erstellen, dieser baut die Struktur der Excel Datei auf und zwar mit folgendem Script:

CREATE TABLE `auswertung` (
`QuartalsID` Long,
`QBegin` DateTime,
`QEnde` DateTime
)

Zu beachten ist hier der ConnectionType der muss auf EXCEL stehen und das SQLStatement s. oben, dieser Task ist nun dafür verantwortlich das bei jedem neuen Durchlauf eine neue Excel-Datei mit den zu füllenden Spalten erstellt wird.

 

2. Nun hängen wir noch einen Datenfluss-Objekt darunter dieser holt die eigentlichen Daten vom Server und schiebt diese in das Excel-Ziel. Um eine Connection anzugeben muss man einen Excel-Verbindungsmanager anlegen, Ihr werdet euch wahrscheinlich wundern, das dieser Manager sehr zwanghaft an eine Excel-Datei halten möchte, gebt Ihm diese am Anfang mit, ansonst könnt Ihr keinen Manager erstellen, im Nachgang ist es wichtig folgende Eigenschaften richitg auszuwählen:

2. a) „Rechtsklick“ –> Eingenschaften eures ConncectionManagers und stellt dort ersteinmal die Expression auf dass Attribute „ExcelFilePath“ so ein(E: ist mein LW Pfad, Ihr könnt euch gern auch eine Variable deklarieren, und diese angeben):

„E:\auswertung_“ +  (DT_WSTR,30)( DAY( GETDATE()))  + „_“ +  (DT_WSTR,30)(MONTH( GETDATE())) + „_“ + (DT_WSTR,30)(YEAR(GETDATE())) +  „.xls“

Dieses Command lautet wie folgt: name_DAY_MONTH_YEAR

 

Nicht zu vergessen beim Excel-Ziel muss der gerade neu angelegte Excel-Verbindungsmanager angeben werden und das CREATE TABLE Statement auf diese Verbindung ausgeführt werden, nun wird der Name der Tabelle = Arbeitsmappen.Name vergeben und die Spalten, nur wenn das passiert kann eine Zuweisung der Daten aus der Datenbankquelle in die Excel Datei automatisch stattfinden, achtet darauf das die Spaltennamen immer gleich sind, habt Ihr Probleme mit Konvertierungen der Datentypen, dann nutzt bitte einen Datenkonvertierungs-Objekt, dieses erläutere ich später einmal in einem gesonderten Blog.

So erster größer Schritt ist getan, nun geht es weiter…

Hier der Datenfluss-Task als Bild (OLDB-Connection muss je nach euren Server/User/PW konfiguriert werden):

 

3. Nun gehen wir zur Ablaufsteuerung zurück, hier konfigurieren wir nun unser Mail Task, der Mail Task hat die gängigen Eigenschaften eines Mail-Objektes nur weis das liebe Mail-Objekt nicht das wir jeden Monat einen anderen Datinamen haben der hinzugefügt werden muss, kein Problem dies lässt sich ganz einfach lösen:

Aufruf Einstellungen dann auf Ausdrücke und dort fügen wir 2 Expressions hinzu:

  • DelayValidation = True (gibt an, ob die Überprüfung des auszuführenden Objektes zur Lauzeit verzögert wird)
  • FileAttachments = „E:\auswertung_“ +  (DT_WSTR,30)( DAY( GETDATE()))  + „_“ +  (DT_WSTR,30)(MONTH( GETDATE())) + „_“ + (DT_WSTR,30)(YEAR(GETDATE())) +  „.xls“

Das Mail-Objekt schaut nun bei jedem neuen Durchgang, wie diese Excel Datei heist und kann sie ganz Artig dem Mail-Objekt beifügen.

So das wars, somit könnte man dieses Package an einen SQL Server Agent hängen und einen neuen Auftrag hinzufügen, der nun im am ersten des Monats eine Auswertung per Email versenden und die Excel Datei befügt.

Schick oder 🙂