很苦惱，PROD上的SSIS專案，榷訓月累的往里部署，加包，也沒覺得是個什么問題，
但是今天從需要從PROD上把這個專案中所有的包都down下來，VS居然報錯Out Of Memory，無論是直接連接SSIS Catelog或者用SSMS匯出ispac均報錯，
解決方法很簡單，使用PowerShell腳本，直接把ispac download下來然后解壓縮，這樣里面就直接可以看到.dtsx檔案了，
核心就在于引數UnzipIspac，一定要是True，這樣執行完成后就能直接看見.dtsx包了，
PS腳本：
1 #PowerShell: DownloadIspac.ps1 2 ################################ 3 ########## PARAMETERS ########## 4 ################################ 5 # Change Server, folder, project and download folder 6 $SsisServer = "XXXXXXXX" # Mandatory 7 $FolderName = "XXXXXXXX" # Can be empty to download multiple projects 8 $ProjectName = "OOOOOOOOO" # Can be empty to download multiple projects 9 $DownloadFolder = "LLLLLLLLLLLLL" # Mandatory 10 $CreateSubfolders = $true # Mandatory 11 $UnzipIspac = $true # Mandatory 12 13 14 ################################################# 15 ########## DO NOT EDIT BELOW THIS LINE ########## 16 ################################################# 17 clear 18 Write-Host 19 20 "================================================================================================================================ 21 22 ========================" 23 Write-Host "== Used parameters ==" 24 Write-Host 25 26 "================================================================================================================================ 27 28 ========================" 29 Write-Host "SSIS Server :" $SsisServer 30 Write-Host "Folder Name :" $FolderName 31 Write-Host "Project Name :" $ProjectName 32 Write-Host "Local Download Folder :" $DownloadFolder 33 Write-Host "Create Subfolders :" $CreateSubfolders 34 Write-Host "Unzip ISPAC (> .NET4.5) :" $UnzipIspac 35 Write-Host 36 37 "================================================================================================================================ 38 39 ========================" 40 41 42 ########################################## 43 ########## Mandatory parameters ########## 44 ########################################## 45 if ($SsisServer -eq "") 46 { 47 Throw [System.Exception] "SsisServer parameter is mandatory" 48 } 49 if ($DownloadFolder -eq "") 50 { 51 Throw [System.Exception] "DownloadFolder parameter is mandatory" 52 } 53 elseif (-not $DownloadFolder.EndsWith("\")) 54 { 55 # Make sure the download path ends with an slash 56 # so we can concatenate an subfolder and filename 57 $DownloadFolder = $DownloadFolder = "\" 58 } 59 60 61 ############################ 62 ########## SERVER ########## 63 ############################ 64 # Load the Integration Services Assembly 65 Write-Host "Connecting to server $SsisServer " 66 $SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices" 67 [System.Reflection.Assembly]::LoadWithPartialName($SsisNamespace) | Out-Null; 68 69 # Create a connection to the server 70 $SqlConnectionstring = "Data Source=" + $SsisServer + ";Initial Catalog=master;Integrated Security=SSPI;" 71 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring 72 73 # Create the Integration Services object 74 $IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection 75 76 # Check if connection succeeded 77 if (-not $IntegrationServices) 78 { 79 Throw [System.Exception] "Failed to connect to server $SsisServer " 80 } 81 else 82 { 83 Write-Host "Connected to server" $SsisServer 84 } 85 86 87 ############################# 88 ########## CATALOG ########## 89 ############################# 90 # Create object for SSISDB Catalog 91 $Catalog = $IntegrationServices.Catalogs["SSISDB"] 92 93 # Check if the SSISDB Catalog exists 94 if (-not $Catalog) 95 { 96 # Catalog doesn't exists. Different name used? 97 Throw [System.Exception] "SSISDB catalog doesn't exist." 98 } 99 else 100 { 101 Write-Host "Catalog SSISDB found" 102 } 103 104 105 ############################ 106 ########## FOLDER ########## 107 ############################ 108 if ($FolderName -ne "") 109 { 110 # Create object to the folder 111 $Folder = $Catalog.Folders[$FolderName] 112 # Check if folder exists 113 if (-not $Folder) 114 { 115 # Folder doesn't exists, so throw error. 116 Write-Host "Folder" $FolderName "not found" 117 Throw [System.Exception] "Aborting, folder not found" 118 } 119 else 120 { 121 Write-Host "Folder" $FolderName "found" 122 } 123 } 124 125 126 ############################# 127 ########## Project ########## 128 ############################# 129 if ($ProjectName -ne "" -and $FolderName -ne "") 130 { 131 $Project = $Folder.Projects[$ProjectName] 132 # Check if project already exists 133 if (-not $Project) 134 { 135 # Project doesn't exists, so throw error. 136 Write-Host "Project" $ProjectName "not found" 137 Throw [System.Exception] "Aborting, project not found" 138 } 139 else 140 { 141 Write-Host "Project" $ProjectName "found" 142 } 143 } 144 145 146 ############################## 147 ########## DOWNLOAD ########## 148 ############################## 149 Function DownloadIspac 150 { 151 Param($DownloadFolder, $Project, $CreateSubfolders, $UnzipIspac) 152 if ($CreateSubfolders) 153 { 154 $DownloadFolder = ($DownloadFolder + $Project.Parent.Name) 155 } 156 157 # Create download folder if it doesn't exist 158 New-Item -ItemType Directory -Path $DownloadFolder -Force > $null 159 160 # Check if new ispac already exists 161 if (Test-Path ($DownloadFolder + $Project.Name + ".ispac")) 162 { 163 Write-Host ("Downloading [" + $Project.Name + ".ispac" + "] to " + $DownloadFolder + " (Warning: replacing existing 164 165 file)") 166 } 167 else 168 { 169 Write-Host ("Downloading [" + $Project.Name + ".ispac" + "] to " + $DownloadFolder) 170 } 171 172 # Download ispac 173 $ISPAC = $Project.GetProjectBytes() 174 [System.IO.File]::WriteAllBytes(($DownloadFolder + "\" + $Project.Name + ".ispac"),$ISPAC) 175 if ($UnzipIspac) 176 { 177 # Add reference to compression namespace 178 Add-Type -assembly "system.io.compression.filesystem" 179 180 # Extract ispac file to temporary location (.NET Framework 4.5) 181 Write-Host ("Unzipping [" + $Project.Name + ".ispac" + "]") 182 183 # Delete unzip folder if it already exists 184 if (Test-Path ($DownloadFolder + "\" + $Project.Name)) 185 { 186 [System.IO.Directory]::Delete(($DownloadFolder + "\" + $Project.Name), $true) 187 } 188 189 # Unzip ispac 190 [io.compression.zipfile]::ExtractToDirectory(($DownloadFolder + "\" + $Project.Name + ".ispac"), ($DownloadFolder + "\" + 191 192 $Project.Name)) 193 194 # Delete ispac 195 Write-Host ("Deleting [" + $Project.Name + ".ispac" + "]") 196 [System.IO.File]::Delete(($DownloadFolder + "\" + $Project.Name + ".ispac")) 197 } 198 Write-Host "" 199 } 200 201 202 ############################# 203 ########## LOOPING ########## 204 ############################# 205 # Counter for logging purposes 206 $ProjectCount = 0 207 208 # Finding projects to download 209 if ($FolderName -ne "" -and $ProjectName -ne "") 210 { 211 # We have folder and project 212 $ProjectCount++ 213 DownloadIspac $DownloadFolder $Project $CreateSubfolders $UnzipIspac 214 } 215 elseif ($FolderName -ne "" -and $ProjectName -eq "") 216 { 217 # We have folder, but no project => loop projects 218 foreach ($Project in $Folder.Projects) 219 { 220 $ProjectCount++ 221 DownloadIspac $DownloadFolder $Project $CreateSubfolders $UnzipIspac 222 } 223 } 224 elseif ($FolderName -eq "" -and $ProjectName -ne "") 225 { 226 # We only have a projectname, so search 227 # in all folders 228 foreach ($Folder in $Catalog.Folders) 229 { 230 foreach ($Project in $Folder.Projects) 231 { 232 if ($Project.Name -eq $ProjectName) 233 { 234 Write-Host "Project" $ProjectName "found in" $Folder.Name 235 $ProjectCount++ 236 DownloadIspac $DownloadFolder $Project $CreateSubfolders $UnzipIspac 237 } 238 } 239 } 240 } 241 else 242 { 243 # Download all projects in all folders 244 foreach ($Folder in $Catalog.Folders) 245 { 246 foreach ($Project in $Folder.Projects) 247 { 248 $ProjectCount++ 249 DownloadIspac $DownloadFolder $Project $CreateSubfolders $UnzipIspac 250 } 251 } 252 } 253 254 ########################### 255 ########## READY ########## 256 ########################### 257 # Kill connection to SSIS 258 $IntegrationServices = $null 259 Write-Host "Finished, total downloads" $ProjectCount
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/5550.html
標籤：SQL Server