Below code will help us down load a specific packages from integration services.
$SqlServer = "SSIS2025DB,81117"
$OutputFolder = "C:\SSISBackup"
if (!(Test-Path $OutputFolder))
{
New-Item -ItemType Directory -Path $OutputFolder -Force | Out-Null
}
$query = @"
;WITH FolderHierarchy AS
(
SELECT
folderid,
parentfolderid,
foldername,
CAST(foldername AS VARCHAR(MAX)) AS FolderPath
FROM msdb.dbo.sysssispackagefolders
WHERE parentfolderid IS NULL
UNION ALL
SELECT
f.folderid,
f.parentfolderid,
f.foldername,
CAST(h.FolderPath + '\' + f.foldername AS VARCHAR(MAX)) AS FolderPath
FROM msdb.dbo.sysssispackagefolders f
INNER JOIN FolderHierarchy h
ON f.parentfolderid = h.folderid
)
SELECT
p.name AS PackageName
, ISNULL(h.FolderPath,'Root') AS FolderPath
, CAST(CAST(p.packagedata AS VARBINARY(MAX)) AS XML) AS PackageXML
FROM msdb.dbo.sysssispackages p
LEFT JOIN FolderHierarchy h
ON p.folderid = h.folderid
WHERE p.name IN (
'HMR_Breakeven_Pfo_Load'
, 'HMR_Cache_Refresh_VACOil'
, 'HMR_Cache_Refresh_WasteOil'
, 'HMR_Customer_Profile_Refresh_VACOil'
, 'HMR_Customer_Profile_Refresh_WasteOil_BranchView'
, 'HMR_Customer_Profile_Refresh_WasteOil_TankView'
, 'HMR_Inter_Company_Rate'
, 'HMR_UMO_Refresh'
)
ORDER BY FolderPath, PackageName
"@
$packages = Invoke-Sqlcmd `
-ServerInstance $SqlServer `
-Database msdb `
-Query $query
foreach ($pkg in $packages)
{
# Create local folder structure matching MSDB hierarchy
if ($pkg.FolderPath -eq "Root")
{
$LocalFolder = $OutputFolder
$PackagePath = "\" + $pkg.PackageName
}
else
{
$LocalFolder = Join-Path $OutputFolder $pkg.FolderPath
$PackagePath = "\" + $pkg.FolderPath + "\" + $pkg.PackageName
}
if (!(Test-Path $LocalFolder))
{
New-Item -ItemType Directory -Path $LocalFolder -Force | Out-Null
}
$FileName = Join-Path $LocalFolder ($pkg.PackageName + ".dtsx")
$pkg.PackageXML.OuterXml | Out-File `
-FilePath $FileName `
-Encoding utf8
Write-Host "Package Path : $PackagePath"
Write-Host "Exported To : $FileName"
Write-Host "--------------------------------------"
}
Write-Host "Export completed."
No comments:
Post a Comment