SQL Server Integration Services (SSIS) 15 项最佳实践

引言

以下列出了一些 SQL Server Integration Services (SSIS) 的最佳实践：

1. 保持简单。

避免不必要地使用组件。例如：

• 步骤 1. 声明变量 varServerDate。
• 步骤 2. 在控制流中使用 ExecuteSQLTask 执行 SQL 查询以获取服务器日期/时间并将其存储在变量中。
• 步骤 3. 使用数据流任务，使用变量 varServerDate 中的服务器日期/时间插入/更新数据库。

仅当从步骤 2 到步骤 3 的时间差真正重要时，才建议采用此顺序。如果不重要，那么只需在步骤 3 中使用 getdate() 命令，如下所示：

--create table #table1 (Lap_Id int, LAP_Date datetime)
Insert into #table1 (Lap_Id, LAP_Date) values (1, getdate())

2. 使用不同的参数值从父级多次调用子包。

当子包从主包执行时，需要为主包传递的参数在子包中进行配置。为此，您可以使用子包中的“父包配置”选项。但是，要使用“父包配置”，您需要指定传递给子包的“父包变量”的名称。如果您想多次调用同一个子包（每次使用不同的参数值），请声明父包变量（名称与子包中给出的名称相同），并将范围限定为“执行包任务”。

SSIS 允许声明具有相同名称但范围限定为不同任务的变量——所有这些都在同一个包中！

3. 具有多个原子步骤的 SQL 作业.

对于调用 SSIS 包的 SQL 作业，创建多个步骤，每个步骤执行一个小任务，而不是一个步骤执行所有任务。在第一种情况下，事务日志会变得太大，如果发生回滚，可能会占用服务器的全部处理空间。

4. 避免不必要的类型转换。

避免不必要的类型转换。例如，平面文件连接管理器默认情况下使用字符串 [DT_STR] 数据类型处理所有列。如果您想使用实际的数据类型，则必须手动更改。最好在源级别就进行更改，以避免不必要的类型转换。

5. 事务。

通常，ETL 进程处理大量数据。在这种情况下，请勿尝试对整个包逻辑进行事务处理。但是，SSIS 支持事务，并且建议在事务的原子性得到保证的情况下使用事务。

例如，考虑一个场景，其中一个源记录需要拆分为目标中的 25 条记录——要么所有 25 条记录都到达目的地，要么为零。在这种情况下，通过使用事务，我们可以确保所有 25 条记录都到达目的地，或者为零。

6. 跨多个任务的分布式事务。

SSIS 包的控制流将各种控制任务线程连接在一起。可以使用相同的连接使事务跨多个任务。要启用此功能，连接管理器的“retainsameconnection”属性应设置为“True”。

7. 将包名称限制为最多 100 个字符。

当一个包名称超过 100 个字符的 SSIS 包部署到 SQL Server 时，它会将包名称截断为 100 个字符，这可能导致执行失败。因此，将包名称限制为最多 100 个字符。

8. Select * from…

确保您没有将任何不必要的列从源传递到下游。对于 OLEDB 连接管理器源，使用“表或视图”数据访问模式等同于“SELECT * FROM <TABLE_NAME>”，这将获取所有列。使用“SQL 命令”仅获取必需的列，并将其传递到下游。在每个下游组件上，过滤掉不必要的列。

9. 排序。

SSIS 中的排序是一项耗时的操作。在已知数据来自数据库表的地方，最好在数据库查询本身中执行排序操作。

10. Excel 源和 64 位运行时。

Excel 源或 Excel 连接管理器仅适用于 32 位运行时。当使用 Excel 源的包启用 64 位运行时（默认启用）时，它将在使用 64 位运行时的生产服务器上失败。转到解决方案属性页 \ 调试，并将 Run64BitRuntime 设置为 False。

11. 组件失败时，停止/继续与下一个组件的执行。

当组件失败时，属性 failParentonFailure 可有效地用于停止包执行或继续与下一个组件的执行——例外是，在序列容器中停止/继续与下一个组件的执行。连接序列容器中组件的约束的值应设置为“Completion”，并且 failParentonFailure 属性应设置为 False（默认值）。

12. 保护.

为避免从一个系统到另一个系统的多数包部署错误，请将包保护级别设置为“DontSaveSenstive”。

13. 复制粘贴脚本组件。

复制粘贴脚本组件后执行包可能会失败。只需打开粘贴的脚本组件的脚本编辑器，保存脚本，然后执行包——它就会正常工作。

14. 配置筛选器 – 用作筛选器。

对于特定于包的所有配置项，将包名称用作配置筛选器是一项最佳实践。当有许多包具有特定于包的配置项时，这尤其有用。对于许多包通用的配置项，请使用通用名称。

15. 最优地使用配置记录。

避免在不同的筛选器/对象名称下记录相同的配置项。例如，如果两个包使用相同的连接字符串，您只需要一条配置记录。为此，请在两个包中使用相同的连接管理器名称。另外，请使用通用的配置筛选器。这在从一个环境迁移到另一个环境（例如：从 UAT 到生产）时非常方便。