Automatisiertes Deployment einer BI Solution – Teil 2 SSIS Environment

Technical Value

In Teil 1 dieser Serie (Automatisiertes Deployment einer BI Lösung - SSIS Pakete) habe ich gezeigt, wie SSIS-Pakete über Power Shell in den SQL Server deployed werden können. In diesem Teil zeige ich, wie ein Environment erstellt und mit den deployten Projekten verknüpft werden kann. Seit SSIS 2012 werden SSIS Pakete über ein Environment parametrisiert. Das Environment löst somit (zumindest zum Teil) die SSIS Configurations Tabelle aus früheren Versionen ab.

Der offizielle Weg zum Erstellen eines Environments ist leider vollständig manuell, ebenso wie die Verknüpfung eines SSIS Projektes mit diesem Environment: Create and Map a Server Environment

Hat man nun ein BI-Projekt mit mehreren SSIS Projekten, so muss nach dem initialen Deployment jedes einzelne Projekt mit dem Environment und den darin enthaltenen Variablen verknüpft werden. Bei einem Projekt mit 3 SSIS Projekten und sechs Environment Variablen sind dies bereits 21 Konfigurationen, die manuell vorgenommen werden müssen (3x Verknüpfung des Environment und 3x sechs Variablen mappen).

Spätestens wenn das Projekt noch weiter wächst, ist dieser Weg nicht mehr praktikabel. Daher haben wir uns dazu entschieden, das Environment aus den Project-Parametern der einzelnen SSIS-Solutions automatisch aufzubauen und direkt beim Deployment zu verknüpfen. Hierzu habe ich das folgende PowerShell Script geschrieben und in das in Teil 1 bereits demonstrierte Script integriert.

Funktionsweise

Das im Folgenden gezeigte Script erstellt bei Bedarf ein neues Environment aus den Project Parametern, oder ergänzt ein bestehendes Environment mit evtl. neuen Project Parametern. Auf diese Weise sind die Variablen in den SSIS-Projekten immer identisch mit den Variablen des Environments. Die Environment Variablen werden additiv erstellt. D.h. am Ende entsteht ein Environment mit allen “distincten” Variablen der einzelnen SSIS Projekte. Neu angelegte Environment Variablen werden standardmäßig mit dem Wert der Project Variable gefüllt.

Ein weiterer Vorteil dieser Vorgehensweise ist, dass jeder Entwickler seine eigene Project Configuration haben kann und auch das Deployment in die Entwicklungsumgebung so erleichtert wird. Es muss nur noch die Project Configuration gepflegt werden.

Gleichzeitig wird verhindert, dass in der Entwicklung neue Environment Variablen erstellt werden und vergessen wird, diese auf die Produktion zu übertragen. Das Fehlen der Variable fällt spätestens bei der Abnahme auf.

Parameter

Zusätzlich zu den bereits definierten Parametern wird noch der folgende benötigt, um den Namen des Environment zu definieren:

[string]$environmentName = $(Read-Host "Environment Name")

Erstellung des Environments

Es wird zunächst geprüft, ob ein Environment mit dem gegebenen Namen existiert. Existiert dies noch nicht, wird es neu angelegt. Wenn das Environment bereits existiert könnte dies überschrieben werden. Wir haben uns jedoch dazu entschieden das Environment nicht zu überschreiben und in diesem Fall nur neue Variablen hinzuzufügen.

if(!$ssisFolder.Environments[$environmentName])
{
Write-Host "Creating new Environment..."
$environment = New-Object "$ssisNamespace.EnvironmentInfo" ($ssisFolder, $environmentName, $environmentDescription)
$environment.Create();
}
else
{
$environment = $ssisFolder.Environments[$environmentName]
}

Erstellung neuer Environment Variablen

Wie bereits erwähnt erstelle ich die Environment Variablen direkt aus den Project Parametern. Hierzu wird über jedes SSIS Projekt iteriert, welches auch im Deployment verwendet wird. Selbstverständlich könnte an dieser Stelle auch auf andere Konfigurationen zurückgegriffen und die Variablen z.B. aus einer Tabelle erstellt werden. Auch werden die Variablen nur dann erstellt, wenn diese noch nicht vorhanden sind. Genau wie beim Environment könnten die existierenden Variablen an dieser Stelle auch einfach überschrieben werden.

Write-Host "Configuring Environment..."
foreach($param in $ssisProject.Parameters)
{
if(!$environment.Variables.Contains($param.Name))
{
$environment.Variables.Add($param.Name, $param.DataType, $param.DesignDefaultValue, $param.Sensitive, $param.Description)
$environment.Alter()
}
}

Verknüpfung des Projektes und des Environments

Zur Verknüpfung des SSIS Projektes mit dem Environment ist lediglich eine Referenz erforderlich. Wichtig ist nach jeder Änderung explizit die Methode “Alter()” aufzurufen. Diese speichert die durchgeführten Änderungen.

Write-Host "Connecting Project and Environment..."
$ssisProject.References.Add($environmentName, $ssisFolderName)
$ssisProject.Alter()

Verknüpfung der Projekt Parameter mit Environment Variablen

Zum Schluss werden noch die einzelnen Project Variablen mit den neu erstellten Environment Variablen verknüpft. Dies erfolgt ebenfalls in der oben genannten For-Each-Schleife.

$ssisProject.Parameters[$param.IdentityKey].Set("Referenced", $param.Name)

Damit ist das gesamte SSIS Deployment bereits abgeschlossen. Im nächsten Teil demonstriere ich das Deployment einer SSDT Solution. Für alle, die das Script direkt ausprobieren möchten, hier noch die vollständige Version.

# declare parameter
param (
# required Parameter
[string]$ssisServerName = $(Read-Host "SSIS Server Name"),
[string]$ispacPath = $(Read-Host "Ordner mit ISPAC Datei(en)"),
[string]$ssisFolderName = $(Read-Host "SSIS Folder Name"),
[string]$environmentName = $(Read-Host "Environment Name"),
# parameter with suitable default values
[string]$ssisDbName = "SSISDB",
$overwriteEnvironment = $FALSE,
)
$ErrorActionPreference = "Stop"
# loading Assemblies
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")
[void][System.Reflection.Assembly]::LoadWithPartialName("System.IO")
# connecting to target SSIS Server
Write-Host "Connection to target server..."
$ssisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
$ioNamespace = "System.IO"
$sqlConnectionString = "Data Source=" + $ssisServerName + ";Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
$ssisService = New-Object "$ssisNamespace.IntegrationServices" $sqlConnection
$ssisdb = $ssisService.Catalogs[$ssisDbName]
# get SSIS Folder, or create a new one if none exists
$ssisFolder = $ssisdb.Folders[$ssisFolderName]
if(!$ssisFolder)
{
Write-Host "Creating Folder" $ssisFolderName "..."
$ssisFolder = New-Object "$ssisNamespace.CatalogFolder" $ssisdb, $ssisFolderName
$ssisFolder.Create()
$ssisFolder = $ssisdb.Folders[$ssisFolderName]
}
$files = Get-ChildItem $ispacPath -Filter *.ispac
foreach($file in $files)
{
$ispacName = $file.Name.Split(".")[0]
$ispacPath = $file.FullName
Write-Host "Deploying Project" $ispacName
$ssisFolder.DeployProject($ispacName, [System.IO.File]::ReadAllBytes($ispacPath)) | Out-Null
$ssisProject = $ssisFolder.Projects[$ispacName]
if($overwriteEnvironment)
{
if($ssisFolder.Environments[$environmentName])
{
Write-Host "Deleting old Environment..."
$ssisFolder.Environments.Remove($ssisFolder.Environments[$environmentName]) | Out-Null
$ssisFolder.Alter()
}
}
if(!$ssisFolder.Environments[$environmentName])
{
Write-Host "Creating new Environment..."
$environment = New-Object "$ssisNamespace.EnvironmentInfo" ($ssisFolder, $environmentName, "")
$environment.Create();
}
else
{
$environment = $ssisFolder.Environments[$environmentName]
}
Write-Host "Configuring Environment..."
foreach($param in $ssisProject.Parameters)
{
if($environment.Variables.Contains($param.Name) -And $overwriteEnvironment)
{
$environment.Variables.Remove($param.Name)
$environment.Alter()
}
if(!$environment.Variables.Contains($param.Name))
{
$environment.Variables.Add($param.Name, $param.DataType, $param.DesignDefaultValue, $param.Sensitive, $param.Description)
$environment.Alter()
}
$ssisProject.Parameters[$param.IdentityKey].Set("Referenced", $param.Name)
}
Write-Host "Connecting Project and Environment..."
if($ssisProject.References.Contains($environmentName, $ssisFolderName))
{
$ssisProject.References.Remove($environmentName, $ssisFolderName)
}
$ssisProject.References.Add($environmentName, $ssisFolderName)
$ssisProject.Alter()
}

Kommentare

Hi J,

sorry for the delay...
Unfortunately I don't have a script to help you.
To archive this you have to search through the XML Structure of your SSIS Package and find the source and destination tasks.
For reference please have a look at the documentation of the DTSX package schema: https://msdn.microsoft.com/en-us/library/gg587789(v=sql.105).aspx

For an OLE DB Task for example, you have to find the "Microsoft.OLEDBSource" Task, get the "SqlCommand" Attribute and analyse the SQL retrieved (assuming that your source task is using a SQL Statement rather than referencing a table).

Antworten

I am trying to read the source and destination tables with connection strings to analyse how the data is being loaded to the system. Is there a script you have for this?

thanks,
J