Publish to SSIS Catalog using PowerShell

# -----------------------------------------------------------------------------
# Example 1
# -----------------------------------------------------------------------------
# Publish to SSIS Catalog using PowerShell
#
# Here is a PowerShell script that I use to provision and setup SSIS Catalogs
# when I'm demoing SQL Server 2012. It does the following:
#
# - Connects to localhost (default instance)
# - Drops the existing SSIS Catalog
# - Creates a new SSIS Catalog
# - Creates a Folder
# - Deploys a Project
# - Creates an Environment
# - Creates three server varaibles
# - Creates an environment reference for the project
#
# References:
#   - [Deploying Multiple SSIS Projects via PowerShell](https://www.simple-talk.com/sql/ssis/deploying-multiple-ssis-projects-via-powershell/)
#   - [Publish to SSIS Catalog using PowerShell](https://www.simple-talk.com/sql/ssis/deploying-multiple-ssis-projects-via-powershell/)
# -----------------------------------------------------------------------------

# Variables
$ProjectFilePath = "C:\SSIS\MyCatalog\Project\bin\Development\MyCatalogProject.ispac"
$ProjectName = "MyCatalogProject"
$FolderName = "Demo"
$EnvironmentName = "CustomerA"

#
# Load the IntegrationServices Assembly and Store the IntegrationServices Assembly namespace to avoid typing it every time
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

Write-Host "Connecting to server ..."
$sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

#
# Create the Integration Services object
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection

#
# Drop the existing catalog if it exists
Write-Host "Removing previous catalog ..."
if ($integrationServices.Catalogs.Count -gt 0) { $integrationServices.Catalogs["SSISDB"].Drop() }

#
# Provision a new SSIS Catalog
Write-Host "Creating new SSISDB Catalog ..."
$catalog = New-Object $ISNamespace".Catalog" ($integrationServices, "SSISDB", "SUPER#secret1")
$catalog.Create()

#
# Create a new folder
Write-Host "Creating Folder " $FolderName " ..."
$folder = New-Object $ISNamespace".CatalogFolder" ($catalog, $FolderName, "Folder description")
$folder.Create()

#
# Read the project file, and deploy it to the folder
Write-Host "Deploying " $ProjectName " project ..."
[byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
$folder.DeployProject($ProjectName, $projectFile)

Write-Host "Creating environment ..."
$environment = New-Object $ISNamespace".EnvironmentInfo" ($folder, $EnvironmentName, "Description")
$environment.Create()

#
# Adding variable to our environment
# Constructor args: variable name, type, default value, sensitivity, description
Write-Host "Adding server variables ..."
$environment.Variables.Add("CustomerID", [System.TypeCode]::String, "C111", "false", "Customer ID")
$environment.Variables.Add("FtpUser", [System.TypeCode]::String, $EnvironmentName, "false", "FTP user")
$environment.Variables.Add("FtpPassword", [System.TypeCode]::String, "SECRET1234!", "true", "FTP password")
$environment.Alter()

#
# making project refer to this environment
Write-Host "Adding environment reference to project ..."
$project = $folder.Projects[$ProjectName]
$project.References.Add($EnvironmentName, $folder.Name)
$project.Alter()

Write-Host "All done."

# -----------------------------------------------------------------------------
# Example 2
# -----------------------------------------------------------------------------
# SSIS Catalog and Project Deployment with PowerShell
#
# Overview
#
# A brief summary of the script below:
#
# 1. Check for the catalog and create it if it doesn't exist
# 2. Checks for a project folder in the catalog, creating it if it doesn't exist
# 3. Deploys the project from the ISPAC file
# 4. Creates an environment (again if it doesn't already exist) in the project
#   folder and then adds a reference to the Project
# 5. Adds a variable programmatically to the Environment folder
# 6. Configures a package parameter within the project to use the environment
#   variable
# 7. Without further ado, the script is provided below:
#
# Reference:
#   - https://blogs.msdn.microsoft.com/bluewatersql/2014/12/19/ssis-catalog-and-project-deployment-with-powershell/
# -----------------------------------------------------------------------------

$ServerName = "localhost"
$SSISCatalog = "SSISDB"
$CatalogPwd = "P@ssw0rd1"

$ProjectFilePath = "C:\Dev\SSISDeploymentDemo\SSISDeploymentDemo\bin\Development\SSISDeploymentDemo.ispac"
$ProjectName = "SSISDeploymentDemo"
$FolderName = "Deployment Demo"
$EnvironmentName = "Microsoft"

# Load the IntegrationServices Assembly
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")

# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

Write-Host "Connecting to server ..."

# Create a connection to the server
$sqlConnectionString = "Data Source=$ServerName;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

$integrationServices = New-Object "$ISNamespace.IntegrationServices" $sqlConnection

$catalog = $integrationServices.Catalogs[$SSICatalog]

# Create the Integration Services object if it does not exist
if (!$catalog) {
    # Provision a new SSIS Catalog
    Write-Host "Creating SSIS Catalog ..."
    $catalog = New-Object "$ISNamespace.Catalog" ($integrationServices, $SSISCatalog, $CatalogPwd)
    $catalog.Create()
}

$folder = $catalog.Folders[$FolderName]

if (!$folder)
{
    #Create a folder in SSISDB
    Write-Host "Creating Folder ..."
    $folder = New-Object "$ISNamespace.CatalogFolder" ($catalog, $FolderName, $FolderName)
    $folder.Create()
}

# Read the project file, and deploy it to the folder
Write-Host "Deploying Project ..."
[byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
$folder.DeployProject($ProjectName, $projectFile)

$environment = $folder.Environments[$EnvironmentName]

if (!$environment)
{
    Write-Host "Creating environment ..."
    $environment = New-Object "$ISNamespace.EnvironmentInfo" ($folder, $EnvironmentName, $EnvironmentName)
    $environment.Create()
}

$project = $folder.Projects[$ProjectName]
$ref = $project.References[$EnvironmentName, $folder.Name]

if (!$ref)
{
    # making project refer to this environment
    Write-Host "Adding environment reference to project ..."
    $project.References.Add($EnvironmentName, $folder.Name)
    $project.Alter()
}

# Adding variable to our environment
# Constructor args: variable name, type, default value, sensitivity, description
$customerID = $environment.Variables["CustomerID"];

if (!$customerID)
{
    Write-Host "Adding environment variables ..."
    $environment.Variables.Add(
        "CustomerID",
        [System.TypeCode]::String, "MSFT", $false, "Customer ID")
    $environment.Alter()
    $customerID = $environment.Variables["CustomerID"];
}


$package = $project.Packages["Package.dtsx"]
$package.Parameters["CustomerID"].Set(
    [Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced,
    $customerID.Name)
$package.Alter()