[Frank85]

Hallo zusammen,

Ich denke ich bin soweit und kann den ersten Prototyp bereitstellen. Anbei findet ihr eine .ispac Datei (bzw. als zip, diese Endung entfernen), welche Ihr auf dem SQL-Server installieren müsst. Nachfolgend eine Anleitung von Microsoft zur Installation von Packages. klick mich

Im Endeffekt ist es nichts anderes, als die Datei zu laden und doppelklicken auf dem SQL-Server, dann kommt schon der Wizard. Ich hoffe ihr kommt damit klar, sonst lasst es mich wissen, dann muss ich es nochmal bebildert schildern.

Wie geht es weiter? Anschließend solltet ihr im "Integration Service Catalog" einen weiteren Zweig haben, der die Packages vom myGekko-Projekt beinhaltet:

  SSIS.PNG (Größe: 8,68 KB / Downloads: 197)

Generell gibt es zwei Parameter, die immer verlangt werden:
ConnectionString: Data Source=SQLVM;Initial Catalog=myGekko;Integrated Security=True;

MasterConnectionString: Data Source=SQLVM;Initial Catalog=master;Integrated Security=True;Application Name=SSIS-myGekko-{F1196109-BE2A-4E35-AA36-ADB7D32258AE}SQLVM.master;

Diese Parameter müssen natürlich auf eure Maschine angepasst werden. Initial Catalog bedeutet "Datenbankname". Data Source ist der Name des Server bzw. der Instanz.

Jetzt habt ihr eine Liste von Paketen - hier kurz zur Erklärung (ich stelle die Pakete der nötigen Abarbeitungsreihenfolge vor):
1) CreateDatabase.dtsx
Damit erzeugt ihr die entsprechende Datenbank auf dem SQL-Server. Die nötigen Schemas und Prozeduren werden automatisch installiert. 
Anbei die Parameter:
pDatabaseName -> myGekko -> sollte später mit dem ConnectionString übereinstimmen
pSchemaNameRAW -> RAW -> in dieses Schema werden die Daten bulk aus den Dateien geladen
pSchemaNameStaging -> STG -> dieses Schema wird benutzt, um die Daten zwischen zu stagen und für die Endlage vorzubereiten

2) FtpDownload.dtsx
Dieses Paket lädt Daten über einen FTP-Server herunter. Da die interne Komponente nicht funktioniert, lade ich die Daten mittels WinSCP-Kommando herunter. Ihr müsst diesen Step nicht nutzen, könnt auch euren eigenen Download implementieren oder nutzt es eh im lokalen Netzwerk, dann ist es egal.
Parameter:
FtpConnectionString -> ftpUrl
FtpServerPassword -> ftpPasswort
FtpServerUserName -> ftpBenutzer
LocalFolder -> C:\download\gekko\ -> wo soll der Download abgelegt werden? 
RemoteFolder -> / -> Ordner auf dem FTP-Server, im Notfall einfach nur ein Slash
WinScpCommand -> C:\Program Files (x86)\WinSCP\winscp.com -> hier wird die WinSCP-Komponente aufgerufen. Der Rest des Kommandos wird aus den Parametern zusammengesetzt. Es erfolgt momentan nur ein Download, kein entfernen der Daten vom Server.

3) TrendyTranslator.dtsx
Ihr müsst auf eurer Maschine den TrendyTranslator installiert haben. Dieser dient dazu, die Trend-Informationen aus dem eigenen Speicherformat in das CSV-Format umzustellen, ansonsten können wir die Daten nicht einlesen.
Parameter:
pDestinationPath -> C:\download\gekko\trend_out\ -> wo sollen die CSV-Dateien abgelegt werden?
pSourcePath -> C:\download\gekko\trend\ -> wo liegen die Trend-Daten aus dem myGekko
pTrendyPath -> C:\Program Files (x86)\myGEKKO\myGEKKO.TrendyTranslator 1.01\MyGekkoTrendyTranslatorBatch.exe

4) CreateCSVSchemaTrend.dtsx
Jetzt kommt die eigentliche Magie. Mit diesem Paket werden die Trend-Dateien analysiert und die zugehörigen RAW-Tabellen werden automatisch angelegt.
Parameter:
pDestinationPath -> C:\download\gekko\trend_out\ -> das ist in der Regel das pDestinationPath-Verzeichnis vom TrendyTranslator Vorgang. Das sind unsere Quelldaten.
pSchemaError -> C:\download\gekko\trend_out\SchemaError\ -> Wo sollen fehlerhafte Dateien abgelegt werden?
pSchemaNameRAW -> RAW -> in welchem Schema sollen die Tabellen angelegt werden?
pSchemaOK -> C:\download\gekko\trend_out\SchemaOK\ -> hier werden die Dateien verschoben, wenn diese erfolgreich als Tabelle angelegt wurden

5) CreateCSVSchemaList.dtsx
Hier gilt das selbe wie für die Trend Informationen, dieses mal für die Listen. Auch hier werden die Dateien analysiert und die entsprechenden RAW-Tabellen angelegt.
Parameter:
pListOK -> C:\download\gekko\list\OK\ -> an diesen Ort werden die erfolgreichen Dateien verschoben
pListPath -> C:\download\gekko\list\ -> Wo liegen die Daten die eingelesen werden sollen?
pSchemaNameRAW -> RAW -> in welchem Schema sollen die Daten angelegt werden? 

6) BulkInsertTrend.dtsx
Hier werden nun die eigentlichen Dateien eingelesen. Der Vorgang für die Analyse und Tabellenanlage und Import ist absichtlich getrennt. Grund dafür sind Performanceaspekte. Ich würde sonst jede Zeile einzeln übertragen müssen, was natürlich recht lange bei der Anzahl an Dateien dauern würde. Daher habe ich es getrennt. Hier wird mittels BULK-Kommando die Datei komplett in die zuvor angelegte Tabelle eingelesen. Gilt natürlich für alle Trend-Dateien.
Parameter:
pErrorPath -> C:\download\gekko\trend_out\ERROR\ -> dort werden die fehlerhaften Dateien abgelegt
pOkPath -> C:\download\gekko\trend_out\OK\ -> hier werden die erfolgreich importierten Dateien abgelegt
pSchemaNameRAW -> RAW -> in welches Schema werden die Daten abglegt? Sollte mit der vorherigen Anlage übereinstimmen
pSchemaOK -> C:\download\gekko\trend_out\SchemaOK\ -> Aus diesem Ordner werden die Daten gelesen und importiert. Sollte mit dem vorherigen Schema-Import (Output-Pfad) übereinstimmen.

7) BulkInsertList.dtsx
Hier gilt das selbe wie bei 6. Auch hier werden die Listeninformationen BULK eingelesen.
Parameter:
pListBulkError -> C:\download\gekko\list\BulkError\ -> dort werden die fehlerhaften Dateien abgelegt
pListBulktImported -> C:\download\gekko\list\BulkImported\ -> hier werden die erfolgreich importierten Dateien abgelegt
pListOK -> C:\download\gekko\list\OK\ -> Quellordner für den Import der Dateien
pSchemaNameRAW -> RAW -> in welches Schema werden die Daten abgelegt? Sollte mit der vorherigen Anlage übereinstimmen

8) RawToStaging.dtsx
Jetzt werden die Daten aus den Tabellen des Schemas (hier: RAW) in die neue Tabellenstruktur (hier: STG) übertragen. Dabei finden Datenkonvertierungen und Korrekturen statt. Auch Duplikate werden rausgerechnet/zusammengefasst. Zuvor sind alle Daten in den RAW-Tabellen in Feldern vom Datentyp "varchar" eingelesen worden. Jetzt werden die Daten in entsprechende Datentypen gewandelt. Dieser Prozess kann einige Zeit in Anspruch nehmen, da vor allem die Duplikatensuche etwas aufwendiger ist.
Parameter: 
pSchemaNameRAW -> RAW -> aus welchem Schema kommen die Daten?
pSchemaNameStaging -> STG -> in welches Schema sollen die Daten abgelegt werden?

9) ToFinalStage.dtsx
Ab hier werden die Daten in die Endfassung gebracht. Ursprünglich war vorgesehen, dass immer nur die fehlenden Daten (also das Delta) hinzugefügt wird. Das war aber leider nicht möglich, da die Dauer einfach ins unermessliche stieg. Daher werden die Tabellen komplett geleert und anschließend wieder neu befüllt. 
Parameter:
pSchemaNameFinalStage -> dbo -> in welchem Schema sollen die finalen Daten vorliegen?
pSchemaNameStaging -> STG -> Quellschema des Stagings

10) CleanUpTables.dtsx
Mit diesem Paket wird am Ende die Datenbank etwas aufgeräumt. Hintergrund ist, wir haben eine Menge an Dateien. Für jede CSV-Datei besitzen wir am Schluss drei Tabellen. Eine davon enthält aber nur die richtigen Daten, so dass wir die Tabellen aus dem Schema RAW und STG entfernen können. Bei mir wird dieser Step zweimal aufgerufen, so dass ich den Parameter "pSchemaNameRAW" einmal mit "RAW" und einmal mit "STG" aufrufe. Wichtig ist auch, dass die Aufräumaktion vor jedem Importdurchgang für beide Schema aufgerufen werden muss, da es sonst zum Abbruch kommt. Irgendwann kann man ggf. nochmal eine Prüfung und ein DROP einbauen, um sich diesen Step zu schenken.
Parameter:
pDatabaseName -> myGekko -> in welcher Datenbank soll aufgeräumt werden?
pSchemaNameRAW -> RAW -> Tabellen welchen Schemas sollen entfernt werden?

Anmerkung: Sollte my-Gekko die Strukturen der CSV-Dateien mal mit einem Update anpassen, so sollte der Import mindestens bis Step 8 (RawToStaging) ohne Fehler weiterlaufen, da alle Tabellen bis dato immer wieder entfernt und neu angelegt werden. Für das FinalStage findet sich dann eine Lösung.

Auf meiner sehr schmal bemessenen Cloud-Lösung läuft ein Durchgang, von Anlage der Datenbank bis zum CleanUp in 8-9 Minuten durch. Dies liegt wie gesagt an der SQL-Server Dimension und vor allem auch an der Anzahl an Dateien. Mein Gekko läuft seit 2015. Hat jemand das Gekko länger am laufen, besitzt er auch mehr Dateien.
Natürlich macht es keinen Sinn, jedes mal die Dateien von 2015 - 2018 komplett einzulesen. Diese werden sich nicht mehr ändern. In diesem Fall sollte man natürlich dem Importer immer nur die Dateien zur Verfügung stellen, die sich geändert haben und importiert werden sollen. Das reduziert den Importvorgang natürlich um einiges. Ausprobiert habe ich es aber immer mit dem kompletten Datenbestand.

Jede CSV-Datei entspricht einer Tabelle im SQL-Server. D.h. für Auswertungen über Grafana müsst ihr euch ggf. Views bauen. Die Tabellen haben soweit möglich alle einen Primärschlüssel, meist das Datum. 
Um eine Trendinformation aus meinem ersten Post des Energiemonitors zu erhalten, legt ihr euch am besten einen View wie den folgenden an:

Code:

drop view [dbo].[EMAX0_ENERGY_v]
go
CREATE VIEW [dbo].[EMAX0_ENERGY_v]
WITH SCHEMABINDING
AS
SELECT [Datum]
     ,[Haus]
     ,[Netz+]
     ,[Netz-]
     ,[Pv]
     ,[Batt+]
     ,[Batt-]
      , NULL as ProgH
      , NULL as ProgHIcon
      , NULL as ProgM
      , NULL as ProgMIcon
 FROM [dbo].[2015 EMAX0_ENERGY]
 union
 SELECT [Datum]
     ,[Haus]
     ,[Netz+]
     ,[Netz-]
     ,[Pv]
     ,[Batt+]
     ,[Batt-]
      , NULL as ProgH
      , NULL as ProgHIcon
      , NULL as ProgM
      , NULL as ProgMIcon
 FROM [dbo].[2016 EMAX0_ENERGY]
 union
 SELECT [Datum]
     ,[Haus]
     ,[Netz+]
     ,[Netz-]
     ,[Pv]
     ,[Batt+]
     ,[Batt-]
      , NULL as ProgH
      , NULL as ProgHIcon
      , NULL as ProgM
      , NULL as ProgMIcon
 FROM [dbo].[2017 EMAX0_ENERGY]
 union
 SELECT [Datum]
     ,[Haus]
     ,[Netz+]
     ,[Netz-]
     ,[Pv]
     ,[Batt+]
     ,[Batt-]
      ,[ProgH]
      ,[ProgHIcon]
      ,[ProgM]
      ,[ProgMIcon]
 FROM [dbo].[2018 EMAX0_ENERGY]
 union
 SELECT [Datum]
     ,[Haus]
     ,[Netz+]
     ,[Netz-]
     ,[Pv]
     ,[Batt+]
     ,[Batt-]
      ,[ProgH]
      ,[ProgHIcon]
      ,[ProgM]
      ,[ProgMIcon]
 FROM [dbo].[2019 EMAX0_ENERGY]

 -- select * from [dbo].[EMAX0_ENERGY_v]

Diesen könnt ihr später in Grafana oder einfach im Management Studio mittels "select * from [dbo].[EMAX0_ENERGY_v]" abrufen und habt die Daten von 2015 bis zum aktuellen Datum in einer Abfrage.
Jetzt kommt es darauf an, was ihr euch auswerten wollt und wie ihr was im Gekko konfiguriert habt. Dementsprechend habt ihr CSV-Dateien/Tabellen mit der entsprechenden Benennung. Die ist glaube ich teilweise unterschiedlich, das weis ich aber nicht 100%ig.

Je nachdem wie gut ihr euch im SQL-Server auskennt, empfehle ich noch zwei Dinge:
1) legt euch einen SQL-Agent Job an, der alle Pakete nacheinander ausführt
2) Im Sql Server Integration Service kann man sich sogenannte Environments anlegen. Parameter aus den Paketen kann man an die Environments binden und mit Werten vorbelegen. Diese Vorbelegung lässt sich später mit einem Klick bei der Agent-Definition (oder dem manuellen Aufruf) direkt zuweisen und spart Konfigurationsaufwand und mindert das Fehlerrisiko, wenn man bspw. Pfade mehrmals tippen müsste.
Kann dazu gerne auch nochmal eine Anleitung posten oder ihr sucht ganz kurz bei Google, da wird man bzgl. diesem Thema auch recht schnell fündig.

So, jetzt erstmal genug von mir. Lasst mich wissen wie ihr klar kommt oder wenn ihr Probleme habt.

Feedback ist auch gerne erwünscht :-D

Viele Grüße
Frank