PoshCode Logo PowerShell Code Repository

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

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