May 8, 2018 at 12:26 pm
I have an ssis solution that is encrypted with password and when I deploy it directly from visual studio, it runs on the server without any problem.I am trying to deploy it using a powershell query, it does deploy without any problems but the password does not go through, and when I try to run it, I get the following error: "The protection level of the package requires a password, but the packagepassword property is empty", I tried changing the protection level using dtutil.exe /file but it still gives me the same error.
[CmdletBinding()]
Param(
# IsPacFilePath is required
[Parameter(Mandatory=$True,Position=1)]
[string]$IspacFilePath,
# SsisServer is required
[Parameter(Mandatory=$True,Position=2)]
[string]$SsisServer,
# FolderName is required
[Parameter(Mandatory=$True,Position=3)]
[string]$FolderName,
# ProjectName is not required
# If empty filename is used
[Parameter(Mandatory=$False,Position=4)]
[string]$ProjectName,
# EnvironmentName is not required
# If empty no environment is referenced
[Parameter(Mandatory=$False,Position=5)]
[string]$EnvironmentName,
# EnvironmentFolderName is not required
# If empty the FolderName param is used
[Parameter(Mandatory=$False,Position=6)]
[string]$EnvironmentFolderName
)if (-not $ProjectName)
{
$ProjectName = [system.io.path]::GetFileNameWithoutExtension($IspacFilePath)
}
if (-not $EnvironmentFolderName)
{
$EnvironmentFolderName = $FolderName
}clear
Write-Host "========================================================================================================================================================"
Write-Host "== Used parameters =="
Write-Host "========================================================================================================================================================"
Write-Host "Ispac File Path : " $IspacFilePath
Write-Host "SSIS Server : " $SsisServer
Write-Host "Project Folder Path : " $FolderName
Write-Host "Project Name : " $ProjectName
Write-Host "Environment Name : " $EnvironmentName
Write-Host "Environment Folder Path: " $EnvironmentFolderName
Write-Host "========================================================================================================================================================"
Write-Host ""
if (-Not (Test-Path $IspacFilePath))
{
Throw [System.IO.FileNotFoundException] "Ispac file $IspacFilePath doesn't exists!"
}
else
{
$IspacFileName = split-path $IspacFilePath -leaf
Write-Host "Ispac file" $IspacFileName "found"
}
Write-Host "Connecting to server $SsisServer "
$SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
[System.Reflection.Assembly]::LoadWithPartialName($SsisNamespace) | Out-Null;$SqlConnectionstring = "Data Source=" + $SsisServer + ";Initial Catalog=master;Integrated Security=SSPI;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring$IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnectionif (-not $IntegrationServices)
{
Throw [System.Exception] "Failed to connect to server $SsisServer "
}
else
{
Write-Host "Connected to server" $SsisServer
}
$Catalog = $IntegrationServices.Catalogs["SSISDB"]
if (-not $Catalog)
{
Throw [System.Exception] "SSISDB catalog doesn't exist. Create it manually!"
}
else
{
Write-Host "Catalog SSISDB found"
}$Folder = $Catalog.Folders[$FolderName]
if (-not $Folder)
{
Write-Host "Creating new folder" $FolderName
$Folder = New-Object $SsisNamespace".CatalogFolder" ($Catalog, $FolderName, $FolderName)
$Folder.Create()
}
else
{
Write-Host "Folder" $FolderName "found"
}
if($Folder.Projects.Contains($ProjectName)) {
Write-Host "Deploying" $ProjectName "to" $FolderName "(REPLACE)"
}
else
{
Write-Host "Deploying" $ProjectName "to" $FolderName "(NEW)"
}
[byte[]] $IspacFile = [System.IO.File]::ReadAllBytes($IspacFilePath)
$Folder.DeployProject($ProjectName, $IspacFile)
$Project = $Folder.Projects[$ProjectName]
if (-not $Project)
{
return ""
}
if (-not $EnvironmentName)
{
$IntegrationServices = $null
Return "Ready deploying $IspacFileName without adding environment references"
}
$EnvironmentFolder = $Catalog.Folders[$EnvironmentFolderName]
if (-not $EnvironmentFolder)
{
Throw [System.Exception] "Environment folder $EnvironmentFolderName doesn't exist"
}
if(-not $EnvironmentFolder.Environments.Contains($EnvironmentName))
{
Throw [System.Exception] "Environment $EnvironmentName doesn't exist in $EnvironmentFolderName "
}
else
{
$Environment = $Catalog.Folders[$EnvironmentFolderName].Environments[$EnvironmentName] if ($Project.References.Contains($EnvironmentName, $EnvironmentFolderName))
{
Write-Host "Reference to" $EnvironmentName "found"
}
else
{
Write-Host "Adding reference to" $EnvironmentName
$Project.References.Add($EnvironmentName, $EnvironmentFolderName)
$Project.Alter()
}
}
$ParameterCount = 0
foreach ($Parameter in $Project.Parameters)
{
$ParameterName = $Parameter.Name
if ($ParameterName.StartsWith("CM.","CurrentCultureIgnoreCase"))
{
}
elseif ($ParameterName.StartsWith("INTERN_","CurrentCultureIgnoreCase"))
{
Write-Host "Ignoring Project parameter" $ParameterName " (internal use only)"
}
elseif ($Environment.Variables.Contains($Parameter.Name))
{
$ParameterCount = $ParameterCount + 1
Write-Host "Project parameter" $ParameterName "connected to environment"
$Project.Parameters[$Parameter.Name].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, $Parameter.Name)
$Project.Alter()
}
else
{
# Variable with the name of the project parameter is not found in the environment
# Throw an exeception or remove next line to ignore parameter
Throw [System.Exception] "Project parameter $ParameterName doesn't exist in environment"
}
}
Write-Host "Number of project parameters mapped:" $ParameterCount
$ParameterCount = 0
foreach ($Package in $Project.Packages)
{
foreach ($Parameter in $Package.Parameters)
{
# Get parameter name and check if it exists in the environment
$PackageName = $Package.Name
$ParameterName = $Parameter.Name
if ($ParameterName.StartsWith("CM.","CurrentCultureIgnoreCase"))
{
}
elseif ($ParameterName.StartsWith("INTERN_","CurrentCultureIgnoreCase"))
{
Write-Host "Ignoring Package parameter" $ParameterName " (internal use only)"
}
elseif ($Environment.Variables.Contains($Parameter.Name))
{
$ParameterCount = $ParameterCount + 1
Write-Host "Package parameter" $ParameterName "from package" $PackageName "connected to environment"
$Package.Parameters[$Parameter.Name].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, $Parameter.Name)
$Package.Alter()
}
else
{
Throw [System.Exception] "Package parameter $ParameterName from package $PackageName doesn't exist in environment"
}
}
}
Write-Host "Number of package parameters mapped:" $ParameterCount
$IntegrationServices = $null
Return "Ready deploying $IspacFileName "
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply