VS调用PowerShell执行ETL脚本遇System.OutOfMemoryException问题

通过Visual Studio的Execute Process Task调用PowerShell脚本迁移Oracle数据到SQL Server时，处理1500万行数据出现OutOfMemoryException，但直接在PowerShell/ISE中运行脚本可正常完成。处理500万行数据时VS调用也正常。

• VS中调用的可执行文件：C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
• 脚本逻辑：通过OracleDataAdapter一次性加载所有查询结果到DataTable，再用SqlBulkCopy写入SQL Server
• 涉及字段：quantity为varchar(4000)

• 修改quantity字段类型或移除该字段
• 减小SqlBulkCopy的BatchSize
• 使用不同的PowerShell调用参数（-F直接指定脚本路径）

Exception calling "Fill" with "1" argument(s): "Exception of type 'System.OutOfMemoryException' was thrown."
At C:\Users\Harry\Import.ps1:14 char:1
+ $adapter.Fill($tbl);
+ ~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OutOfMemoryException

1. 切换为64位PowerShell进程 #

VS默认以32位模式运行Execute Process Task，而C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe在32位进程中会被重定向到32位版本（位于SysWOW64目录），32位进程的内存上限约2GB，不足以承载1500万行数据的内存开销。

修改VS中Execute Process Task的可执行文件路径为：

C:\Windows\Sysnative\WindowsPowerShell\v1.0\powershell.exe

Sysnative是64位系统中供32位进程访问64位系统文件的虚拟目录，这样就能调用64位PowerShell，获得更大的内存可用空间。

2. 分批读取数据，避免一次性加载全量数据 #

原脚本通过OracleDataAdapter.Fill一次性将所有数据加载到DataTable，即使SqlBulkCopy设置了BatchSize，也无法降低内存占用。改为使用OracleDataReader逐批读取并写入，内存占用会大幅降低。

修改后的脚本：

Add-Type -Path 'C:\Oracle\Client\ODP.NET\managed\common\Oracle.ManagedDataAccess.dll'

# Oracle连接配置
$oracleConnString = "User Id=Oracle_Net;Password=*****;Data Source=Nexus;Connection Timeout=0"
$query = @"
SELECT Cust_id, Relation_id, Order_id, quantity
FROM Cust_Order ob
WHERE Pay_method = '110'
AND Set = 0
"@

# SQL Server连接配置
$sqlConnString = "server=Customer_Nexus;database=Cust_Order;trusted_connection=true;Connection Timeout=0"
$batchSize = 10000
$destinationTable = "dbo.T_Order_Intake"

# 创建Oracle连接和命令
$oracleConn = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($oracleConnString)
$oracleCmd = $oracleConn.CreateCommand()
$oracleCmd.CommandText = $query

try {
    $oracleConn.Open()
    $reader = $oracleCmd.ExecuteReader()

    # 初始化SqlBulkCopy，启用批量写入
    using ($sqlBulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($sqlConnString)) {
        $sqlBulkCopy.DestinationTableName = $destinationTable
        $sqlBulkCopy.BatchSize = $batchSize
        $sqlBulkCopy.BulkCopyTimeout = 0

        # 直接通过DataReader写入，无需加载全量数据到内存
        $sqlBulkCopy.WriteToServer($reader)
    }
}
catch {
    Write-Error "执行出错: $_"
}
finally {
    # 清理资源
    if ($reader) { $reader.Close() }
    if ($oracleConn) { $oracleConn.Close() }
}

脚本说明 #

• 使用OracleDataReader流式读取数据，不会将全量数据加载到内存
• SqlBulkCopy.WriteToServer支持直接接收DataReader作为数据源，实现边读边写
• 添加try/catch/finally确保资源正确释放

3. 额外优化建议 #

• 确保SQL Server目标表的字段类型与Oracle源表匹配，避免不必要的类型转换开销
• 如果quantity字段实际存储的是数值，可在查询中直接转换为数值类型（如TO_NUMBER(quantity)），减少内存占用
• 若仍有内存压力，可进一步减小batchSize值

内容的提问来源于stack exchange，提问作者HarryS