Skip to content

Instantly share code, notes, and snippets.

@zblesk zblesk/database.sql
Created Feb 7, 2017

Embed
What would you like to do?
-- 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
# 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"
$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';
};
<#
.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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.