SSIS Catelog中的项目太大导致VS导入项目的时候报错Out Of Memory「建议收藏」

SSIS Catelog中的项目太大导致VS导入项目的时候报错Out Of Memory「建议收藏」很苦恼,PROD上的SSIS项目,日积月累的往里部署,加包,也没觉得是个什么问题。 但是今天从需要从PROD上把这个项目中所有的包都down下来,VS居然报错Out Of Memory,无论是直接连接

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

代码100分

 

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
转载请注明出处: https://daima100.com/9341.html

(0)
上一篇 2023-02-10
下一篇 2023-02-10

相关推荐

  • MongoDB(七):聚合aggregate

    MongoDB(七):聚合aggregate1. 聚合aggregate 聚合主要用于计算数据,类似sql中的sum()、avg() 语法: db.集合名称.aggregate([{管道:{表达式}}]) stu准备的数据: db.s

    2022-12-28
    117
  • 【SQL SERVER】锁机制

    【SQL SERVER】锁机制SQL Server中锁机制保证并发情况下的数据访问,开发过程中利用好索引减少数据,能减少数据扫描数据加锁的过程,合理规范使用事务,能减少死锁发生

    2023-02-13
    99
  • SQL Server 2019 深度解读:微软数据平台的野望

    SQL Server 2019 深度解读:微软数据平台的野望本文为笔者在InfoQ首发的原创文章,主要介绍了SQL Server 2019的新特性和产品演进思路。现转载回自己的博客,请大家多多指教。

    2022-12-26
    113
  • Mysql基础04-查询「建议收藏」

    Mysql基础04-查询「建议收藏」关联查询 1、内连接:实现A∩B select 字段列表 from A表 inner join B表 on 关联条件 where 等其他子句; 2、左外连接 #实现查询结果是A select 字段列表

    2022-12-29
    109
  • Python字符串替换方法:轻松修改文本内容

    Python字符串替换方法:轻松修改文本内容在Python中,字符串是一种重要的数据类型。相比较其他的编程语言,Python提供了一个非常强大的字符串处理机制。在日常开发中,我们常常需要对文本内容进行修改。本文将为大家介绍Python的字符串替换方法,帮助大家轻松修改文本内容。

    2024-04-07
    23
  • Python Number Dictionary:实现数字到字符串的转换

    Python Number Dictionary:实现数字到字符串的转换a href=”https://beian.miit.gov.cn/”苏ICP备2023018380号-1/a Copyright www.python100.com .Some Rights Reserved.

    2024-01-18
    58
  • Python开发:构建高效实用的应用程序

    Python开发:构建高效实用的应用程序Python是一种简单易学、功能强大的编程语言,被广泛用于应用程序的开发。Python有大量的第三方库和模块,可以轻松地完成许多任务,如爬虫、数据分析、机器学习、人工智能等。使用Python开发应用程序可以提高程序的开发效率,减少代码量,并增加代码的可读性。

    2024-02-07
    50
  • 数据库性能优化8大通用方案「终于解决」

    数据库性能优化8大通用方案「终于解决」毫不夸张地说,咱们后端工程师,无论在哪家公司,呆在哪个团队,做哪个系统,遇到的第一个让人头疼的问题绝对是数据库性能问题。如果我们有一套成熟的方法论,能让大家快速、准确的去选择出合适的优化方案,我相信能

    2023-05-14
    86

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注