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