Deploy an SSIS project with PowerShell

Artikel
02/28/2023

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

This quickstart demonstrates how to use a PowerShell script to connect to a database server and deploy an SSIS project to the SSIS Catalog.

Prerequisites

An Azure SQL Database server listens on port 1433. If you're trying to connect to an Azure SQL Database server from within a corporate firewall, this port must be open in the corporate firewall for you to connect successfully.

Supported platforms

You can use the information in this quickstart to deploy an SSIS project to the following platforms:

SQL Server on Windows.
Azure SQL Database. For more info about deploying and running packages in Azure, see Lift and shift SQL Server Integration Services workloads to the cloud.

You cannot use the information in this quickstart to deploy an SSIS package to SQL Server on Linux. For more info about running packages on Linux, see Extract, transform, and load data on Linux with SSIS.

For Azure SQL Database, get the connection info

To deploy the project to Azure SQL Database, get the connection information you need to connect to the SSIS Catalog database (SSISDB). You need the fully qualified server name and login information in the procedures that follow.

Sign in to the Azure portal.
Select SQL Databases from the left-hand menu, and then select the SSISDB database on the SQL databases page.
On the Overview page for your database, review the fully qualified server name. To see the Click to copy option, hover over the server name.
If you forget your Azure SQL Database server login information, navigate to the SQL Database server page to view the server admin name. You can reset the password if necessary.
Click Show database connection strings.
Review the complete ADO.NET connection string.

Supported authentication method

Refer to authentication methods for deployment.

SSIS PowerShell Provider

Provide appropriate values for the variables at the top of the following script, and then run the script to deploy the SSIS project.

Note

The following example uses Windows Authentication to deploy to a SQL Server on premises. Use the New-PSDive cmdlet to establish a connection using SQL Server authentication. If you're connecting to an Azure SQL Database server, you can't use Windows authentication.

# Variables
$TargetInstanceName = "localhost\default"
$TargetFolderName = "Project1Folder"
$ProjectFilePath = "C:\Projects\Integration Services Project1\Integration Services Project1\bin\Development\Integration Services Project1.ispac"
$ProjectName = "Integration Services Project1"

# Get the Integration Services catalog
$catalog = Get-Item SQLSERVER:\SSIS\$TargetInstanceName\Catalogs\SSISDB\

# Create the target folder
New-Object "Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder" ($catalog, 
$TargetFolderName,"Folder description") -OutVariable folder
$folder.Create()

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

# Verify packages were deployed.
dir "$($catalog.PSPath)\Folders\$TargetFolderName\Projects\$ProjectName\Packages" | 
SELECT Name, DisplayName, PackageId

PowerShell script

Provide appropriate values for the variables at the top of the following script, and then run the script to deploy the SSIS project.

Note

The following example uses Windows Authentication to deploy to a SQL Server on premises. To use SQL Server authentication, replace the Integrated Security=SSPI; argument with User ID=<user name>;Password=<password>;. If you're connecting to an Azure SQL Database server, you can't use Windows authentication.

# Variables
$SSISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
$TargetServerName = "localhost"
$TargetFolderName = "Project1Folder"
$ProjectFilePath = "C:\Projects\Integration Services Project1\Integration Services Project1\bin\Development\Integration Services Project1.ispac"
$ProjectName = "Integration Services Project1"

# Load the IntegrationServices assembly
$loadStatus = [System.Reflection.Assembly]::Load("Microsoft.SQLServer.Management.IntegrationServices, "+
    "Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL")

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

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

# Get the Integration Services catalog
$catalog = $integrationServices.Catalogs["SSISDB"]

# Create the target folder
$folder = New-Object $SSISNamespace".CatalogFolder" ($catalog, $TargetFolderName,
    "Folder description")
$folder.Create()

Write-Host "Deploying " $ProjectName " project ..."

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

Write-Host "Done."

Next steps

Consider other ways to deploy a package.
Run a deployed package. To run a package, you can choose from several tools and languages. For more info, see the following articles:

Delen via