PoshCode Logo PowerShell Code Repository

SQLPSX SSIS Demo by Chad Miller 22 months ago
embed code: <script type="text/javascript" src="http://PoshCode.org/embed/1769"></script>download | new post

A PowerShell script that demonstrates working with SSIS using the SQL Server PowerShell Extensions module SSIS.

  1. #Edit SSIS.psm1 and Comment/Uncomment 2005 or 2008 version of SSIS assembly
  2. #add-type -AssemblyName "Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
  3. add-type -AssemblyName "Microsoft.SqlServer.ManagedDTS, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
  4.  
  5. #Import the SSIS module
  6. import-module SSIS
  7.  
  8. #Work with SSIS packages stored on the file system
  9. $packages = dir "C:\Program Files\Microsoft SQL Server\100\DTS\Packages\*" | select -ExpandProperty Fullname | foreach {get-ispackage -path $_ }
  10. $packages | foreach {$package = $_; $_.Configurations | Select @{n='Package';e={$Package.DisplayName}}, Name,ConfigurationString}
  11. $packages | foreach {$package = $_; $_.Connections | Select @{n='Package';e={$Package.DisplayName}}, Name,ConnectionString}
  12.  
  13. #Create a new folder on the SSIS server
  14. new-isitem "\msdb" "sqlpsx" "Z002"
  15. #Copy SSIS packages from the file system to SQL Server and change the connection string for SSISCONFIG
  16. copy-isitemfiletosql -path "C:\Program Files\Microsoft SQL Server\100\DTS\Packages\*" -destination "msdb\sqlpsx" -destinationServer "Z002" -connectionInfo @{SSISCONFIG=".\SQLEXPRESS"}
  17.  
  18. #Work with SSIS packages on SQL Server
  19. $packages = get-isitem -path '\sqlpsx' -topLevelFolder 'msdb' -serverName "Z002\SQL2K8" | where {$_.Flags -eq 'Package'} | foreach {get-ispackage -path $_.literalPath -serverName $_.Servername}
  20. $packages | foreach {$package = $_; $_.Configurations | Select @{n='Package';e={$Package.DisplayName}}, Name,ConfigurationString}
  21. $packages | foreach {$package = $_; $_.Connections | Select @{n='Package';e={$Package.DisplayName}}, Name,ConnectionString}

Submit a correction or amendment below (click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me