SQL Server & ASP .NET Blog

Interessantes und Wissenswertes

Konfiguration von SSIS-Paketen in SQL Server “Denali”

Wie bereits berichtet gibt es für den SQL Server “Denali” eine neue Projektart für SSIS-Projekte. Bisher wurden Pakete immer einzeln bereitgestellt und es gab dabei die Möglichkeit diese im Dateisystem oder in der msdb zu speichern. Konfigurieren konnte man die Pakete über Variablen, die per XML-Datei aus einer Datenbank oder per Umgebungsvariablen gesetzt wurden.

SSIS in SQL Server “Denali” bringt mit dem Project Deployment die SSISDB, Environments und Parameter ins Spiel. Dazu kurze Stichpunkte:

SSISDB – Zentrale Datenbank zum Speichern, Konfigurieren und für das Logging von SSIS Projekten und Paketen
Environment – Container für eine oder mehrere Variablen, die bei der Ausführung von Paketen gesetzt werden können
Parameter – im Grund eine weitere “Variablenart” in SSIS-Paketen, die beim Aufruf eines Paketes oder eines Projektes gesetzt werden kann

Im Grund funktioniert das Zusammenspiel folgendermaßen:

  1. Ein SSIS-Projekt wird angelegt und Parameter werden definiert
  2. Ein Environment mit einigen Variablen wird angelegt
  3. Das SSIS-Projekt wird auf den Server bereitgestellt (direkt aus Visual Studio in die SSISDB)
  4. bei der Ausführung (Execution) wird für das auszuführende Paket die zu verwendete Environment angegeben, die im Paket hinterlegten Parameter werden konfiguriert und das Paket läuft

Die folgende Grafik zeigt dieses Verhalten noch einmal grob:

image

Damit ist es ein Leichtes mehrere Umgebungen mit unterschiedlichen Parameterwerten für bspw. Entwicklungs-, Test- und Produktivumgebungen zu erstellen und während der Ausführung zwischen diesen umzuschalten.

So viel zur Theorie. In der Praxis sieht dieses Vorgehen folgendermaßen aus:

Vorbereitung: Ich habe mir zwei Datenbanken, eine Testdatenbank und eine Produktivdatenbank mit jeweils einer Tabelle erzeugt.

CREATE DATABASE MyDB_Prod;
GO
USE MyDB_Prod
GO
CREATE TABLE myTable
(
  id int primary key identity(1,1),
  value varchar(200)
)
GO

CREATE DATABASE MyDB_Test;
GO
USE MyDB_Test
GO
CREATE TABLE myTable
(
  id int primary key identity(1,1),
  value varchar(200)
)

Ich möchte nun ein SSIS-Paket erstellen, welches je nach Konfiguration die eine oder die andere Datenbank verwendet.

1. Neues SSIS-Projekt erstellen

image

2. Im SSIS-Projekt einen Parameter anlegen

image

Dazu das Fenster Variables & Parameters aus “View => Other Windows” öffnen und einen neuen String-Parameter “DatabaseName” anlegen. Als DefaultValue habe ich hier MyDB_Test angegeben und Required = true. Die Eigenschaft Sensitive on Server würde den Parameterwert nach dem Deployment in der Datenbank verschlüsseln.

3. Datenfluss erstellen und Datenquelle konfigurieren

Für dieses Beispiel habe ich einen simplen Datenfluss angelegt, der mit “SELECT 1 AS One” eine Zeile generiert, zu dieser Zeile den Parameterwert hinzufügt und dann in die Tabelle “myTable” schreibt.

image

Die Datenflussspalte “value” wird dabei mit dem Parameterwert des Parameters “DatabaseName” gefüllt. Diese Parameter sind im Editor für abgeleitete Spalten unter den Variablen zu finden.

image

Das Datenflussziel mappt dann einfach die Spalte value auf die Spalte value in der Zieltabelle.

image

Außerdem muss der Connectionmanager für die Zieldatenbank angepasst werden. Dazu eine Expression für den Connectionmanager anlegen und den InitialCatalog auf den Parameterwert des Parameters “DatabaseName” setzen.

image

4. Environment erstellen

Im SSMS dazu den Knoten Integration Services aufklappen, in der SSISDB einen neuen Ordner “SSISTest” anlegen (Rechtsklick => Create folder…) und danach nach Aktualisierung des Ordners im Unterordner Environments zwei neue Environments anlegen (Rechtsklick => Create Environment…) – Produktiv und Entwicklung

image

Nun sollte es im SSMS ungefähr so aussehen.

image

5. Variablen in den Environments anlegen

Dazu die Eigenschaften einer Environment öffnen (Rechtsklick => Properties) und unter Variables jeweils die Variable “DatabaseName” hinzufügen. In der Environment “Entwicklung” ist der Wert dafür “MyDB_Test”, in der Environment "Produktiv” ist er “MyDB_Prod”

image

6. Projekt bereitstellen

Die Bereitstellung des SSIS-Projektes wird über einen Wizard erledigt. Dazu muss das Projekt zunächst einmal erstellt werden (Build => Build SSISTest). Danach kann das Projekt über einen Rechtsklick auf den Projektnamen und “Deploy” bereitgestellt werden.

image

Der Wizard startet:

image

Klick auf “Next”, also übernehmen der Datei.

Als Deployment-Ziel wähle ich den lokalen Server und den vorher erstellten Ordner.

image

Im nächsten Fenster findet die Zuordnung von Parametern zu Parameterwerten statt.

image

Hier gibt es für jeden Parameter die Möglichkeit den “Design default value” zu verwenden, den Wert zu bearbeiten, oder den Parameterwert mit einer Environment-Variable zu verknüpfen. Zur Verknüpfung mit der bereits angelegten Variable DatabaseName muss dieser Name exakt so eingegeben werden, wie er auf dem Server vorhanden ist (evtl. gibt es in einer späteren Version eine Möglichkeit auf dem Server vorhandene Parameter hier auszuwählen).

Mit einem Klick auf Next gibt es noch eine Zusammenfassung und der abschließende Klick auf Deploy stellt das Projekt bereit und es erscheint im SSMS.

image

Deployment erledigt.

image

Anzeige im SSMS

image

7. Projekt mit Environments verknüpfen

Nun muss das Projekt noch mit den zuvor erstellten Environments verknüpft werden. Dazu Rechtsklick auf das Projekt => Properties und im sich öffnenden Fenster unter References beide angelegte Environments anlegen.

image

Damit ist die “Entwicklung” beendet und es kann an die Ausführung gedacht werden.

8. Paket ausführen

Um nun das Paket mit einer bestimmten Environment auszuführen einfach Rechtsklick auf das Paket => Run. In diesem Fenster kann ausgewählt werden, welche Environment verwendet werden soll und durch einen Klick auf OK läuft die Ausführung.

image

Durch den in der Environment gespeicherten Variablenwert wird das Paket zur Laufzeit konfiguriert und fügt seine Daten entweder in die Datenbank “MyDB_Prod” oder “MyDB_Test” ein.

select * from MyDB_Prod..mytable
select * from MyDB_Test..mytable
blog comments powered by Disqus