PoshCode Logo PowerShell Code Repository

Install-ISPackage by Chad Miller 25 months ago (modification of post by Chad Miller view diff)
diff | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/3772"></script>download | new post

The Install-ISPackage script installs an Dtsx file to a SQL Server store using the command-line utility dtutil.
Works for 2005 and higher

  1. #######################
  2. <#
  3. .SYNOPSIS
  4. Installs an SSIS package to a SQL Server store.
  5. .DESCRIPTION
  6. The Install-ISPackage script installs an Dtsx file to a SQL Server store using the command-line utility dtutil.
  7. Works for 2005 and higher
  8. .EXAMPLE
  9. ./install-ispackage.ps1 -DtsxFullName "C:\Users\Public\bin\SSIS\sqlpsx1.dtsx" -ServerInstance "Z001\SQL1" -PackageFullName "SQLPSX\sqlpsx1"
  10. This command install the sqlpsx1.dtsx package to SQL Server instance Z001\SQL1 under the SQLPSX folder as sqlpsx1. If SQLPSX folder does not exist it will be created.
  11. .NOTES
  12. Version History
  13. v1.0   - Chad Miller - 6/26/2012 - Initial release
  14. v1.1   - Chad Miller - 7/05/2012 - Updated to output object. Fixed lastexitcode check in test functions
  15. v1.2   - Chad Miller - 7/09/2012 - Added SqlVersion and Dtutil Path logic
  16. v1.3   - Chad Miller - 7/10/2012 - Fixed 2005 path logic
  17. v1.4   - Chad Miller - 9/25/2012 - Fixed 2012 path logic
  18. v1.5   - Chad Miller - 11/20/2012 - Fixed issues with root path deployments
  19. #>
  20. param(
  21. [Parameter(Position=0, Mandatory=$true)]
  22. [string]
  23. $DtsxFullName,
  24. [Parameter(Position=1, Mandatory=$true)]
  25. [string]
  26. $ServerInstance,
  27. [Parameter(Position=2, Mandatory=$true)]
  28. [string]
  29. $PackageFullName
  30. )
  31.  
  32.  
  33. $ErrorActionPreference = "Stop"
  34. $Script:dtutil = $null
  35. $exitCode = @{
  36. 0="The utility executed successfully."
  37. 1="The utility failed."
  38. 4="The utility cannot locate the requested package."
  39. 5="The utility cannot load the requested package."
  40. 6="The utility cannot resolve the command line because it contains either syntactic or semantic errors"}
  41.  
  42. #######################
  43. function Get-SqlVersion
  44. {
  45.     param($ServerInstance)
  46.    
  47.     write-verbose "sqlcmd -S `"$ServerInstance`" -d `"master`" -Q `"SET NOCOUNT ON; SELECT SERVERPROPERTY('ProductVersion')`" -h -1 -W"
  48.    
  49.     $SqlVersion = sqlcmd -S "$ServerInstance" -d "master" -Q "SET NOCOUNT ON; SELECT SERVERPROPERTY('ProductVersion')" -h -1 -W
  50.  
  51.     if ($lastexitcode -ne 0) {
  52.         throw $SqlVersion
  53.     }
  54.     else {
  55.         $SqlVersion
  56.     }
  57.    
  58. } #Get-SqlVersion
  59.  
  60. #######################
  61. function Set-DtutilPath
  62. {
  63.     param($SqlVersion)
  64.  
  65.     $paths = [Environment]::GetEnvironmentVariable("Path", "Machine") -split ";"
  66.  
  67.     if ($SqlVersion -like "9*") {
  68.         $Script:dtutil = $paths | where { $_ -like "*Program Files\Microsoft SQL Server\90\DTS\Binn\" }
  69.         if ($Script:dtutil -eq $null) {
  70.             throw "SQL Server 2005 Version of dtutil not found."
  71.         }
  72.     }
  73.     elseif ($SqlVersion -like "10*") {
  74.         $Script:dtutil = $paths | where { $_ -like "*Program Files\Microsoft SQL Server\100\DTS\Binn\" }
  75.         if ($Script:dtutil -eq $null) {
  76.             throw "SQL Server 2008 or 2008R2 Version of dtutil not found."
  77.         }
  78.     }
  79.     elseif ($SqlVersion -like "11*") {
  80.         $Script:dtutil = $paths | where { $_ -like "*Program Files\Microsoft SQL Server\110\DTS\Binn\" }
  81.         if ($Script:dtutil -eq $null) {
  82.             throw "SQL Server 2012 Version of dtutil not found."
  83.         }
  84.     }
  85.  
  86.     if ($Script:dtutil -eq $null) {
  87.         throw "Unable to find path to dtutil.exe. Verify dtutil installed."
  88.     }
  89.     else {
  90.         $Script:dtutil += 'dtutil.exe'
  91.     }
  92.    
  93. } #Set-DtutilPath
  94.  
  95. #######################
  96. function install-package
  97. {
  98.     param($DtsxFullName, $ServerInstance, $PackageFullName)
  99.    
  100.     $result = & $Script:dtutil /File "$DtsxFullName" /DestServer "$ServerInstance" /Copy SQL`;"$PackageFullName" /Quiet
  101.     $result = $result -join "`n"
  102.  
  103.     new-object psobject -property @{
  104.         ExitCode = $lastexitcode
  105.         ExitDescription = "$($exitcode[$lastexitcode])"
  106.         Command = "$Script:dtutil /File `"$DtsxFullName`" /DestServer `"$ServerInstance`" /Copy SQL;`"$PackageFullName`" /Quiet"
  107.         Result = $result
  108.         Success = ($lastexitcode -eq 0)}
  109.  
  110.     if ($lastexitcode -ne 0) {
  111.         throw $exitcode[$lastexitcode]
  112.     }
  113.  
  114. } #install-package
  115.  
  116. #######################
  117. function test-path
  118. {
  119.     param($ServerInstance, $FolderPath)
  120.  
  121.     write-verbose "$Script:dtutil /SourceServer `"$ServerInstance`" /FExists SQL`;`"$FolderPath`""
  122.  
  123.     $result = & $Script:dtutil /SourceServer "$ServerInstance" /FExists SQL`;"$FolderPath"
  124.  
  125.     if ($lastexitcode -gt 1) {
  126.         $result = $result -join "`n"
  127.         throw "$result `n $($exitcode[$lastexitcode])"
  128.     }
  129.  
  130.     if ($result -and $result[4] -eq "The specified folder exists.") {
  131.         $true
  132.     }
  133.     else {
  134.         $false
  135.     }
  136.  
  137. } #test-path
  138.  
  139. #######################
  140. function test-packagepath
  141. {
  142.     param($ServerInstance, $PackageFullName)
  143.  
  144.     write-verbose "$Script:dtutil /SourceServer `"$ServerInstance`" /SQL `"$PackageFullName`" /EXISTS"
  145.    
  146.     $result = & $Script:dtutil /SourceServer "$ServerInstance" /SQL "$PackageFullName" /EXISTS
  147.  
  148.     if ($lastexitcode -gt 1) {
  149.         $result = $result -join "`n"
  150.         throw "$result `n $($exitcode[$lastexitcode])"
  151.     }
  152.  
  153.     new-object psobject -property @{
  154.         ExitCode = $lastexitcode
  155.         ExitDescription = "$($exitcode[$lastexitcode])"
  156.         Command = "$Script:dtutil /SourceServer `"$ServerInstance`" /SQL `"$PackageFullName`" /EXISTS"
  157.         Result = $result[4]
  158.         Success = ($lastexitcode -eq 0 -and $result -and $result[4] -eq "The specified package exists.")}
  159.  
  160.  
  161. } #test-packagepath
  162.  
  163. #######################
  164. function new-folder
  165. {
  166.     param($ServerInstance, $ParentFolderPath, $NewFolderName)
  167.  
  168.     $result = & $Script:dtutil /SourceServer "$ServerInstance" /FCreate SQL`;"$ParentFolderPath"`;"$NewFolderName"
  169.     $result = $result -join "`n"
  170.  
  171.     new-object psobject -property @{
  172.         ExitCode = $lastexitcode
  173.         ExitDescription = "$($exitcode[$lastexitcode])"
  174.         Command = "$Script:dtutil /SourceServer `"$ServerInstance`" /FCreate SQL;`"$ParentFolderPath`";`"$NewFolderName`""
  175.         Result = $result
  176.         Success = ($lastexitcode -eq 0)}
  177.  
  178.     if ($lastexitcode -ne 0) {
  179.         throw $exitcode[$lastexitcode]
  180.     }
  181.  
  182. } #new-folder
  183.  
  184. #######################
  185. function Get-FolderList
  186. {
  187.     param($PackageFullName)
  188.  
  189.     if ($PackageFullName -match '\\') {
  190.         $folders = $PackageFullName  -split "\\"
  191.         0..$($folders.Length -2) | foreach {
  192.         new-object psobject -property @{
  193.             Parent=$(if($_-gt 0) { $($folders[0..$($_ -1)] -join "\") } else { "\" })
  194.             FullPath=$($folders[0..$_] -join "\")
  195.             Child=$folders[$_]}}
  196.     }
  197.  
  198. } #Get-FolderList
  199.  
  200. #######################
  201. ##     MAIN          ##
  202. #######################
  203.  
  204. try {
  205.     #1. Get Sql Version
  206.     $SqlVersion = Get-SqlVersion -ServerInstance $ServerInstance
  207.    
  208.     #2. Set Dtutil Path based on Sql Version
  209.     Set-DtutilPath -SqlVersion $SqlVersion
  210.    
  211.     #3. Get SSIS Folder List, verify exists and created missing folders
  212.     Get-FolderList -PackageFullName $PackageFullName |
  213.     where { $_.FullPath -ne "" -and $(test-path -ServerInstance $ServerInstance -FolderPath $_.FullPath) -eq $false } |
  214.     foreach { new-folder -ServerInstance $ServerInstance -ParentFolderPath $_.Parent -NewFolderName $_.Child }
  215.  
  216.     #4. Install SSIS Package
  217.     install-package -DtsxFullName $DtsxFullName -ServerInstance $ServerInstance -PackageFullName $PackageFullName
  218.    
  219.     #5. Verify Package
  220.     test-packagepath -ServerInstance $ServerInstance -PackageFullName $PackageFullName
  221. }
  222. catch {
  223.     write-error "$_ `n $("Failed to install DtsxFullName {0} to ServerInstance {1} PackageFullName {2}" -f $DtsxFullName,$ServerInstance,$PackageFullName)"
  224. }

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