database.sql

-- You can also create your DB schema, if needed

-- SSIS needs CLR enabled:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
  

deploy.ps1

# Usage info at: https://zblesk.net/blog/scripts-for-repeatable-ssis-package-deployment 
. .\ssisDeployment.ps1
. .\deployConfig.ps1
write-output "Initializing SQL DB"
. sqlcmd -S $config.integrationServerName -i .\database.sql
write-output "SQL DB initialized"
write-output "`nDeploying SSIS package"
deploy-ssis @config

# If, for instance, you need to configure your packages with SQL scripts, you can run them now
# write-output "Configuring SSIS package"
#. sqlcmd -S $config.integrationServerName -i .\ssis_config.sql
# write-output "SSIS Configured"

write-output "Done"

deployConfig.ps1

$config = @{
  'ssisProjectName' = 'My SSIS Project';
  'integrationSqlServerConnectionString' = 'Data Source="server.path.here";Initial Catalog=Integration;Integrated Security=SSPI;';
  'ispacPackagePath' = '.\MyPackage.ispac';
  'ssisCatalogPassword' = 'SuperstrongPassword31337';
  'ssisFolderName' = 'My SSIS Folder';
  'integrationServerName' = 'server.path.here';
};

ssisDeployment.ps1

<#
.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
}