使用SQL身份验证的SQL Server 2016使用powershell在MSDB中部署SSIS包

# 示例连接字符串
$serverName = "YourServerName\InstanceName"
$databaseName = "MSDB"
$username = "YourUsername"
$password = "YourPassword"

# 创建连接
$connectionString = "Server=$serverName;Database=$databaseName;User Id=$username;Password=$password;"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)

try {
    $connection.Open()
    Write-Host "Connected to SQL Server successfully."
}
catch {
    Write-Host "Failed to connect to SQL Server: $_"
}
finally {
    $connection.Close()
}

# 示例部署 SSIS 包的 PowerShell 脚本
$packagePath = "C:\Path\To\YourPackage.dtsx"
$packageParentFolder = [System.IO.Path]::GetDirectoryName($packagePath)
$packageName = [System.IO.Path]::GetFileNameWithoutExtension($packagePath)

# 创建 SSIS 执行上下文
$executionId = [System.Guid]::NewGuid()
$executionParameter = New-Object Microsoft.SqlServer.Dts.Runtime.ExecutionParameter($executionId, "PackagePath", $packagePath)
$executionParameter.Value = $packagePath

# 执行包
$package = New-Object Microsoft.SqlServer.Dts.Runtime.Package
$package.LoadFromDTSExecResult($executionParameter, $null)

$executionResult = $package.Execute($null, $executionParameter, $null)

if ($executionResult.Status -eq [Microsoft.SqlServer.Dts.Runtime.DTSExecResult]::Success) {
    Write-Host "Package deployed successfully."
}
else {
    Write-Host "Failed to deploy package: $($executionResult.Status)"
}