Home > Software engineering >  Strange behavior of PowerShell code - returns wrong result unless session is restarted
Strange behavior of PowerShell code - returns wrong result unless session is restarted

Time:06-21

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.

  • Related