This blog post has also been published in English.
Nachdem wir nun reichlich Wege gesehen haben, wie sich mittels Biml SSIS Pakete zur Beladung und Generierung von Tabellen erzeugen lassen, versuchen wir nun die Lücke zu schliessen, wie Biml uns unterstützen kann auch die Ausführung von Stored Procedures und anderen Tasks zu steuern. Hierzu nutzen wir erneut ein Metadaten-Modell, jedoch mit einem sehr minimalistischen Ansatz. Wir nutzen ihn in dieser bzw. ähnlichen Formen jedoch in diversen Projekten – weil er durch eben diesen Ansatz auch extrem einfach zu pflegen ist.
Zunächst legen wir eine neue Datenbank namens “MyBiml_SimplisticMetaData” an und erzeugen in dieser zwei Tabellen sowie eine Sicht und befüllen diese mit einigen Beispieldaten:
USE [MyBiml_SimplisticMetaData] GO CREATE TABLE [dbo].[MyBimlMeta_Connections]( [Connection] [nvarchar](50) NULL, [Type] [nvarchar](50) NULL, [Server] [nvarchar](50) NULL, [Database] [nvarchar](50) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[MyBimlMeta_Packages]( [Package] [nvarchar](50) NULL, [Tier] [int] NULL, [StepName] [nvarchar](50) NULL, [StepType] [nvarchar](50) NULL, [Connection] [nvarchar](50) NULL, [Task] [nvarchar](50) NULL, [Parameter_1] [nvarchar](50) NULL, [Parameter_2] [nvarchar](50) NULL ) ON [PRIMARY] GO CREATE VIEW [dbo].[vMyBimlMeta_Packages] AS SELECT Conn.Connection , Conn.[Database] , Pack.Package , Pack.Tier , Pack.StepName , Pack.StepType , ISNULL(Pack.Task, N'') AS Task , ISNULL(Pack.Parameter_1, N'') AS Parameter_1 , ISNULL(Pack.Parameter_2, N'') AS Parameter_2 FROM dbo.MyBimlMeta_Connections AS Conn INNER JOIN dbo.MyBimlMeta_Packages AS Pack ON Conn.Connection = Pack.Connection GO INSERT INTO [MyBimlMeta_Connections] SELECT 'OLAP','OLAP','MyDWHServer','MyOLAPDB' INSERT INTO [MyBimlMeta_Connections] SELECT 'DWH','OLEDB','MyDWHServer','MyDataWareHouse' INSERT INTO [MyBimlMeta_Packages] SELECT 'JustOneStoredProc',1,'SP 1','SP','DWH','SP_1','100','' INSERT INTO [MyBimlMeta_Packages] SELECT 'FullProcess',1,'SP 1','SP','DWH','SP_1','100','' INSERT INTO [MyBimlMeta_Packages] SELECT 'FullProcess',1,'SP 2','SP','DWH','SP_2','''A'',20','' INSERT INTO [MyBimlMeta_Packages] SELECT 'FullProcess',2,'SP 3','SP','DWH','SP_3','5','' INSERT INTO [MyBimlMeta_Packages] SELECT 'FullProcess',3,'Process SSAS','OLAPDB','OLAP','','',''
Unsere Metadaten setzen sich aus 2 Komponenten zusammen:
1. Connections
Hier definieren wir unsere Datenbankverbindungen mit den folgenden Eigenschaften:
Connection: Der Name, der für die Verbindung genutzt werden soll
Type: Die Art der Connection – unser Biml Code kann hier zwischen OLEDB und OLAP unterscheiden, wobei sich dies natürlich problemlos erweitern lässt
Server: Name des Servers zur Verbindung
Database: Name der Datenbank zur Verbindung
Hieraus erstellen wir uns wiederum mit einem einfachen BimlScript, in welchem wir schlicht über diese Tabelle loopen, die entsprechenden Biml Verbindungen:
<#@ template language="VB" tier="1" #>
<#@ import namespace="System.Data" #>
<# Dim ControlConnectionString as string = "Provider=SQLNCLI11;Server=Localhost;Initial Catalog=MyBiml_SimplisticMetaData;Integrated Security=SSPI;" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<OleDbConnection Name="Control" ConnectionString="<#= ControlConnectionString #>"/>
<# For Each dr As datarow In ExternalDataAccess.GetDataTable( ControlConnectionString, "Select * from MyBimlMeta_Connections").rows
if dr("Type") = "OLEDB" then #>
<OleDbConnection Name="<#=dr("Connection") #>" ConnectionString="Provider=SQLNCLI11;Server=<#= dr("Server") #>;Initial Catalog=<#= dr("Database") #>;Integrated Security=SSPI;"/>
<# elseif dr("Type") = "OLAP" then #>
<AnalysisServicesConnection Name="<#=dr("Connection") #>" Server="<#= dr("Server") #>" ConnectionString="Data Source=<#= dr("Server") #>;Initial Catalog=<#= dr("Database") #>;Provider=MSOLAP.6;Integrated Security=SSPI;Impersonation Level=Impersonate;"/>
<# end if
Next #>
</Connections>
</Biml>
Nun kommt die andere Komponente ins Spiel:
2. Packages
Hier legen wir wiederum fest, welche Tasks in welchem Paket und in welcher Reihenfolge ausgeführt werden sollen:
Package: Name des Pakets – alle Einträge mit gleichem Paketnamen werden somit in einem Paket zusammengefasst
Tier: Reihenfolge des Tasks im Paket – alle Einträge mit gleicher Reihenfolge laufen gleichzeitig ab
StepName: Name des Tasks
StepType: Typ des Tasks – in unserem Beispiel werden das Aufbereiten einer OLAP Datenbank (Typ: OLAPDB) sowie das Ausführen von Stored Procedures (Typ: SP) unterstützt
Connection: Verweis auf einen Eintrag aus den Connection-Metadaten
Task: zum Beispiel Name der SP, die ausgeführt wird
Parameter_1: Optionale Parameter zum Aufruf einer Stored Procedure
Parameter_2: Wird hier nicht verwendet, könnte aber bei anderen Tasks eventuell benötigt werden
Basierend darauf erzeugen wir eine weitere Biml-Datei, welche wie folgt vorgeht:
– Für jeden eindeutigen “Package” Eintrag wird ein Paket angelegt
– Für jeden eindeutigen “Tier” Eintrag in diesem Package wird ein Container angelegt
– Für jeden Eintrag in diesem Tier wird wiederum ein Task angelegt
Die Container laufen nacheinander ab, in sich jedoch parallel. Container 2 wird somit erst gestartet wenn Container 1 abgeschlossen ist, innerhalb von Container 1 laufen jedoch alle Tasks gleichzeitig.
<#@ template language="VB" tier="2" #>
<#@ import namespace="System.Data" #>
<# Dim ControlConnection as AstDbConnectionNode = rootnode.connections("Control")
Dim ControlConnectionString as string = ControlConnection.Connectionstring #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<# For Each Package As datarow In ExternalDataAccess.GetDataTable( ControlConnectionString, "Select distinct Package from [vMyBimlMeta_Packages]").rows #>
<Package Name="<#= Package("Package") #>" ConstraintMode="Linear">
<Tasks>
<# for each tier as datarow in ExternalDataAccess.GetDataTable( ControlConnectionString, "Select distinct tier from [vMyBimlMeta_Packages] where package = '" & Package("Package") & "' order by tier").rows #>
<Container Name="Tier <#= tier(0) #>" ConstraintMode="Parallel">
<Tasks>
<# for each task as datarow in ExternalDataAccess.GetDataTable( ControlConnectionString, "Select * from [vMyBimlMeta_Packages] where package = '" & Package("Package") & "' and tier = " & tier(0)).rows #>
<# if task("StepType") = "SP" then #>
<ExecuteSQL Name="<#= task("StepName") #>" ConnectionName="<#=task("Connection") #>">
<DirectInput>
EXEC <#= task("Task") #> <#= task("Parameter_1") #>
</DirectInput>
</ExecuteSQL>
<# elseif task("StepType") = "OLAPDB" then #>
<AnalysisServicesProcessing Name="<#= task("StepName") #>" ConnectionName="<#= task("Connection") #>">
<ProcessingConfigurations>
<DatabaseProcessingConfiguration DatabaseId="<#= task("Database") #>" ProcessingOption="ProcessFull"/>
</ProcessingConfigurations>
</AnalysisServicesProcessing>
<# end if #>
<# next #>
</Tasks>
</Container>
<# next #>
</Tasks>
</Package>
<# Next #>
</Packages>
</Biml>
Hiermit haben wir eine solide Basis geschaffen, um entsprechende Abläufe in SSIS mittels Metadaten zu modellieren. Weitere Task-Typen (Aufbereiten von Dimensionen oder Partitionen, Backup-Operationen etc.) lassen sich durch simple Codeergänzungen einfach vornehmen.
Haben Sie hierzu Fragen oder Anmerkungen? Wir freuen uns auf Ihren Input unter !
Weitere Informationen zu Biml, einschließlich Terminen und Blog Beiträgen finden Sie auch auf unserer Biml Seite.
Viel Spaß beim Biml’n!