Export System Center Orchestrator PowerShell Scripts

{
  "FolderPath": "c:\SCO_PS_STRUCTURE\Runbooks\SomeFolder\SomeScript.ps1",
  "ScriptBody": "Write-Host 'Hello, World!'"
}

# Define the SQL Server connection parameters
$serverInstance = "SERVER\INSTANCE"
$database = "orchestrator"

# Specify the base path where the PowerShell script files will be saved
$basePath = "c:\SCO_PS_STRUCTURE\Runbooks\"

# Execute the SQL query to retrieve script data from the database

$query = @"
DECLARE @BasePath NVARCHAR(255) = '$($basepath)';

SELECT
    @BasePath + ISNULL(F2.Name, '') + '\' + F1.Name + '\' + P.Name + '\' + O.Name + '.ps1' AS FolderPath,
    R.ScriptBody
FROM FOLDERS AS F1
JOIN Policies AS P ON F1.UniqueID = P.ParentID
JOIN Objects AS O ON P.UniqueID = O.ParentID
JOIN RUNDOTNETSCRIPT AS R ON O.UniqueID = R.UniqueID
LEFT JOIN FOLDERS AS F2 ON F1.ParentID = F2.UniqueID
WHERE R.ScriptType = 'PowerShell';
"@

# Execute the SQL query and save the script files
$result = Execute-SqlSelectQuery -ServerInstance $serverInstance -Database $database -Query $query

# Process the query results and save script files
foreach ($object in $result) {
    $filePath = $object.FolderPath
    $fileContent = $object.ScriptBody

    if (Test-Path -Path $filePath -PathType Leaf) {
        # File already exists, so overwrite it
        Set-Content -Path $filePath -Value $fileContent
        Write-Host "File '$filePath' already exists and has been overwritten."
    } else {
        # File doesn't exist, create it
        New-Item -Path $filePath -ItemType File -Force
        Set-Content -Path $filePath -Value $fileContent
        Write-Host "File '$filePath' has been created."
    }
}