SSIS无法保存包并重新启动Visual Studio [英] SSIS failing to save packages and reboots Visual Studio

问题描述

这是我第一次使用SSIS，所以请多多包涵... 我正在使用SSIS将表从Oracle迁移到SSMS，我尝试转移一些非常大的表(超过5000万行).现在，当我只是想保存软件包(甚至不运行它)时，SSIS完全冻结并重新启动VS.它总是返回内存不足的错误，但是，我正在使用运行此程序包所需的RAM足够多的远程服务器.

This is my first experience with SSIS so bear with me... I am using SSIS to migrate tables from Oracle to SSMS, there are some very large tables I am trying to transfer (50 million rows +). SSIS is now completely freezing up and rebooting VS when I am just trying to save the package (not even running it). It keeps returning errors of insufficient memory, however, I am working on a remote server that has well over the RAM it takes to run this package.

尝试保存时出现错误消息

我唯一能想到的是，当该程序包尝试运行时，我的以太网Kbps在该程序包启动时就通过屋顶.也许需要更新我的管道?

The only thing I can think of is when this package is attempting to run, my Ethernet Kbps are through the roof right as the package starts. Maybe need to update my pipeline?

以太网图

此外，由于BYTE大小(同样，并非几乎使用服务器上的所有内存)，导入时我最大的表将失败.我们正在使用ODBC Source，因为这是我们能够使其他大型表上传超过100万行的唯一方法.

Also, my largest table will fail when importing due to BYTE sizes (again, not nearly using all the memory on the server). We are using ODBC Source as this was the only way we were able to get other large tables to upload more than 1 million rows.

我尝试创建一个临时缓冲区文件来缓解内存压力，但是没有任何变化.我已经将AutoAdjustBufferSize更改为True，结果没有变化.也更改了DefaultBufferMaxRows和DefaultBufferSize ..不变.

I have tried creating a temporary buffer file to help with memory pressure, but that had no changes. I have changed the AutoAdjustBufferSize to True, no change in results. also changed DefaultBufferMaxRows and DefaultBufferSize.. no change.

运行大表时出错:

信息:SSIS上SRC_STG_ TABLENAME 处的0x4004300C.Pipeline:执行 阶段开始了.

Information: 0x4004300C at SRC_STG_TABLENAME, SSIS.Pipeline: Execute phase is beginning.

信息:SRC_STG_ TABLENAME 处的0x4004800D:缓冲区管理器 未能进行810400000字节的内存分配调用，但无法执行 换出任何缓冲区以减轻内存压力.分别有2个缓冲区 考虑过，有2人被锁定.

Information: 0x4004800D at SRC_STG_TABLENAME: The buffer manager failed a memory allocation call for 810400000 bytes, but was unable to swap out any buffers to relieve memory pressure. 2 buffers were considered and 2 were locked.

没有足够的内存可用于管道，因为没有 已经安装了足够的东西，其他进程正在使用它，或者太多 缓冲区被锁定.

Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.

信息:SRC_STG_ TABLENAME 处的0x4004800F:缓冲区管理器 在2个物理缓冲区中分配了1548 MB.

Information: 0x4004800F at SRC_STG_TABLENAME: Buffer manager allocated 1548 megabyte(s) in 2 physical buffer(s).

信息:SRC_STG_ TABLENAME 处的0x40048010:组件"ODBC 源"(60)拥有775兆字节的物理缓冲区.

Information: 0x40048010 at SRC_STG_TABLENAME: Component "ODBC Source" (60) owns 775 megabyte(s) physical buffer.

信息:SRC_STG_ TABLENAME 处的0x4004800D:缓冲区管理器 未能进行810400000字节的内存分配调用，但无法执行 换出任何缓冲区以减轻内存压力.分别有2个缓冲区 考虑和2被锁定.

Information: 0x4004800D at SRC_STG_TABLENAME: The buffer manager failed a memory allocation call for 810400000 bytes, but was unable to swap out any buffers to relieve memory pressure. 2 buffers were considered and 2 were locked.

没有足够的内存可用于管道，因为 已经安装了足够的东西，其他进程正在使用它，或者太多 缓冲区被锁定.

Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.

信息:SRC_STG_ TABLENAME 处的0x4004800F:缓冲区管理器 在2个物理缓冲区中分配了1548 MB.

Information: 0x4004800F at SRC_STG_TABLENAME: Buffer manager allocated 1548 megabyte(s) in 2 physical buffer(s).

信息:SRC_STG_ TABLENAME 处的0x40048010:组件"ODBC 源"(60)拥有775兆字节的物理缓冲区.

Information: 0x40048010 at SRC_STG_TABLENAME: Component "ODBC Source" (60) owns 775 megabyte(s) physical buffer.

信息:SRC_STG_ TABLENAME 处的0x4004800D:缓冲区管理器 未能进行810400000字节的内存分配调用，但无法执行 换出任何缓冲区以减轻内存压力.分别有2个缓冲区 考虑和2被锁定.

Information: 0x4004800D at SRC_STG_TABLENAME: The buffer manager failed a memory allocation call for 810400000 bytes, but was unable to swap out any buffers to relieve memory pressure. 2 buffers were considered and 2 were locked.

没有足够的内存可用于管道，因为 已经安装了足够的东西，其他进程正在使用它，或者太多 缓冲区被锁定.

Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.

信息:SRC_STG_ TABLENAME 处的0x4004800F:缓冲区管理器 在2个物理缓冲区中分配了1548 MB.

Information: 0x4004800F at SRC_STG_TABLENAME: Buffer manager allocated 1548 megabyte(s) in 2 physical buffer(s).

信息:SRC_STG_ TABLENAME 处的0x40048010:组件"ODBC 源"(60)拥有775兆字节的物理缓冲区.

Information: 0x40048010 at SRC_STG_TABLENAME: Component "ODBC Source" (60) owns 775 megabyte(s) physical buffer.

错误:SRC_STG_ TABLENAME 处错误0xC0047012: 分配810400000字节.

Error: 0xC0047012 at SRC_STG_TABLENAME: A buffer failed while allocating 810400000 bytes.

错误:SRC_STG_ TABLENAME 处出现0xC0047011:系统报告26 内存负载百分比.物理内存有68718940160字节 可用50752466944字节.有4294836224字节的虚拟 可用914223104字节的内存.分页文件具有84825067520 字节和61915041792字节可用.

Error: 0xC0047011 at SRC_STG_TABLENAME: The system reports 26 percent memory load. There are 68718940160 bytes of physical memory with 50752466944 bytes free. There are 4294836224 bytes of virtual memory with 914223104 bytes free. The paging file has 84825067520 bytes with 61915041792 bytes free.

信息:SRC_STG_ TABLENAME 处的0x4004800F:缓冲区管理器 在2个物理缓冲区中分配了1548 MB.

Information: 0x4004800F at SRC_STG_TABLENAME: Buffer manager allocated 1548 megabyte(s) in 2 physical buffer(s).

信息:SRC_STG_ TABLENAME 处的0x40048010:组件"ODBC 源"(60)拥有775兆字节的物理缓冲区.

Information: 0x40048010 at SRC_STG_TABLENAME: Component "ODBC Source" (60) owns 775 megabyte(s) physical buffer.

错误:SRC_STG_ TABLENAME 处为0x279，ODBC源[60]:添加失败 行到输出缓冲区.

Error: 0x279 at SRC_STG_TABLENAME, ODBC Source [60]: Failed to add row to output buffer.

错误:SRC_STG_ TABLENAME 处为0x384，ODBC源[60]:打开数据库 发生连接(ODBC)错误.

Error: 0x384 at SRC_STG_TABLENAME, ODBC Source [60]: Open Database Connectivity (ODBC) error occurred.

错误:SSIS的SRC_STG_ TABLENAME 处的错误:0xC0047038 代码DTS_E_PRIMEOUTPUTFAILED. ODBC源上的PrimeOutput方法 返回错误代码0x80004005.组件返回了失败代码 当管道引擎调用PrimeOutput()时.的意思 故障代码由组件定义，但错误是致命的，并且 管道停止执行.可能发布了错误消息 在此之前，有关失败的更多信息.

Error: 0xC0047038 at SRC_STG_TABLENAME, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on ODBC Source returned error code 0x80004005. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

这真的阻碍了我的工作.帮助！

This is really holding up my work. HELP!

推荐答案

我建议分块读取数据:

而不是加载整个表，而是尝试将数据拆分为大块并将其导入到SQL Server.一段时间以来，我

I suggest reading data in chunks:

Instead of loading the whole table, try to split the data into chunks and import them to SQL Server. From a while, I answered a similar answer related to SQLite, i will try to reproduce it to fit the Oracle syntax:

在此示例中，每个块包含10000行.

1. 声明2个类型为Int32(@[User::RowCount]和@[User::IncrementValue])的变量
2. 添加执行select Count(*)命令的Execute SQL Task并将结果集存储到变量@[User::RowCount]
中

1. Declare 2 Variables of type Int32 (@[User::RowCount] and @[User::IncrementValue])
2. Add an Execute SQL Task that execute a select Count(*) command and store the Result Set into the variable @[User::RowCount]

1. 添加具有以下首选项的For循环:

1. 在for循环容器内添加Data flow task
2. 在数据流任务中添加ODBC Source和OLEDB Destination
3. 在ODBC Source中，选择SQL Command选项并编写一个SELECT * FROM TABLE查询*(仅检索元数据`
4. 在源和目标之间映射列
5. 返回Control flow并单击Data flow task并按 F4 以查看属性窗口

6. 在属性窗口中，转到expression并将以下表达式分配给[ODBC Source].[SQLCommand]属性:(有关更多信息，请参考

1. Inside the for loop container add a Data flow task
2. Inside the dataflow task add an ODBC Source and OLEDB Destination
3. In the ODBC Source select SQL Command option and write a SELECT * FROM TABLE query *(to retrieve metadata only`
4. Map the columns between source and destination
5. Go back to the Control flow and click on the Data flow task and hit F4 to view the properties window

6. In the properties window go to expression and Assign the following expression to [ODBC Source].[SQLCommand] property: (for more info refer to How to pass SSIS variables in ODBC SQLCommand expression?)

"SELECT * FROM MYTABLE ORDER BY ID_COLUMN
OFFSET " + (DT_WSTR,50)@[User::IncrementValue] + "FETCH NEXT 10000 ROWS ONLY;"

其中MYTABLE是源表名称，而IDCOLUMN是主键或标识列.

Where MYTABLE is the source table name, and IDCOLUMN is your primary key or identity column.

控制流屏幕截图

• ODBC源-SQL Server
• 如何在ODBC SQLCommand表达式中传递SSIS变量? /a>
• 如何限制订购后Oracle查询返回的行数?
• 从db2获取前n至n行

• ODBC Source - SQL Server
• How to pass SSIS variables in ODBC SQLCommand expression?
• HOW TO USE SSIS ODBC SOURCE AND DIFFERENCE BETWEEN OLE DB AND ODBC?
• How do I limit the number of rows returned by an Oracle query after ordering?
• Getting top n to n rows from db2

在搜索类似问题时，我发现了一些可以尝试的其他解决方法:

While searching for similar issues i found some additional workarounds that you can try:

(1)更改SQL Server最大内存

• SSIS:缓冲区管理器无法进行内存分配调用

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

(2)启用命名管道

• 已修复]缓冲区管理器检测到系统虚拟内存不足，但无法换出任何缓冲区

1. 转到控制面板->管理工具->计算机管理
2. 关于SQL实例的协议->设置命名管道= Enabled
3. 重新启动SQL实例服务
4. 之后，尝试导入数据，它将立即以块的形式获取数据，而不是一次全部获取.希望对您有用，并节省您的时间.

1. Go to Control Panel – > Administrative Tools -> Computer Management
2. On Protocol for SQL Instance -> Set Named Pipes = Enabled
3. Restart the SQL instance Service
4. After that try to import the data and it will fetch the data in chunks now instead of fetch all at once. Hope that will work for you guys and save your time.

(3)如果使用SQL Server 2008安装修补程序

• 在以下

  In the following MSDN link, the error cause was described as following:

  虚拟内存是物理内存的超集. Windows中的进程通常不指定要使用的进程，因为这将(极大地)抑制Windows如何进行多任务处理. SSIS分配虚拟内存.如果Windows能够，则所有这些分配都保存在物理内存中，在物理内存中访问速度更快.但是，如果SSIS请求的内存大于实际可用的内存，则该虚拟内存会溢出到磁盘，从而使程序包的运行速度降低几个数量级.而且在最坏的情况下，如果系统中没有足够的虚拟内存，则该软件包将失败.

  Virtual memory is a superset of physical memory. Processes in Windows typically do not specify which they are to use, as that would (greatly) inhibit how Windows can multitask. SSIS allocates virtual memory. If Windows is able to, all of these allocations are held in physical memory, where access is faster. However, if SSIS requests more memory than is physically available, then that virtual memory spills to disk, making the package operate orders of magnitude slower. And in worst cases, if there is not enough virtual memory in the system, then the package will fail.

  这篇关于SSIS无法保存包并重新启动Visual Studio的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！