SSISCatelog中的项目太大导致VS导入项目的时候报错OutOfMemory
很苦恼，PROD上的SSIS项目，日积月累的往里部署，加包，也没觉得是个什么问题。
但是今天从需要从PROD上把这个项目中所有的包都down下来，VS居然报错Out Of Memory，无论是直接连接SSIS Catelog或者用SSMS导出ispac均报错。
解决方法很简单，使用PowerShell脚本，直接把ispac download下来然后解压缩，这样里面就直接可以看到.dtsx文件了。
核心就在于参数UnzipIspac，一定要是True，这样执行完成后就能直接看见.dtsx包了。
PS脚本：
1#PowerShell: DownloadIspac.ps12################################
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#################################################
17clear
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##########################################
45if ($SsisServer-eq"")
46{
47Throw [System.Exception] "SsisServer parameter is mandatory"
48}
49if ($DownloadFolder-eq"")
50{
51Throw [System.Exception] "DownloadFolder parameter is mandatory"
52}
53elseif (-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
77if (-not$IntegrationServices)
78{
79Throw [System.Exception] "Failed to connect to server $SsisServer "
80}
81else
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
94if (-not$Catalog)
95{
96# Catalog doesn"t exists. Different name used?
97Throw [System.Exception] "SSISDB catalog doesn"t exist."
98}
99else
100{
101 Write-Host "Catalog SSISDB found"
102}
103
104
105############################
106########## FOLDER ##########
107############################
108if ($FolderName-ne"")
109{
110# Create object to the folder
111$Folder = $Catalog.Folders[$FolderName]
112# Check if folder exists
113if (-not$Folder)
114 {
115# Folder doesn"t exists, so throw error.
116 Write-Host "Folder"$FolderName"not found"
117Throw [System.Exception] "Aborting, folder not found"
118 }
119else
120 {
121 Write-Host "Folder"$FolderName"found"
122 }
123}
124
125
126#############################
127########## Project ##########
128#############################
129if ($ProjectName-ne""-and$FolderName-ne"")
130{
131$Project = $Folder.Projects[$ProjectName]
132# Check if project already exists
133if (-not$Project)
134 {
135# Project doesn"t exists, so throw error.
136 Write-Host "Project"$ProjectName"not found"
137Throw [System.Exception] "Aborting, project not found"
138 }
139else
140 {
141 Write-Host "Project"$ProjectName"found"
142 }
143}
144
145
146##############################
147########## DOWNLOAD ##########
148##############################
149Function DownloadIspac
150{
151Param($DownloadFolder, $Project, $CreateSubfolders, $UnzipIspac)
152if ($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
161if (Test-Path ($DownloadFolder + $Project.Name + ".ispac"))
162 {
163 Write-Host ("Downloading [" + $Project.Name + ".ispac" + "] to " + $DownloadFolder + " (Warning: replacing existing
164
165file)")
166 }
167else
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)
175if ($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
184if (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
209if ($FolderName-ne""-and$ProjectName-ne"")
210{
211# We have folder and project
212$ProjectCount++
213 DownloadIspac $DownloadFolder$Project$CreateSubfolders$UnzipIspac
214}
215elseif ($FolderName-ne""-and$ProjectName-eq"")
216{
217# We have folder, but no project => loop projects
218foreach ($Projectin$Folder.Projects)
219 {
220$ProjectCount++
221 DownloadIspac $DownloadFolder$Project$CreateSubfolders$UnzipIspac
222 }
223}
224elseif ($FolderName-eq""-and$ProjectName-ne"")
225{
226# We only have a projectname, so search
227# in all folders
228foreach ($Folderin$Catalog.Folders)
229 {
230foreach ($Projectin$Folder.Projects)
231 {
232if ($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}
241else
242{
243# Download all projects in all folders
244foreach ($Folderin$Catalog.Folders)
245 {
246foreach ($Projectin$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
以上是 SSISCatelog中的项目太大导致VS导入项目的时候报错OutOfMemory 的全部内容， 来源链接： utcz.com/z/532843.html
