Matrizen importieren mit dem Package Creator

Der folgende Beitrag zeigt eine Möglichkeit, wie der Package Creator von DeltaMaster ETL verwendet werden kann, um Matrizen zu importieren, bei denen Spaltennamen und -anzahl dynamisch sind.

Häufig kommt es vor, dass wir im Projektalltag mit Importdateien konfrontiert werden, die nicht ohne Weiteres in eine relationale Tabelle importiert werden können. Besonders herausfordernd sind Tabellen in Matrixform, die nicht auf andere Weise aus dem Vorsystem exportiert werden können – vor allem, wenn eine solche Tabelle regelmäßig in den SQL Server importiert werden soll, aber die Spalten der Matrix sich von Import zu Import ändern. In diesem Fall verursacht eine manuelle Anpassung einen unverhältnismäßig großen Aufwand. Wir haben eine vollautomatisierte Lösung dafür, die wir hier an einem Beispiel vorstellen wollen.

Problemstellung

Als Beispiel dienen folgende Matrizen, die jeweils den Umsatz eines Jahres nach Produkten beinhalten, inklusive Summenspalte und -zeile:

Abbildung 1: Matrix Umsatz 2021

Abbildung 2: Matrix Umsatz 2022

Die Produkte können sich in jedem Jahr ändern. Das ist kein Problem, da diese in den Zeilen stehen und direkt in eine Tabelle im SQL Server geschrieben werden können. Problematisch ist, dass im nächsten Jahr die Spalten nicht mehr Jan 2021 etc. heißen, sondern Jan 2022 und so weiter. Ein standardmäßiger Import mit einem SSIS-Paket ist hier nicht mehr möglich, da hier die Input- und Output-Spalten fest definiert werden müssten. Für einen vollautomatisierten Import wird demnach eine Tabelle mit dynamischen Spalten sowie ein dynamisches SSIS-Paket benötigt.

Die Lösung für dieses Problem versteckt sich in einer unscheinbaren Konfiguration des sogenannten Package Creators von DeltaMaster ETL. Wird ein Import definiert und das SSIS-Paket zu dem definierten Import aufgebaut, wird auch die Import-Tabelle erstellt, sofern diese noch nicht existiert. Damit ist es kein Problem, wenn die Spaltennamen der Quelle sich ändern, da die Spaltennamen des Ziels gleich mit angepasst werden.

Implementierung

Der erste Schritt ist die Definition des SSIS-Imports in DeltaMaster ETL. Hierbei gibt es noch keine nennenswerten Besonderheiten. Folgende Eingaben sind dazu nötig:

Abbildung 3: Definition Bericht SSIS Packages

Abbildung 4: Definition Bericht Sources

Abbildung 5: Definition Bericht Source Properties

Abbildung 6: Definition Bericht Source Tables

Dieses Paket kann nun mit DeltaMaster ETL erstellt werden:

Abbildung 7: Erfolgsmeldung aus DeltaMaster ETL.exe

Am einfachsten kann mit diesem Paket gearbeitet werden, wenn eine Solution erstellt wird, in die das Paket bei erneuter Ausführung importiert wird. In diesem Fall kann das Paket direkt nach Erstellung getestet werden.

Beim manuellen Erstellen des Pakets entsteht auch eine passende Tabelle. Noch ist aber nichts automatisiert. Die Automatisierung passiert in einem separaten SSIS Paket:

Abbildung 8: Definition SSIS Paket zum Erstellen des dynamischen SSIS Pakets

Zunächst wird die Import-Tabelle gelöscht. Im Anschluss wird ein Betriebssystem-Task ausgeführt, der das Paket mithilfe des Package Creators neu erstellt. Dabei entsteht die Import-Tabelle erneut. Als Basis für die Neuerstellung dient stets die Datei, die sich gerade im Import-Verzeichnis befindet. Folgende Einstellungen müssen dazu im Betriebssystem-Task vorgenommen werden:

Abbildung 9: Definition Betriebssystem-Task

Zum einen muss der Pfad des Package Creators aus dem ETL-Installationsverzeichnis gewählt werden. Zum anderen muss der Befehl „CreatePackage“ mit den Parametern hinterlegt werden.

Am Ende dieses Schritts ist eine Zieltabelle entstanden, deren Spalten sich verändern, und ein SSIS-Paket, das in diese Tabelle schreiben kann. Allerdings ist dies noch keine nachhaltige Lösung, da vorher importierte Daten stets wieder gelöscht werden würden. Auch die Form der Daten ist noch nicht zweckmäßig und muss angepasst werden. Um sowohl eine Archivierung der Daten als auch eine dynamische Anpassung ihrer Struktur zu erreichen, kann eine Prozedur mit dynamischem SQL verwendet werden:

CREATE OR ALTER PROC P_APP_Import_Umsatz
(
@DEBUG bit = 0
)
AS

DECLARE @sqlSelect varchar(max)
DECLARE @sqlInsert varchar(max)
DECLARE @sqlDelete varchar(max)


SET @sqlSelect =
'SELECT
       pr.ProduktID
       ,pe.MonthID
       ,u.Umsatz
FROM
(
        SELECT
               Produkte as ProduktID
               ,MonthBEZ
               ,Umsatz
        FROM [dbo].[T_Import_Umsatz_new] un
        UNPIVOT
        (
               Umsatz for MonthBEZ IN
                      ('+
                      (
                      SELECT
                             STUFF
                             (
                             (
                             SELECT ', ['+[name]+']'
                             FROM sys.all_columns
                             WHERE
                             object_id = OBJECT_ID('[dbo].[T_Import_Umsatz_new]')
                             AND [name] <> 'Produkte'
                             FOR XML PATH('')
                             ),1,2,''
                             )
                      )+')
        ) as unpvt
) u
INNER JOIN
        (
               SELECT DISTINCT
                      dbo.F_BC_DateCODE(''m'',pe.[Periode],0) AS MonthBEZ
                      ,dbo.F_BC_DateID(''m'',pe.[Periode],0) AS MonthID
               FROM [dbo].[T_S_Periode] pe
        ) pe
               ON
                      pe.MonthBEZ = u.MonthBEZ
INNER JOIN
        [dbo].[T_Import_Produkte] pr
               ON
                      pr.ProduktID = u.ProduktID
'
SET @sqlDelete =
'DELETE u
FROM dbo.T_Import_Umsatz u
WHERE EXISTS(
SELECT * FROM
(
'+@sqlSelect+'
)
un WHERE un.MonthID = u.MonthID)
'

SET @sqlInsert =
'INSERT INTO dbo.T_Import_Umsatz(
       ProduktID
       ,MonthID
       ,Umsatz
)
'+@sqlSelect

IF @DEBUG = 0

BEGIN
       exec (@sqlDelete)
       exec (@sqlInsert)
END
ELSE
BEGIN
       exec(@sqlSelect)
       print @sqlDelete
       print @sqlInsert
END

Diese Prozedur sucht alle Spaltennamen aus der Tabelle sys.all_columns und verwendet diese dann in Zusammenhang mit dem UNPIVOT-Operator (mit diesem Befehl kann aus einer Kreuztabelle eine flache Liste erzeugt werden). Außerdem filtert die Prozedur nur relevante Spalten und Zeilen, indem sie diese mit den Stammdaten (Produkte und Periode) vergleicht. Somit werden die unnötigen Summen automatisch entfernt. Das Ergebnis wird in eine Archivtabelle eingefügt, die nur noch aus den drei relevanten Spalten ProduktID, MonthID und Umsatz besteht. Um eine unproblematische Mehrfachausführung zu gewährleisten, wird eine Deltalogik über die MonthID-Spalte angewendet.

Abschließende Hinweise

Die vorgestellte Implementierung erleichtert den Umgang mit Matrizen beim Import. Dabei gibt es zwei Themen, auf die geachtet werden muss: Zum einen kann mit der gezeigten Implementierung immer nur eine Matrix gleichzeitig importiert werden. Dies zu umgehen ist theoretisch möglich, aber etwas aufwendiger. Grundidee ist es, die zu importierenden Dateien einzeln zu behandeln und für jede Datei das SSIS-Paket auszuführen. Zum anderen funktioniert die Logik nur, wenn das erstellte SSIS-Paket nicht direkt vom erstellenden SSIS-Paket aufgerufen wird. Dies muss auch nicht gemacht werden. Zwei Aufrufe in zwei Schritten in einem SQL Server Agent Job sind zum Beispiel vollkommen ausreichend.