|
<# |
|
.SYNOPSIS |
|
Insalls the SSIS packages into the server. Creates the catalog and folders, if necessary. |
|
Usage info at: https://zblesk.net/blog/scripts-for-repeatable-ssis-package-deployment |
|
#> |
|
Function deploy-ssis |
|
{ |
|
Param( |
|
[Parameter(Mandatory=$true, HelpMessage="Enter the SSIS catalog password", ValueFromPipelineByPropertyName = $true)] |
|
[string]$ssisCatalogPassword, |
|
[Parameter(Mandatory=$true, HelpMessage="Enter the SSIS folder name", ValueFromPipelineByPropertyName = $true)] |
|
[string]$ssisFolderName, |
|
[Parameter(Mandatory=$true, HelpMessage="Enter the SSIS project name", ValueFromPipelineByPropertyName = $true)] |
|
[string]$ssisProjectName, |
|
[Parameter(Mandatory=$true, HelpMessage="Enter the SSIS integration server connection string", ValueFromPipelineByPropertyName = $true)] |
|
[string]$integrationSqlServerConnectionString, |
|
[Parameter(Mandatory=$true, ValueFromPipelineByPropertyName = $true)] |
|
[string]$integrationServerName, |
|
[Parameter(Mandatory=$true, HelpMessage="Enter the path to the .ispac package to be installed", ValueFromPipelineByPropertyName = $true)] |
|
[string]$ispacPackagePath, |
|
[Parameter(Mandatory=$false, HelpMessage="SQL server version, as used in the FS path", ValueFromPipelineByPropertyName = $true)] |
|
[string]$sqlServerVersion = '120' |
|
) |
|
|
|
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices" |
|
[Reflection.Assembly]::LoadWithPartialName($ISNamespace) > $_ |
|
|
|
Write-Output "Connecting to server" |
|
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $integrationSqlServerConnectionString |
|
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection |
|
|
|
if(!$integrationServices.Catalogs["SSISDB"]) |
|
{ |
|
"Creating catalog" |
|
$catalog = New-Object $ISNamespace".Catalog" ($integrationServices, "SSISDB", $ssisCatalogPassword) |
|
$catalog.Create() |
|
Write-Output "Catalog created." |
|
} |
|
else |
|
{ |
|
"Catalog already exists. Skipping creation." |
|
$catalog = $integrationServices.Catalogs["SSISDB"] |
|
} |
|
|
|
if ($catalog.Folders[$ssisFolderName]) |
|
{ |
|
Write-Output "SSIS folder $ssisFolderName already exists" |
|
} |
|
else |
|
{ |
|
Write-Output "Creating folder $ssisFolderName" |
|
$folder = New-Object $ISNamespace".CatalogFolder" ($catalog, $ssisFolderName, "Incident integration") |
|
$folder.Create() |
|
Write-Output "Folder created" |
|
} |
|
|
|
$destPath = "/SSISDB/" + $ssisFolderName + "/" + $ssisProjectName |
|
Write-Output "Everything is ready. Deploying package $ispacPackagePath to '$destPath'" |
|
Write-Output "Declared SQL server version: $sqlServerVersion" |
|
. "$env:ProgramFiles\Microsoft SQL Server\$sqlServerVersion\DTS\Binn\ISDeploymentWizard.exe" ` |
|
/Silent /SourcePath:$ispacPackagePath /DestinationServer:$integrationServerName /DestinationPath:$destPath | Out-Null |
|
} |