Friday, 14 March 2014

PowerShell to list the packages stored in Integration Services SQL2008/SQL2008R2

PowerShell to list the packages stored in Integration Services SQL2008/SQL2008R2; I will come up with a SQL 2012 version soon.

# Get SSIS Package List from a Integration Services Server
# Created by - Vinoth N Manoharan
# Version 1.0
# Date - 14/02/2014

$SSISServerName = "Enter SSIS Servername"

Clear-Host
 add-type -AssemblyName "Microsoft.SqlServer.ManagedDTS, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Import-Module SQLPS –DisableNameChecking -ErrorAction SilentlyContinue
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.ManagedDTS")|Out-Null
$FnChildFolders = @()
$FnBaseFolderPath = $null
$SubFnChildFolder = @()
$FnChildFolders = @()
$FolderName = $null
$ChkFolderName = $null
$ChildFolderElements = @()
$Element = @()
$SubChkFolder = $null
$BaseFolderPath = $null
$SSISServerName = $null
$SSISServer = @()
$BaseFolder = @()
$ChildFolders = @()
$BaseFolderPakages = @()
$SubChildFolder = @()
$pkgfolderpath = $null
$subpkgfolderpath = $null

Function GetPackageFromFolder($FnChildFolders,$fnFoldername)
{ 
 
 [String]$FolderName= $FnChildFolders
 Write-Host $fnFoldername
   Write-Host "----------------------------`n"
 $ChildFolderElements = $SSISServer.GetPackageInfos($FolderName,$SSISServerName,$null,$null) 
 #$ChildFolderElements
   if($ChildFolderElements -ne @() -or $ChildFolderElements -ne $null)
 {
 forEach($Element in $ChildFolderElements)
 {
  if($Element.Flags -eq "Folder")
  {
   Write-Host "`n`tSubFolder----------->"$Element.Name
   $subpkgfolderpath = $FolderName+$Element.Name+"\"
   #$subpkgfolderpath
   GetPackageFromFolder $subpkgfolderpath $Element.Name
  }
  Else
  {
   Write-Host $Element.Name
  }
 }
 }
      
Write-Host "`n"
}
$BaseFolderPath = "\\"
$SSISServer =  New-Object Microsoft.SQLServer.DTS.RunTime.Application
$BaseFolder = $SSISServer.GetPackageInfos('\',$SSISServerName,$null,$null) 
$ChildFolders = $BaseFolder|Where{$_.Flags -eq "Folder"}
$BaseFolderPakages = $BaseFolder|Where{$_.Flags -eq "Package"}
Write-Host "\"
Write-Host "----"
$BaseFolderPakages.name
Write-Host "`n"
ForEach($SubChildFolder in $ChildFolders)
{
  $FolderName = $SubChildFolder.Name
  $SubChildFolderpath = $BaseFolderPath+$FolderName+"\"
  GetPackageFromFolder $SubChildFolderpath $FolderName
}
Copyright © 2014 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

No comments:

Post a Comment