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