SSIS 中的批量插入任务

SSIS 中的批量插入任务只能将数据从文本文件传输到表或视图，这与 SQL Server 中的批量插入类似。如果目标表或视图已包含数据，则在 SSIS 批量插入任务运行时，新数据将追加到现有数据中。如果要替换数据，请在运行批量插入任务之前运行一个执行 SQL 任务，该任务运行 DELETE 或 TRUNCATE 语句。

例如，我们正在处理股票市场数据，每天都会收到 TB 级别的 .csv 格式（逗号分隔值）数据。我们的任务是每天将此 .csv 文件中的数据复制到 SQL 数据库表中。我们通常有两种方法在 SSIS 中执行批量插入任务。

  • 拖放数据流任务，然后在数据流中拖放平面文件源和 OLE DB 目标，并复制数据。如果您想执行任何 SSIS 转换，这种方法非常有用。
  • 使用 SSIS 批量插入任务。与前一种方法相比，这种方法更强大，因为 批量插入任务 内部使用批量复制（BCP）操作（在 SQL Server 中速度非常快）。

SSIS 中批量插入任务的可用选项

单击批量插入任务中的“选项”选项卡将显示以下选项：

  • CodePage：指定数据文件中数据的代码页。通常用于其他语言。
  • DataFileType：指定在加载操作中使用的数据类型值。
  • BatchSize：指定批次中的行数。默认值为整个数据文件。如果将 BatchSize 设置为零，则数据将加载到单个批次中。例如，如果我们将批次大小设置为 100，则每个批次就像一个事务，如果任务在一段时间后失败，则成功加载的批次不会回滚。
  • LastRow：指定要复制的最后一行。
  • FirstRow：指定从中开始复制的第一行。
  • SortedData：在批量插入语句中指定 ORDER BY 子句。默认为 false。
  • MaxErrors：指定在批量插入操作被取消之前可以发生的错误的最大数量。值为 0 表示允许无限数量的错误。
Bulk Insert Task in SSIS 0
选项术语定义
检查约束检查列数据。
保留 NULL 值选择在批量插入操作期间保留 NULL 值，而不是为空列插入任何默认值。
启用标识插入选择将现有值插入标识列。
表锁定选择在批量插入期间锁定表。
触发触发器选择触发表上的任何插入、更新或删除触发器。

SSIS 批量插入任务示例

在此批量插入任务示例中，我们将把本地驱动器上的文本文件复制到目标。

我们有一个名为 GEOGRAPHY.txt 的平面文件，包含 1000000 行。我们的任务是使用 SSIS 中的批量插入任务插入所有行。

将 SSIS 批量插入任务从工具箱拖放到“控制流”区域。

Bulk Insert Task in SSIS 2

双击批量插入任务以配置源和目标连接字符串。在“常规”选项卡中，我们可以更改名称和描述。

现在单击“连接”选项卡以设置源和目标连接。首先，我们需要配置源连接，因此请选择文件选项。

注意：我们必须提供凭据才能访问本地或远程的文本文件。

Bulk Insert Task in SSIS 4

通过单击“新建连接”，我们可以选择要使用的源文本文件。单击“浏览”按钮选择文本文件。这里我们选择 GEOGRAPHY 文本文件，大小为 92,562 KB。

单击“确定”完成 SSIS 批量插入任务源配置。

行分隔符属性选项将为 {CR}{LF}（回车符）。列分隔符属性将设置为逗号 {‘}，因为在我们的平面文件中，列由逗号分隔。

Bulk Insert Task in SSIS 6

现在，通过单击“<新建连接>”来配置目标连接。连接是指选择数据库名称。这里我们选择以下数据库作为目标数据库。

现在，通过从列表中选择来配置 SSIS 批量插入任务目标表。这里我们选择 GEOGRAPHY 表作为目标表。

提示：目标表应在服务器中预先设计好。

Bulk Insert Task in SSIS 7

注意：批量插入任务不会记录导致错误的行。如果您想将错误记录写入错误文件或表，最好使用数据流任务。

单击“选项”

Bulk Insert Task in SSIS 8

我们已经在开头解释了每个选项的属性。暂时，我们保留默认设置，但对于“第一行”，我们将值更改为 2。因为您可以观察到数据，第一行是列名。

单击“确定”完成 SSIS 批量插入任务配置并运行包。

Bulk Insert Task in SSIS 9

让我们打开 Management Studio 并编写以下命令并检查结果。

SELECT [CountryRegionCode] AS [Country Code]
      ,[EnglishCountryRegionName] AS [Country]
      ,[StateProvinceCode] AS [State Code]
      ,[StateProvinceName] AS [State]
      ,[City]
      ,[PostalCode]
      ,[UnitPrice]
      ,[ExtendedAmount] AS [ExtAmount]
      ,[ProductStandardCost] AS [StandardCost]
      ,[TotalProductCost] AS [ProductCost]
      ,[SalesAmount] AS [Sales]
      ,[TaxAmt]
  FROM [DimGeography]

SSIS 批量插入任务中需要注意的点

  • SSIS 中的批量插入任务只能将数据从文本文件传输到 表 或 视图。
  • 批量插入任务支持平面文件连接管理器来选择文本文件。
  • 批量插入任务仅支持 OLE DB 连接管理器 作为目标数据库。
  • 在使用批量插入任务之前，目标表必须存在。
  • 如果文本文件的第一行有列名，请不要忘记将“第一行”选项更改为 2。
  • 为大量数据设置批次大小插入始终是一个好习惯。

评论已关闭。