Case
I have an SSIS project in Visual Studio which is called "01 StageFinance". The prefix is to preserve the order of projects in the Visual Studio solution.
 |
| Solution Explorer inVisual Studio |
When I manually deploy the Ispac file to the Catalog, I have the possibility to rename the project from "01 StageFinance" to "StageFinance".
 |
| Possible to rename project in Wizard |
But when I deploy the Ispac file via Powershell or TSQL then it returns an error when I rename the prject name:
Failed to deploy the project. Fix the problems and try again later.:The specified project name, StageFinance
, does not match the project name in the deployment file. Why do I need to provide the project name if I can't change it?
Solution
I used the SQL Server Profiler to see what happens during deployment. Both the Wizard and the PowerShell script call the stored procedure
deploy_project
exec [SSISDB].[catalog].[deploy_project]
@folder_name=N'Finance',@project_name=N'StageFinance',@project_stream=0x504B0304
What struc to me was that the Wizard called the stored procedure with the new/changed name and then it didn't fail. And when I tried that with powershell it failed.
$NewProjectName = "StageFinance"
[byte[]] $IspacFile = [System.IO.File]::ReadAllBytes($IspacFilePath)
$Folder.DeployProject($NewProjectName, $IspacFile)
Conclusion: The ispac file was probably modified by the Wizard. Next I unzipped the Ispac file and edited the file @Project.manifest and saw that line 4 contained the project name.
 |
| @Project.manifest |
I changed that project name to "StageFinance", zipped all the files and renamed the zipfile to .Ispac. Then I deployed the new Ispac file without any errors! Doing that manually each time the project needs to be deployed is annoying.
Automate rename with PowerShell
Recently I posted a PowerShell
deployment script for automatic deployment. I added a sectio to that script to do the project rename:
# Partial Script
############################
########## RENAME ##########
############################
# If the filename and projectname are different
# Then we need to rename the internal projectname
# before deploying it.
# Derive projectname from ISPAC filename
$CurrentProjectName = [system.io.path]::GetFileNameWithoutExtension($IspacFilePath)
# Check if rename is necessary
If (-not($CurrentProjectName -eq $ProjectName))
{
# Split filepath of ispac file in folder and file
$TmpUnzipPath = split-path $IspacFilePath -Parent
# Determine the filepath of the new ispac file
$NewIspacFilePath = $TmpUnzipPath + "\" + $ProjectName + ".ispac"
# Determine the path of the unzip folder
$TmpUnzipPath = $TmpUnzipPath + "\" + $CurrentProjectName
# Catch unexpected errors and stop script
Try
{
# Check if new ispac already exists
if (Test-Path $NewIspacFilePath)
{
[System.IO.File]::Delete($NewIspacFilePath)
}
# Search strings
$SearchStringStart = ''
$SearchStringEnd = ''
# Add reference to compression namespace
Add-Type -assembly "system.io.compression.filesystem"
# Extract ispac file to temporary location (.NET Framework 4.5)
[io.compression.zipfile]::ExtractToDirectory($IspacFilePath, $TmpUnzipPath)
# Replace internal projectname with new projectname
$EditFile = $TmpUnzipPath + "\@Project.manifest"
(get-content $EditFile).replace($SearchStringStart + $CurrentProjectName + $SearchStringEnd, $SearchStringStart + $ProjectName + $SearchStringEnd) | set-content $EditFile
# Zip temporary location to new ispac file (.NET Framework 4.5)
[io.compression.zipfile]::CreateFromDirectory($TmpUnzipPath, $NewIspacFilePath)
# Delete temporary location
[System.IO.Directory]::Delete($TmpUnzipPath, $True)
# Replace ispac parameter
$IspacFilePath = $NewIspacFilePath
}
Catch [System.Exception]
{
Throw [System.Exception] "Failed renaming project in $IspacFileName : $_.Exception.Message "
}
}
The complete script can be downloaden
here.
Thanks to Bill Fellows for pointing me in the right direction.
