目标
我的目标是创建支持审计功能(审核维度)的SSIS (ETL)模板。我发现了几种实现审计维度的方法,下面用下面的一些参考链接对这些方法进行了描述:
Best way to get identity of inserted row?
环境:
简化的DimAudit表模式:
CREATE TABLE [dw].[DimAudit] (
[AuditKey] [int] IDENTITY(1 ,1) NOT NULL,
[ParentAuditKey] [int] NOT NULL,
[TableName] [varchar] (50) NOT NULL DEFAULT ('Unknown'),
[PackageName] [varchar] (50) NOT NULL DEFAULT ('Unknown'),
[ExecStartDate] [datetime] NOT NULL DEFAULT ( getdate()),
[ExecStopDate] [datetime] NULL,
[SuccessfulProcessingInd] [char] (1) NOT NULL DEFAULT ('N'),
CONSTRAINT [PK_dbo.DimAudit] PRIMARY KEY CLUSTERED
(
[AuditKey] ASC
)
) ON [PRIMARY]
ALTER TABLE [dw].[DimAudit] WITH CHECK ADD CONSTRAINT [FK_DimAudit_ParentAuditKey] FOREIGN KEY( [ParentAuditKey])
REFERENCES [dw]. [DimAudit] ( [AuditKey])
GO
ALTER TABLE [dw].[DimAudit] CHECK CONSTRAINT [FK_DimAudit_ParentAuditKey]
GO主键选项:
主键在审计表中生成,然后查询AuditKey。

任务:主SQL审核生成密钥(SQL )
INSERT INTO [dw].[DimAudit]
(ParentAuditKey
,[TableName]
,[PackageName]
,[ExecStartDate]
,[ExecStopDate]
,[SuccessfulProcessingInd])
VALUES
(1
,'Master Extract Package'
,?
,?
,?
,'N')
SELECT AuditKey
FROM [dw].[DimAudit]
WHERE TableName = 'Master Extract Package' and ExecStartDT = ?
/*
Last Parameter: ParameterSystem::StartTime
Result Set populates User::ParentAuditKey
*/任务:主SQL审核结束(SQL )
UPDATE [dw]. [DimAudit]
SET ParentAuditKey = AuditKey
,ExecStopDT = SYSDATETIME()
,SuccessfulProcessingInd= 'Y'
WHERE AuditKey = ?
/*
Parameter: User::ParentAuditKey
*/SEQUENCE 选项:
sequence选项不选择主键( AuditKey ),而是使用下面的逻辑来创建下一个可用的AuditKey。
CREATE SEQUENCE dbo . AuditID as INT
START WITH 1
INCREMENT BY 1 ;
GO
DECLARE @AuditID INTEGER ;
SET @AuditID = NEXT VALUE FOR dbo. AuditID ;Best way to get identity of inserted row?
使用标识选项感觉有风险,因为ETL包可以并行执行。
问题
审计维度表和管理密钥的推荐做法是什么?
Sequence &主键选项完成了这项工作;但是,我对选择主键选项很担心,因为包可以以相同的毫秒(理论上)执行,因此存在几个主键。所以,序列听起来是最好的选择。
是否有更好的方法来为数据集市创建审计维度?
发布于 2015-11-24 16:37:11
您可以使用输出语法:
INSERT INTO [dw].[DimAudit]
(ParentAuditKey
,[TableName]
,[PackageName]
,[ExecStartDate]
,[ExecStopDate]
,[SuccessfulProcessingInd])
OUTPUT inserted.AuditKey
VALUES
(1
,'Master Extract Package'
,?
,?
,?
,'N')或者SCOPE_IDENTITY(),这是我个人使用的:
INSERT INTO Meta.AuditDim (
Date,
UserName,
Source,
SourceType,
AuditType,
ExecutionId,
ExecutionHost,
ParentAuditKey,
FileID
)
VALUES (
GETDATE(),
CURRENT_USER,
@Source,
@SourceType,
@AuditType,
@ExecutionId,
@ExecutionHost,
@ParentAuditKey,
@FileID
);
SELECT AuditKey FROM Meta.AuditDim WHERE AuditKey = SCOPE_IDENTITY();https://stackoverflow.com/questions/33482538
复制相似问题