SQL Server 2019标准版提取Oracle数据的可行方案咨询

1. 修正SSIS包配置（无需VS） #

• 验证Oracle驱动兼容性：确保服务器安装的Oracle ODAC/ODBC驱动版本与SQL Server 2019、SSIS运行时匹配，优先选用ODP.NET或ODBC驱动，避开仅企业版支持的组件
• 手动修改包连接字符串：将开发环境的.dtsx包文件复制到服务器后，用文本编辑器打开，替换Oracle连接配置为服务器可用的驱动格式（例如ODBC连接字符串）
• 用dtutil命令行调整配置：执行命令修改包内连接属性，无需VS：

  dtutil /FILE "C:\YourPackage.dtsx" /SET \Package.Connections[OracleConn].Properties[ConnectionString];'DRIVER={Oracle in OraClient19Home1};DBQ=ORCL;UID=yourUser;PWD=yourPass'
  

• 检查权限：确保执行SSIS包的账户拥有Oracle读权限、SQL Server目标表写权限

2. 链接服务器+T-SQL脚本 #

• 配置Oracle ODBC驱动：在SQL Server服务器安装Oracle ODBC驱动，创建系统DSN
• 创建链接服务器：执行T-SQL完成配置：

  EXEC sp_addlinkedserver 
      @server = 'ORACLE_LINK',
      @srvproduct = 'Oracle',
      @provider = 'MSDASQL',
      @datasrc = '你的Oracle系统DSN名称';
  
  EXEC sp_addlinkedsrvlogin 
      @rmtsrvname = 'ORACLE_LINK',
      @useself = 'FALSE',
      @rmtuser = 'Oracle用户名',
      @rmtpassword = 'Oracle密码';
  

• 编写同步脚本：用OPENQUERY拉取Oracle数据并插入SQL Server表：

  INSERT INTO SQLServerTargetTable (Col1, Col2)
  SELECT Col1, Col2 FROM OPENQUERY(ORACLE_LINK, 'SELECT Col1, Col2 FROM OracleSourceTable');
  

• 自动执行：将脚本封装为SQL Server代理作业，设置定时调度

3. PowerShell脚本同步（无额外许可证） #

• 部署Oracle数据访问组件：在服务器安装ODP.NET Core或ODBC驱动，确保PowerShell可调用
• 编写同步脚本示例：

  # 连接Oracle
  $oracleConnStr = "Data Source=ORCL;User Id=yourUser;Password=yourPass;"
  $oracleConn = New-Object Oracle.DataAccess.Client.OracleConnection($oracleConnStr)
  $oracleConn.Open()
  
  # 查询数据
  $query = "SELECT Col1, Col2 FROM OracleSourceTable"
  $command = New-Object Oracle.DataAccess.Client.OracleCommand($query, $oracleConn)
  $reader = $command.ExecuteReader()
  
  # 连接SQL Server并插入
  $sqlConnStr = "Server=.;Database=YourDB;Integrated Security=True;"
  $sqlConn = New-Object System.Data.SqlClient.SqlConnection($sqlConnStr)
  $sqlConn.Open()
  $sqlCmd = New-Object System.Data.SqlClient.SqlCommand("INSERT INTO TargetTable (Col1, Col2) VALUES (@Col1, @Col2)", $sqlConn)
  $sqlCmd.Parameters.Add("@Col1", [System.Data.SqlDbType]::NVarChar)
  $sqlCmd.Parameters.Add("@Col2", [System.Data.SqlDbType]::Int)
  
  while ($reader.Read()) {
      $sqlCmd.Parameters["@Col1"].Value = $reader["Col1"]
      $sqlCmd.Parameters["@Col2"].Value = $reader["Col2"]
      $sqlCmd.ExecuteNonQuery()
  }
  
  # 关闭连接
  $reader.Close()
  $oracleConn.Close()
  $sqlConn.Close()
  

• 自动执行：将脚本保存为.ps1文件，通过Windows任务计划定时运行，确保执行账户权限足够

4. 批量导出导入工具 #

• Oracle端导出：用expdp命令导出数据（支持DMP或CSV格式）：

  expdp yourUser/yourPass@ORCL tables=OracleSourceTable directory=DATA_PUMP_DIR dumpfile=oracle_data.dmp logfile=export.log
  

• SQL Server端导入：用BULK INSERT或bcp命令导入文件：

  BULK INSERT SQLServerTargetTable
  FROM 'C:\oracle_data.csv'
  WITH (
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n',
      FIRSTROW = 2
  );
  

• 自动串联：通过Windows任务计划按顺序执行导出、导入脚本

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