function Export-SSISProjectSSISDB

{

(

[Parameter(position=0, mandatory=$true)][string]$Instance,

[Parameter(position=1, mandatory=$true)][string]$OutputDir

)

if(!(Test-Path $OutputDir))

{

Write-Output "Error - invalid path specified in OutputDir"

return

}

$testquery = "SELECT COUNT(*) AS 'Result' FROM sys.databases WHERE name = 'SSISDB'"

{

$result = (Invoke-Sqlcmd -ServerInstance $Instance -Query $testquery -ConnectionTimeout 5 -QueryTimeout 5 -ErrorAction Stop).Result

if($result -eq 0)

{

Write-Output "Error - no SSISDB present on instance or no permission to view it"

return

}

}

{

Write-Output "Error - failure connecting to instance"

return

}

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null

$SSISnamespace = "Microsoft.SqlServer.Management.IntegrationServices"

$connstring = "Data source=$($Instance);Initial Catalog=master;Integrated Security=SSPI;"

$sqlconn = New-Object System.Data.SqlClient.SqlConnection $connstring

$SSIS = New-Object $SSISnamespace".IntegrationServices" $sqlconn

$catalog = $SSIS.Catalogs["SSISDB"]

foreach($folder in $catalog.Folders)

{

Set-Location -Path $outputdir

New-Item -ItemType Directory -Name $folder.Name | Out-Null

$folderpath = $outputdir + "\" + $folder.Name

Set-Location -path $folderpath

$projects = $folder.Projects

if($projects.Count -gt 0)

{

foreach($project in $projects)

{

$projectpath = $folderpath + "\" + $project.Name + ".ispac"

Write-Host "Exporting to $($projectpath) ...";

[System.IO.File]::WriteAllBytes($projectpath, $project.GetProjectBytes())

}

}

}

Set-Location -Path $outputdir

}