I have this code which check for the existence of a project in SSISDB. On the first run I make sure the project is there and it returns the correct value. But then I delete the project and run the code again but it then returns 1 again. When I restart the session then it starts to return the correct answer again. What is the problem and how can I solve it?
import-module sqlserver;
$TargetInstanceName = "localhost\default"
$TargetFolderName = "FolderForTesting";
$ProjectName = "ProjectTesting";
$catalog = Get-Item SQLSERVER:\SSIS\$TargetInstanceName\Catalogs\SSISDB\
$folder = $catalog.Folders["$TargetFolderName"];
$project = $folder.Projects["$ProjectName"];
if($null -eq $project){
Return 0
} else {
Return 1
}
CodePudding user response:
Combining mine and Theo's helpful comments into a possible solution:
import-module sqlserver;
$TargetInstanceName = "localhost\default"
$TargetFolderName = "FolderForTesting";
$ProjectName = "ProjectTesting";
try {
$catalog = Get-Item SQLSERVER:\SSIS\$TargetInstanceName\Catalogs\SSISDB\
$folder = $catalog.Folders[ $TargetFolderName ]
$project = $folder.Projects[ $ProjectName ]
if($null -eq $project){
Return 0
} else {
$project.Refresh() # Causes an exception if project actually doesn't exist
Return 1
}
}
catch {
return 0
}
This is based on Refreshing the SQL Server PowerShell Provider and PS SQLPS refreshing the SQL Server object and your own testing. I couldn't find any official information regarding the topic.