SSIS Check Deployment Feature Image

Weryfikowanie opublikowanego pakietu SSIS

Written by on in DevOps, Integration Services, PowerShell

SQL Server Integration Services od wersji SQL Server 2012 pozwala na korzystanie z “Project Deployment Model”, który umożliwia wygodne publikowanie pakietów na serwer. Korzystając z tego modelu wszystkie pakiety w jednym projekcie są kompilowane do jednego pliku wynikowego (ISPAC) i następnie mogą zostać opublikowane do SSIS Catalogu, lub mówiąc konkretnie do bazy danych SSISDB (od wersji SQL Server 2016 istnieje również możliwość publikacji pojedynczych pakietów). Samą publikację można przeprowadzić za pomocą Visual Studio, narzędzia ISDeploymentWizard, SQL Server Management Studio, ale także za pomocą T-SQL, Powershell czy bibliotek dla platformy .NET. Liczba dostępnych metod oraz łatwość użycia sprawiają, że publikowanie nie jest trudne i łatwo go zautomatyzować za pomocą własnych skryptów lub korzystając z gotowych rozwiązań jak TeamCity.

Niekiedy zdarza się jednak, że coś pójdzie nie tak. W przypadku gdy z jakichkolwiek powodów publikacja się nie powiedzie, zostanie przerwana i zostanie zwrócony błąd sprawa jest oczywista. Zdarza się jednak, że projekt zostanie opublikowany, natomiast jego uruchomienie świadczy o tym, że na serwerze znajduje się jego stara lub niewłaściwa wersja. Na taki problem można trafić między innymi w sytuacji, kiedy za pomocą TeamCity próbujemy opublikować projekt, który wcześniej został cofnięty do poprzedniej wersji w TFS. Niestety TeamCity w takiej sytuacji czasami miewa problemy z wybraniem właściwego brancha i przy braku uwagi może to spowodować opublikowania niewłaściwej wersji pakietu. Co więcej w takim przypadku zobaczymy w SSIS Catalog, że pojawiła się nowa wersja, natomiast niekoniecznie będzie to wersja właściwa. W sytuacji manualnego publikowania błąd ludzki oczywiście też nie jest wykluczony. W celu uniknięcia ewentualnych problemów można skorzystać z kilku metod, które pozwolą nam zweryfikować jaka wersja projektu/pakietu znajduje się na serwerze. Zarówno w przypadku automatyzacji i Continiuus Integration jak i w przypadku ręcznej publikacji dodatkowa weryfikacja może okazać się bardzo pomocna i może zmniejszyć niebezpieczeństwo ewentualnych błędów.

Najprostszą metodą, aby sprawdzić co właściwie zostało opublikowane jest skorzystanie z Visual Studio. Wybierając kolejno File > New > Project > Templates > Business Intelligence > Integration Services będziemy mogli skorzystać z szablonu “Integration Services Import Project Wizard”, który umożliwi nam pobranie projektu z SSIS Catalogu (bazy danych SSISDB).

This slideshow requires JavaScript.

Za pomocą konfiguratora wystarczy połączyć się do SSIS Catalogu oraz wybrać projekt, który chcemy zaimportować. Korzystając z tej metody możemy po prostu otworzyć pakiet, który ostatnio był modyfikowany i sprawdzić czy jego wersja odpowiada tej, która powinna znaleźć się na serwerze.

Inne metody korzystają z faktu, iż każda zmiana w projekcie powoduje zmianę w metadanych tego pakietu, w tym wartości DTS:VersionBuild. Na marginesie warto zauważyć, że mimo nazwy numer generowany jest w chwili zapisu projektu, a nie jego budowania. Ponadto w metadanych znajdziemy również, między innymi, DTS:VersionGUID, natomiast dla celów tego postu jest on zupełnie nieistotny, gdyż jego wartość ulegnie zmianie w chwili publikacji, ale nawet w chwili każdorazowego jego uruchomienia (do sprawdzenia w SSISDB). Warto również nadmienić, że “VersionBuild” dotyczy pojedynczego pakietu i każdy pakiet ma różne wartości zgodnie z ilością zmian. Wartości te można odczytać wyświetlając kod źródłowy pakietu.

Początek kodu źródłowego będzie podobny do tego:

<?xml version="1.0"?>
<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts"
  DTS:refId="Package"
  DTS:CreationDate="9/4/2017 7:53:38 PM"
  DTS:CreationName="Microsoft.Package"
  DTS:CreatorComputerName="COMPUTER_NAME"
  DTS:CreatorName="DOMAIN\USER_NAME"
  DTS:DTSID="{7086C6CC-8BFD-410A-B7C1-2915269C5648}"
  DTS:ExecutableType="Microsoft.Package"
  DTS:LastModifiedProductVersion="13.0.4001.0"
  DTS:LocaleID="1033"
  DTS:ObjectName="Package"
  DTS:PackageType="5"
  DTS:VersionBuild="10"
  DTS:VersionGUID="{3E27251D-320C-43E1-A25A-C2D527FA4B6D}">

Korzystając z tej informacji, po zaimportowaniu projektu tak jak wyżej, zamiast szukać odpowiedniej zmiany w pakiecie wystarczy porównać wersję pakietu z serwera z wersję pakietu w projekcie.

Dzięki tej informacji można również sprawdzić aktualną wersję pakietu na serwerze bez jego pobierania, gdyż metadane pakietów zostają zapisywanie w bazie SSISDB. W celu weryfikacji wystarczy zatem wykorzystać odpowiednie zapytanie na tej bazie.

USE SSISDB;

SELECT 
	pr.last_deployed_time,
	pa.name,
	pa.version_build
FROM SSISDB.catalog.folders f
INNER JOIN SSISDB.catalog.projects pr
	ON f.folder_id = pr.folder_id
INNER JOIN SSISDB.catalog.packages pa
	ON pr.project_id = pa.project_id
WHERE f.name = 'Demo_CheckDeployment'
	AND pr.name = 'SSIS_Demo_CheckDeployment'
	AND pa.name = 'Package.dtsx'

Rezultatem zapytania będzie:

Idąc o krok dalej możemy zautomatyzować naszą weryfikację. Po pierwsze za pomocą PowerShell jesteśmy w stanie porównać dwa projekty na osobnych instancjach SQL Server.

#region User Configuration

$Server1 = '.\sql2016'    
$Server2 = '.\sql2016'    
$Server1FolderName = "Demo_CheckDeployment 3"
$Server2FolderName = "Demo_CheckDeployment 4"
$Server1ProjectName = "SSIS_Demo_CheckDeployment"
$Server2ProjectName = "SSIS_Demo_CheckDeployment"

#endregion

#region Script Parameters, Configuration, Object initialization

$database = 'SSISDB'
$query = "
SELECT 
	pa.name  + ' ' +CONVERT(VARCHAR(10), pa.version_build) as PackageNameAndVersion
FROM SSISDB.catalog.folders f
INNER JOIN SSISDB.catalog.projects pr
	ON f.folder_id = pr.folder_id
INNER JOIN SSISDB.catalog.packages pa
	ON pr.project_id = pa.project_id
WHERE f.name = '###FOLDER_NAME###'
	AND pr.name = '###PROJECT_NAME###'
"

$Server1Packages = New-Object "System.Collections.Generic.List[String]"
$Server2Packages = New-Object "System.Collections.Generic.List[String]"

#endregion

#region Getting data from servers

### Get data from 1st server
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server=$($Server1);database=$($Database);trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $query.Replace("###PROJECT_NAME###", $Server1ProjectName).Replace("###FOLDER_NAME###",$Server1FolderName)
$Reader = $Command.ExecuteReader()
$Datatable = New-Object System.Data.DataTable
$Datatable.Load($Reader)
foreach ($row in $Datatable)
{
    $Server1Packages.Add($row.PackageNameAndVersion)
}
$Connection.Close() 



### Get data from 1st server

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server=$($Server2);database=$($Database);trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $query.Replace("###PROJECT_NAME###", $Server2ProjectName).Replace("###FOLDER_NAME###",$Server2FolderName)
$Reader = $Command.ExecuteReader()
$Datatable = New-Object System.Data.DataTable
$Datatable.Load($Reader)

foreach ($row in $Datatable)
{
     $Server2Packages.Add($row.PackageNameAndVersion)
}

$Connection.Close() 

#endregion

#region Compare results

if ($server1Packages.Count -eq $server2Packages.Count)
{
    $differences = Compare-Object $server1Packages $server2Packages 

    if ($differences.Length -gt 0)
    {
        throw "There is a difference between this two projects"
    }
    else
    {
        # all ok
    }

}
else 
{
    throw "Different number of packages."
}

#endregion

Skrypt porówna wszystkie pakiety oraz ich wersję dla konkretnych projektów w konkretnych folderach pomiędzy dwiema instancjami SQL Server.

Innym przykładem może być porównanie wersji pakietów z plików, w tym przypadku plików znajdujących się wewnątrz pliku ISPAC, z wersją pakietów znajdującymi się na serwerze.

Add-Type -AssemblyName System.IO.Compression.FileSystem

#region User Configuration

$IspacFile = "C:\Users\sldr01\Documents\Visual Studio 2015\Projects\SSIS_Demo_CheckDeployment\SSIS_Demo_CheckDeployment\bin\Development\SSIS_Demo_CheckDeployment.ispac"
$TempFolder = "C:\test\CheckSSISDeployment\" 
$Server = '.\sql2016'    
$ServerFolderName = "Demo_CheckDeployment 4"
$ServerProjectName = "SSIS_Demo_CheckDeployment"

#endregion

#region Script Parameters, Configuration, Object initialization

$ISPACPackages = New-Object "System.Collections.Generic.List[String]"
$ServerPackages = New-Object "System.Collections.Generic.List[String]"

#endregion

#region read ISPAC pacakges

# unzip the isapc file
[System.IO.Compression.ZipFile]::ExtractToDirectory($IspacFile, $TempFolder)

$allPackages = Get-ChildItem  $TempFolder -Filter *.dtsx -Name

foreach($package in $allPackages)
{
    $tempPackage = $TempFolder + $package
    $tempPackageVersion = ""
    $tempPackageVersion = Get-Content $tempPackage | % { if($_ -match "DTS:VersionBuild") {$_.ToString().Replace("DTS:VersionBuild=","").Replace('"',"").Trim()}}
    $PackageVersion = $package + " " + $tempPackageVersion
    $ISPACPackages.Add($PackageVersion)

}

# clean temp didrectory
$TempFolderClean = $TempFolder + "*"
Remove-Item $TempFolderClean

#endregion

#region Read server packages
$database = 'SSISDB'
$query = "
SELECT 
	pa.name  + ' ' +CONVERT(VARCHAR(10), pa.version_build) as PackageNameAndVersion
FROM SSISDB.catalog.folders f
INNER JOIN SSISDB.catalog.projects pr
	ON f.folder_id = pr.folder_id
INNER JOIN SSISDB.catalog.packages pa
	ON pr.project_id = pa.project_id
WHERE f.name = '###FOLDER_NAME###'
	AND pr.name = '###PROJECT_NAME###'
"


### Get data from server
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server=$($Server);database=$($Database);trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $query.Replace("###PROJECT_NAME###", $ServerProjectName).Replace("###FOLDER_NAME###",$ServerFolderName)
$Reader = $Command.ExecuteReader()
$Datatable = New-Object System.Data.DataTable
$Datatable.Load($Reader)
foreach ($row in $Datatable)
{
    $ServerPackages.Add($row.PackageNameAndVersion)
}
$Connection.Close() 

#endregion

#region Compare results

if ($serverPackages.Count -eq $ISPACPackages.Count)
{
    $differences = Compare-Object $serverPackages $ISPACPackages 

    if ($differences.Length -gt 0)
    {
        throw "There is a difference between this two projects"
    }
    else
    {
        # all ok
    }

}
else 
{
    throw "Different number of packages."
}


#endregion

Skrypt na samym początku wypakuje zawartość pliku ISPAC do tymczasowego folderu, następnie po kolei przeczyta wszystkie pakiety i zapisze do kolekcji wersje pakietów. W końcu pobierze dla konkretnego folderu i konkretnego projektu wersje pakietów i porówna je z tymi z ISPAC.

SQL Server Integration Services dostarcza szereg możliwości do publikowania projektu, które są stosunkowo proste i zwykle niezawodne. Z racji faktu, że jednak czasami błędy się zdarzają i istnieje ryzyko opublikowania niewłaściwego projektu, dobrym pomysłem wydaje się wprowadzenie do procesu publikowania zmian projektu SSIS ((Request for change – RFC)) dodatkowego etapu z weryfikacją. Jak widać na powyższych przykładach, możliwości takiej weryfikacji jest wiele i może być ona ręczna albo w pełni automatyczna.

 

Slawomir Drzymala
Follow me on
Latest posts by Slawomir Drzymala (see all)

Leave a Reply