SSIS包作业执行完成后如何查看结果？

Question

我有一个 SSIS 包，它将数据导入 SQL Server 2008 数据库。我已经在 SQL Server 代理中设置了计划作业来运行该包。当我查看历史记录时，我只能看到作业是否运行成功。除此之外我看不到其他消息。

我想知道每当执行作业时会导入多少条记录。我怎样才能监控它？我应该使用 SSIS 包中的附加组件还是在 SQL Server 代理作业设置中设置一些配置？

我在 SQL Server 代理作业设置中找到了一些日志记录工具，但我不确定它是否可以满足我的要求。

Answer 1

如果您已将该包部署到数据库的集成服务目录（而不是从文件系统加载它），您可以轻松获得详细的报告。

在 SQL Server Management Studio 中打开目录节点，右键单击包名称，选择“报表”|“报表”。标准报告|所有执行并查看有关作业及其子组件的每个步骤的详细信息，包括导入的记录。

Answer 2

当 SQL Server 作业历史记录不显示 SSIS 包的输出时，还有另一种方法：使用 DTEXEC 命令行。

（好处：这种方法将作业的输出放在支持它的其他人期望找到的地方：在作业历史记录中。
大包的缺点：如果你有一个很长的SSIS包，有很多任务或组件，还有很多输出，那么作业历史记录会将包输出分成很多行作业历史记录，从而采用前面答案中的方法——日志记录到表中——更易于阅读。）

要在作业的查看历史记录中显示 SSIS 包输出：
(1) 将作业步骤从类型“SQL Server Integration Services Package”更改为“操作系统 (CmdExec)”，
(2) 使用DTEXEC命令行，执行包。

命令行示例：

DTExec /DTS "\MSDB\myPkgName" /DECRYPT pkgPass /MAXCONCURRENT " -1 " /CHECKPOINTING OFF 

请注意，如果 SSIS 包需要 32 位 执行（例如，导出到 Excel 时为 true），则通过完全限定来使用“Program Files (x86)”中的 DTEXEC 实用程序它。例如，SQL Server 应用程序安装在“E:”驱动器上，并且使用 SQL Server 2014：

"E:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\DTExec.exe" /DTS "\MSDB\myPkgName" /DECRYPT pkgPass /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

如果您的 SSIS 包位于文件系统中（作为“.dtsx”文件），则替换“/DTS”与“/文件”。

如果您的 SSIS 包放置在 SSISDB 中（使用从 SQL Server 2012 开始提供的“项目部署模型”，而不是旧的“包部署模型”），则将“/DTS”替换为“/ISSERVER”。

接下来，进入作业步骤的“高级”页面，并确保选中“在历史记录中包含步骤输出”框。

最后，考虑您的作业步骤的“运行方式”：如果您的作业步骤“运行方式”已设置为“SQL Server Integration Services Package”类型的作业步骤上的代理，则您已将该代理设置为对子系统有效SQL Server 集成服务包”。现在，要执行上述命令行，请检查代理的属性，并确保它对于子系统“操作系统 (CmdExec)”也处于活动状态。

MSDN 参考：Sql 代理历史记录上的 SSIS 输出

Answer 3

使用以下过程获取带有执行 ID 的 SSIS 错误

CREATE PROCEDURE [dbo].[get_ssis_status] @EXECUTION_ID INT\n
AS
BEGIN
  SELECT o.operation_id EXECUTION_ID
    ,convert(datetimeoffset,OM.message_time,109) TIME
    ,D.message_source_desc ERROR_SOURCE
    ,OM.message ERROR_MESSAGE
    ,CASE ex.STATUS
        WHEN 4 THEN 'Package Failed'
        WHEN 7 THEN CASE EM.message_type 
            WHEN 120 THEN 'package failed' 
            WHEN 130 THEN 'package failed' ELSE 'Package Succeed'END
        END AS STATUS
FROM SSISDB.CATALOG.operation_messages AS OM
INNER JOIN SSISDB.CATALOG.operations AS O ON O.operation_id = OM.operation_id
INNER JOIN SSISDB.CATALOG.executions AS EX ON o.operation_id = ex.execution_id
INNER JOIN (VALUES (- 1,'Unknown'),(120,'Error'),(110,'Warning'),(130,'TaskFailed')) EM(message_type, message_desc) ON EM.message_type = OM.message_type
INNER JOIN (VALUES 
 (10,'Entry APIs, such as T-SQL and CLR Stored procedures')
,(20,'External process used to run package (ISServerExec.exe)')
,(30,'Package-level objects')
,(40,'Control Flow tasks')
,(50,'Control Flow containers')
,(60,'Data Flow task')
    ) D(message_source_type, message_source_desc) ON D.message_source_type = OM.message_source_type
WHERE ex.execution_id = @EXECUTION_ID
AND OM.message_type IN (120,130,-1);
END

Answer 4

如果您只是想了解正在处理的列，而对进一步使用的信息不感兴趣，一种可能的选择是使用 SSIS 日志记录功能。以下是它如何用于数据流任务。

1. 单击 SSIS 包。
2. 在菜单上，选择“SSIS -->”日志记录...
3. 在“配置 SSIS 日志：”对话框中，选择提供程序类型并单击“添加”。我选择 SQL Server 作为本示例。选中名称复选框并在配置列下提供数据源。这里 SQLServer 是连接管理器的名称。 SSIS 将在您选择的数据库中创建一个名为dbo.sysssislog 的表和存储过程dbo.sp_ssis_addlogentry。请参阅下面的屏幕截图#1。
4. 如果需要处理行，请选中“OnInformation”复选框。在示例中，包执行成功，因此在 OnInformation 下找到日志记录。您可能需要根据您的要求微调此事件选择。请参阅下面的屏幕截图#2。
5. 以下是数据流任务中的示例包执行。请参阅下面的屏幕截图#3。
6. 以下是日志表 dbo.sysssislog 的示例输出。我只显示了 id 和 message 列。表中还有许多其他列。在查询中，我仅过滤名为“Package1”的包和事件“OnInformation”的输出。您可以注意到 ID 为 7、14 和 15 的记录包含已处理的行。请参阅下面的屏幕截图#4。

希望有帮助。

屏幕截图 #1：

屏幕截图 #2：

屏幕截图#3：

屏幕截图 #4：