SSIS Catelog中的项目太大导致VS导入项目的时候报错Out Of Memory

很苦恼，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