Einführung
Es gibt vielerlei Gründe dafür ein SSIS-Template-Paket zu erstellen. In der Regel möchte man die Konfiguration über die Vielzahl der Paket in einem Projekt homogen halten. Heterogenität in der Paketkonfiguration erhöht die Fehleranfälligkeit während der Bereitstellungsphase insgesamt. Zwar gibt es die Möglichkeit der Vererbung der Konfiguration für Unterpakete, allerdings führt das für den IS-Entwickler dazu, dass Pakete nicht mehr einzeln zu starten sind, da diesen Paketen die vererbte Konfiguration des Elternpakts fehlt. Diese Einschränkung ist für die Entwicklung nicht hinnehmbar.
Um diese Einschränkung zu umgehen, muss jedes Paket einzeln Konfiguriert werden. Diese Arbeit manuell zu machen erhöht wieder die Fehleeanfälligkeit für die Bereitstellungsphase. So liegt der logische Schluss der Automatisierung recht nahe.
Nun gibt es unzählige Möglichkeiten IS-Pakete automatisiert anzupassen. Eine Möglichkeit wäre die Pakete durch einen XSLT-Prozessor zu jagen, die für die richtig Konfiguration sorgt. Das hätte den Charm, dass man für verschiedene Bereitstelllungszenarien verschiedene XSLT-Stylesheets schreiben könnte, die die jeweilige Konfiguration für das entsprechende Endsystem berücksichtigt. Der Nachteil dieser Variante ist, dass das KnowHow für XSLT vorhanden sein muss. Außerdem geht XSLT nicht leicht von der Hand.
Ein andere Variante wäre ein .NET-Applikation zu entwerfen, welche mitteln des IS-Objektmodell die entsprechenden Pakte anpasst oder generiert. Der Nachteil hierbei wäre die fehlende Flexibilität während der Entwicklung. Schnelle Änderungen setzen eine Ressource mit C#-Kenntnissen und Visual Studio voraus.
Eine ähnlicher aber flexiblerer Ansatz wäre eine Skript-Sprache zu verwenden mit Zugang zur .NET-Welt. Die Möglichkeiten sind auch hier wieder vielfältig. Hier wären zu nennen: Visual Basic, PowerShell und Iron Python. Welche Skript-Sprache man verwendet ist eher eine Geschmackssache. Da ich das Erweitern von IS-Paketen um eine einheitliche Paketkonfiguration eher in der Bereitstellung verorten würde, würde ich PowerShell (wegen der Nähe zur Administration) den Vorzug geben.
Grundgerüst
Die erste Hürde, die es zu meisten gilt ist von recht langweiliger Natur. Standardmäßig können PowerShell-Skripte nicht direkt ausgeführt werden. Wenn man sich die Mächtigkeit der PoweShell bewusst macht, ist das verständlich.
Es gibt zwei Wege dieses Hürde zu nehmen. Zum einen können wir die Sicherheitsmaßnahmen für PowerShell-Skripte herunterfahren. Für eine Produktionsumgebung ist das nicht zu empfehlen, zu Entwicklungszwecken aber legitim. Mit folgendem Kommando wird die Sicherheitseinstellung ausgeschaltet.
Set-ExecutionPolicy Unrestricted
Für eine Produktionsumgebung müsste das Skript signiert werden und eine Vertrauensstellung dem Eigentümer gegenüber eingerichtet werden. Weitere Informationen hierzu finden sich hier:
Der nächste Schritt wäre PowerShell mit SSIS zu verheiraten. Dazu müssen Assemblies des SQL Servers geladen werden. Im Fall des SQL Server 2008 befinden sich diese Dateien im Verzeichnis C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies.
[Reflection.Assembly]::LoadFile(“…\Microsoft.SqlServer.DTSPipelineWrap.dll”)[Reflection.Assembly]::LoadFile(“…\Microsoft.SQLServer.ManagedDTS.dll”)
Nach diesem Schritt stehen einem alle Objekte des SSIS-Objektsmodells zur Verfügung. Der nächste Schritt ist nun sich eine SSIS-Applikationskontext zu besorgen, ein Paket zu erstellen bzw. zu öffnen, Änderungen vorzunehmen und diese wieder abzuspeichern. Hier ein Beispiel für ein Grundgerüst:
# SSIS-Kontext erstellen
$context = new-object Microsoft.SqlServer.Dts.Runtime.Application
# Neues Paket erstellen
$package = new-object Microsoft.SqlServer.Dts.Runtime.Package
# Paket nach eigenen Wünschen anpassen
# Aufräumen
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($package)[System.Runtime.Interopservices.Marshal]::ReleaseComObject($context)
Remove-Variable context
Remove-Variable package
Soll ein bereits vorhandenes Paket geöffnet werden müsste das Grundgerüst entsprechend angepasst werden:
$context.LoadPackage(“C:\TestPackage.dtsx”, $null, $false)
Variablen erstellen und/oder anpassen
Bei der Homogenisierung der Paketkonfiguration werden Variablen benötigt. Zum Beispiel können Variablen dazu verwendet werden bestimmte Connections über Expressions zu Parametrisieren. Hier ein einfaches Beispiel um dem Paket Variablen mit zu geben. Diese Variablen werden später mittels Expressions an eine Connection weitergegeben. Über diese Connection werden dann alle benötigten weiteren Connections und Variablen geladen.
$var = $package.Variables.Add(“AdminDBCatalog”, $false, “Config”, “AdminDB”)
$var = $package.Variables.Add(“AdminDBHostename”, $false, “Config”, “HOSTNAME”)
Paketkonfiguration einschalten
Wie die Connections und die Variablen ist auch die Paketkonfiguration über das Objekt-Modell zu erreichen. Der Einfachheit halber verzichte ich an dieser Stelle darauf zu überprüfen ob die Konfiguration bereits existiert. Auf das grundlegende Prinzip dabei gehe ich bei der Erstellung der Connections im nächsten Abschnitt ein.
Im folgenden erstelle ich eine Paketkonfiguration welche einen Katalog- und Hostnamen aus einer Environmentvariable läd. Später wird eine Connection dann mit den Inhalten der Variablen parametrisiert.
#Sicherstellen, dass die Paket-Konfiguration eingeschaltet ist
$package.EnableConfigurations = $true
# Katalog aus Envrironment-Variable laden
$config = $package.Configurations.Add()
$config.Name = “AdminDBCatalog”
$config.ConfigurationType = 2
$config.ConfigurationString = “AdminDBCatalog”
$config.PackagePath = “\Package.Variables[Config::AdminDBHostname].Properties[Value]”
# Katalog aus Envrironment-Variable laden
$config = $package.Configurations.Add()
$config.Name = “AdminDBHostname”
$config.ConfigurationType = 2
$config.ConfigurationString = “AdminDBHostname”
$config.PackagePath = “\Package.Variables[Config::AdminDBCatalog].Properties[Value]“
Connections erstellen oder anpassen
Wesentlicher Bestandteil einer Connection ist der Connection-String und der Connection-Type. Folgendes Beispiel zeigt die Erstellung einer einfachen OLEDB-Connection:
$conMgr = $package.Connections.Add(“OLEDB”)
$conMgr.Name = “AdminDB Connection”
$conMgr.ConnectionString = “Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdminDB;Data Source=HOSTNAME;Auto Translate=False;”
Den Connection String bekommt man entweder über der Database Explorer des VS, mit dem SSIS-Designer oder von http://www.connectionstrings.com/. Den Connection Type könnt ihr mit folgendem Befehl ermitteln:
$context.ConnectionInfos | select ConnectionType
ConnectionType
————–
ODBC
FLATFILE
OLEDB
MULTIFILE
MSOLAP100
HTTP
FILE
ADO.NET
FTP
ADO
MULTIFLATFILE
EXCEL
SQLMOBILE
CACHE
MSMQ
SMTP
WMI
SMOServer
In der Regel wäre es für unser Vorhaben aber besser wenn auf das Vorhandensein der Connection geprüft und diese ggf. angepasst würde. Prüfen kann man diese wie folgt:
$ErrorActionPreference = “SilentlyContinue”
$connectionName = “AdminDB Connection”
$conMgr = $package.Connections.Item($connectionName)
# Im Fehlerfall Connection neu anlegen
if (!$?) {
$conMgr = $package.Connections.Add(“OLEDB”)
$conMgr.Name = $connectionName
}
# Connections String setzen
$conMgr.ConnectionString = “Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdminDB;Data Source=ANDERESOURCE;Auto Translate=False;”
Diese Technik stellt sicher, dass die Connection angelegt wird oder wenn bereits vorhanden, angepasst wird. Im zweiten Schritt wird die Connection so mit Expressions parametrisiert, dass die Connection dynamisch von den beiden Enronmentvariablen AdminDBCatalog und AdminDBHost abhängt.
# Connection an die Paketkonfiguration koppeln
$conMgr.SetExpression(“InitialCatalog”, “@[Config::AdminDBCatalog]“)
$conMgr.SetExpression(“ServerName”, “@[Config::AdminDBHostname]“)
Speichern des Pakets
Gespeichert werden kann das Paket mit folgendem Kommando:
$context.SaveToXml(“C:\TestPaket.dtsx”, $null)
Fazit und Ausblick
Im weiteren Verlauf wäre es wohl Ratsam weitere Fehler abzufangen. Des weiteren müssten weitere Variablen und Connections hinzugefügt werden, die man über eine Konfigurationsdatenbank konfigurieren will. Zum Schluss müsste noch die Paketkonfiguration angepasst werden, so dass weitere Inhalte aus der Konfigurationsdatenbank in Variablen geladen werden.
Wie jedoch zu sehen ist kann man mit PoweShell-Skripten und relativ wenigen Zeilen einen kleinen IS-Paket-Generator schreiben der auch noch leicht zu verstehen und zu erweitern ist.
Die Arbeit für solch ein Skript hat man genau einmal. Sich bei jeder Änderung der Konfiguration oder der Implementierung neuer Pakete durch die Dialoge der Paketkonfiguration zu klicken ist recht mühsam, Fehleranfällig und zeitverschwenderisch.
