如何在使用DTEXEC.EXE命令行实用程序时获取SSIS包的实际执行时间

Question

我正在使用DTEXEC.EXE执行一个SSIS包，如下所示

C:\Program Files\Microsoft SQL Server\140\DTS\Binn>DTExec.exe/Server localhost/ISServer"\MyServer\mypackage.dtsx">

执行命令后，将显示以下详细信息。

Started:  3:28:09 PM
Execution ID: 41165.
To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report
Started:  3:28:09 PM
Finished: 3:28:09 PM
Elapsed:  0.172 seconds

包的实际执行时间为20分钟，但"运行时间"显示为0.172秒。在使用命令行运行包时，是否有获取实际执行时间的选项？

提前感谢

Answer 1

从DTEXEC运行SSIS包时，它们将以同步执行模式运行除非，否则要求从SSISDB运行它们。然后，您需要在/Par "$ServerOption::SYNCHRONIZED(Boolean)";True的DTEXEC调用中添加一个附加参数

我创建了一个显式等待延迟15秒的包，并在我的机器上运行了两次。

C:Usersbillinkc>dtexec /server .dev2017 /ISServer "ssisdbSoJustWaitPackage.dtsx"
Microsoft (R) SQL Server Execute Package Utility
Version 14.0.3037.1 for 32-bit
Copyright (C) 2017 Microsoft. All rights reserved.
Started:  7:41:06 AM
Execution ID: 161421.
To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report
Started:  7:41:06 AM
Finished: 7:41:07 AM
Elapsed:  0.141 seconds
C:Usersbillinkc>dtexec /server .dev2017 /ISServer "ssisdbSoJustWaitPackage.dtsx" /Par "$ServerOption::SYNCHRONIZED(Boolean)";True
Microsoft (R) SQL Server Execute Package Utility
Version 14.0.3037.1 for 32-bit
Copyright (C) 2017 Microsoft. All rights reserved.
Started:  7:41:12 AM
Execution ID: 161422.
To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report
Started:  7:41:12 AM
Finished: 7:41:30 AM
Elapsed:  18.39 seconds

第一个是默认执行，它不需要时间，因为它将责任交给了SQL Server本身。第二个迫使我们实时获取消息，因此，15秒+设置时间

Answer 2

上面显示的是加载和执行包的实际时间。包的运行时就是您要查找的。

在审计表中写入行是一种常见的做法，您可以通过写入包id&currenttime，name和您想要的其他信息(错误消息等(

在数据包结束时，您使用endtime更新DB中的行。

您可以查询此表并比较start&endtimes以查找运行包的总时间。

我们在这里常用的审计表示例；

USE [database]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[dim_audit](
[AuditKey] [int] IDENTITY(1,1) NOT NULL,
[ParentAuditKey] [int] NOT NULL,
[TableName] [nvarchar](50) NOT NULL,
[PkgName] [nvarchar](50) NOT NULL,
[PkgGUID] [uniqueidentifier] NULL,
[PkgVersionGUID] [uniqueidentifier] NULL,
[PkgVersion] [nvarchar](50) NULL,
[ExecStartDT] [datetime] NOT NULL,
[ExecStopDT] [datetime] NULL,
[ExecutionInstanceGUID] [uniqueidentifier] NULL,
[ExtractRowCnt] [bigint] NULL,
[InsertRowCnt] [bigint] NULL,
[UpdateRowCnt] [bigint] NULL,
[DeleteRowCnt] [bigint] NULL,
[TableInitialRowCnt] [bigint] NULL,
[TableFinalRowCnt] [bigint] NULL,
[TableMaxSurrogateKey] [bigint] NULL,
[SuccessfulProcessingInd] [nchar](1) NOT NULL,
CONSTRAINT [PK_dim_audit] PRIMARY KEY CLUSTERED 
(
[AuditKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO