Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Deploys an ispsc file to a SSISDB using MOM.
#######################
<#
.SYNOPSIS
Installs an SSIS project to a SSISDB.
.DESCRIPTION
I created this by modifying demo script shared by Bill Fellows through PASS session "Understanding the SSIS 2012 Deployment Model [LT-101]"
The Install-ISProject script installs an ispsc file to a SSISDB using MOM.
Works for 2012 and higher
.EXAMPLE
./Install-ISProject.ps1 -IspacFullName "D:\App_temp\SSIS\Test2\bin\Development\Test2.ispac" -ServerInstance "SpeakSQL\JY2012" -CatalogFolderName "DEV" -ISProjectName "test2"
This command install the sqlpsx1.ispac project to SQL Server instance SpeakSQL\JY2012 under the DEV folder as test2. If DEV folder does not exist it will be created.
.EXAMPLE
./Install-ISProject.ps1 -IspacFullName "D:\App_temp\SSIS\Test2\bin\Development\Test2.ispac" -ServerInstance "SpeakSQL\JY2012" -CatalogFolderName "DEV" -ISProjectName "test2" -Verbose
.NOTES
Version History
v1.0 - SpeakSQL.wordpress.com - 5/31/2013 - Initial release
#>
param(
[Parameter(Position=0, Mandatory=$true)]
[string]
$IspacFullName,
[Parameter(Position=1, Mandatory=$true)]
[string]
$ServerInstance,
[Parameter(Position=2, Mandatory=$true)]
[string]
$CatalogFolderName,
[Parameter(Position=3, Mandatory=$true)]
[string]
$ISProjectName
)
Add-Type -AssemblyName "Microsoft.SqlServer.Management.IntegrationServices, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$ErrorActionPreference = "Stop"
#######################
Function Deploy-Project
{
param($IspacFullName, $ServerInstance, $CatalogFolderName, $ISProjectName)
write-verbose "Connecting $ServerInstance"
$ConnectionString = [String]::Format("Data Source={0};Initial Catalog=msdb;Integrated Security=SSPI;", $ServerInstance)
$Connection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
$IntegrationServices = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices($Connection)
# SSISDB catalog
$catalog = $IntegrationServices.Catalogs["SSISDB"]
$CatalogFolder = $catalog.Folders[$CatalogFolderName]
if (-not $CatalogFolder)
{
write-verbose "Creating folder $CatalogFolderName"
$CatalogFolder = New-Object Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder($catalog, $CatalogFolderName, $CatalogFolderName)
$CatalogFolder.Create()
}
else
{
write-verbose "Existing folder $CatalogFolderName"
}
# test to ensure file exists
if (-not $IspacFullName -or -not (Test-Path $IspacFullName))
{
throw "Project $IspacFullName does not exist"
}
write-verbose "Deploying $IspacFullName"
# read the data into a byte array
[byte[]] $ProjectStream = [System.IO.File]::ReadAllBytes($IspacFullName)
# $ISProjectName MUST match the value in the .ispac file
$Project = $CatalogFolder.DeployProject($ISProjectName, $ProjectStream)
} #Deploy-Project
#######################
## MAIN ##
#######################
try {
$PackageFullName=$CatalogFolderName+"\\"+$ISProjectName
Deploy-Project -IspacFullName $IspacFullName -ServerInstance $ServerInstance -CatalogFolderName $CatalogFolderName -ISProjectName $ISProjectName
}
catch {
write-error "$_ `n $("Failed to deploy IspacFullName {0} to ServerInstance {1} PackageFullName {2}" -f $IspacFullName,$ServerInstance,$PackageFullName)"
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment