第一步:创建需要的表
第二步:创建相应的存储过程
第三步:创建需要的视图
第四步:SSIS 创建流程
第一步创建需要的表:
USE [NESTLEBI_LOG]
GO
/****** Object: Table [dbo].[ETL_EXECUTE_STATUS] Script Date: 10/25/2017 10:30:47 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ETL_EXECUTE_STATUS](
[STATUS_ID] [nvarchar](10) NOT NULL,
[STATUS_DESC] [nvarchar](20) NULL,
PRIMARY KEY CLUSTERED
(
[STATUS_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [NESTLEBI_LOG]
GO
/****** Object: Table [dbo].[ETL_JOB_ERROR_LOG] Script Date: 10/25/2017 10:31:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ETL_JOB_ERROR_LOG](
[ERROR_LOG_ID] [int] IDENTITY(1,1) NOT NULL,
[PROCESS_LOG_ID] [int] NULL,
[ERROR_MSG] [nvarchar](max) NULL,
[COMPONENT_NAME] [nvarchar](255) NULL,
[CREATE_TIME] [datetime] NULL,
[ERROR_CODE] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[ERROR_LOG_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
USE [NESTLEBI_LOG]
GO
/****** Object: Table [dbo].[ETL_JOB_PROCESS_LOG] Script Date: 10/25/2017 10:31:51 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ETL_JOB_PROCESS_LOG](
[PROCESS_LOG_ID] [int] IDENTITY(1,1) NOT NULL,
[PCKG_NAME] [nvarchar](255) NULL,
[MACHINE_NAME] [nvarchar](255) NULL,
[EXECUTE_USER] [nvarchar](255) NULL,
[JOB_START_TIME] [datetime] NULL,
[JOB_FINISH_TIME] [datetime] NULL,
[EXECUTE_STATUS_ID] [nvarchar](10) NULL,
[EXECUTION_ID] [nvarchar](100) NULL,
[DATA_DT] [date] NULL,
PRIMARY KEY CLUSTERED
(
[PROCESS_LOG_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [NESTLEBI_LOG]
GO
/****** Object: Table [dbo].[ETL_PACKAGE_INFO] Script Date: 10/25/2017 10:32:37 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ETL_PACKAGE_INFO](
[PCKG_NAME] [nvarchar](255) NOT NULL,
[PCKG_DESC] [nvarchar](1000) NULL,
[PCKG_DEVELOPER] [nvarchar](255) NULL,
[PCKG_TGT_LVL] [nvarchar](10) NULL,
[PCKG_SRC_SYS] [nvarchar](20) NULL,
[PCKG_SUBJ] [nvarchar](10) NULL,
[PCKG_SOLUTION] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED
(
[PCKG_NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [NESTLEBI_LOG]
GO
/****** Object: Table [dbo].[ETL_TASK_EXE_LOG] Script Date: 10/25/2017 10:33:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ETL_TASK_EXE_LOG](
[EXECUTION_ID] [nvarchar](100) NULL,
[PACKAGE_NAME] [nvarchar](100) NULL,
[TASK_ID] [nvarchar](255) NULL,
[TASK_NAME] [nvarchar](255) NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[ExistingRowsBefore] [bigint] NULL,
[ProcessedRows] [bigint] NULL,
[EXECUTE_STATUS_ID] [nvarchar](10) NULL
) ON [PRIMARY]
GO
USE [NESTLEBI_LOG]
GO
/****** Object: Table [dbo].[ETL_TASK_LIST] Script Date: 10/25/2017 10:33:37 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ETL_TASK_LIST](
[PACKAGE_NAME] [nvarchar](100) NOT NULL,
[TASK_NAME] [nvarchar](255) NOT NULL,
[TASK_TYPE] [nvarchar](20) NULL,
PRIMARY KEY CLUSTERED
(
[PACKAGE_NAME] ASC,
[TASK_NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
第二步:创建相应的存储过程
USE [NESTLEBI_LOG]
GO
/****** Object: StoredProcedure [dbo].[SP_ETL_ERROR_LOG_INSERT] Script Date: 10/25/2017 10:36:57 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[SP_ETL_ERROR_LOG_INSERT]
@PROCESS_LOG_ID INTEGER,
@ERROR_MESSAGE NVARCHAR(255),
@COMPONENT_NAME NVARCHAR(255),
@ERROR_CODE NVARCHAR(50)
AS
BEGIN
INSERT INTO dbo.ETL_JOB_ERROR_LOG
(
PROCESS_LOG_ID,
ERROR_MSG,
COMPONENT_NAME,
CREATE_TIME,
ERROR_CODE
)
VALUES
(
@PROCESS_LOG_ID,
@ERROR_MESSAGE,
@COMPONENT_NAME,
GETDATE(),
@ERROR_CODE
)
END
GO
USE [NESTLEBI_LOG]
GO
/****** Object: StoredProcedure [dbo].[SP_ETL_GET_TABLE_COUNT] Script Date: 10/25/2017 10:37:13 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_ETL_GET_TABLE_COUNT]
@TABLE_NAME NVARCHAR(255),
@ROW_COUNT BIGINT OUTPUT
AS
BEGIN
SELECT @ROW_COUNT = SUM(PART.rows)
FROM NESTLEBI.sys.tables TBL
INNER JOIN NESTLEBI.sys.partitions PART ON TBL.object_id = PART.object_id
INNER JOIN NESTLEBI.sys.indexes IDX ON PART.object_id = IDX.object_id
AND PART.index_id = IDX.index_id
WHERE TBL.name = @TABLE_NAME
AND IDX.index_id < 2
GROUP BY TBL.object_id, TBL.name
RETURN @ROW_COUNT
END
GO
USE [NESTLEBI_LOG]
GO
/****** Object: StoredProcedure [dbo].[SP_ETL_PROCESS_LOG_END_UPDATE] Script Date: 10/25/2017 10:37:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_ETL_PROCESS_LOG_END_UPDATE]
@EXECUTE_STATUS_ID INTEGER,
@PROCESS_LOG_ID INTEGER
AS
BEGIN
UPDATE dbo.ETL_JOB_PROCESS_LOG
SET JOB_FINISH_TIME = GETDATE(),
EXECUTE_STATUS_ID = @EXECUTE_STATUS_ID
WHERE PROCESS_LOG_ID = @PROCESS_LOG_ID
END
GO
USE [NESTLEBI_LOG]
GO
/****** Object: StoredProcedure [dbo].[SP_ETL_PROCESS_LOG_START_INSERT] Script Date: 10/25/2017 10:37:59 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_ETL_PROCESS_LOG_START_INSERT]
@PACKAGE_NAME NVARCHAR(255),
@MACHINE_NAME NVARCHAR(255),
@EXECUTE_USER NVARCHAR(255),
@START_TIME DATETIME,
@EXECUTION_ID NVARCHAR(100),
@DATA_DT DATE,
@PROCESS_LOG_ID INTEGER OUTPUT
AS
BEGIN
INSERT INTO dbo.ETL_JOB_PROCESS_LOG
(
PCKG_NAME,
MACHINE_NAME,
EXECUTE_USER,
JOB_START_TIME,
EXECUTE_STATUS_ID,
EXECUTION_ID,
DATA_DT
)
VALUES
(
@PACKAGE_NAME,
@MACHINE_NAME,
@EXECUTE_USER,
@START_TIME,
0,-- IN PROCESS,
@EXECUTION_ID,
@DATA_DT
)
SELECT @PROCESS_LOG_ID = @@IDENTITY
END
GO
USE [NESTLEBI_LOG]
GO
/****** Object: StoredProcedure [dbo].[SP_ETL_TASK_END_UPDATE] Script Date: 10/25/2017 10:38:13 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_ETL_TASK_END_UPDATE]
@ExecutionID NVARCHAR(250),
@TaskID NVARCHAR(250),
@PACKAGE_NAME NVARCHAR(100),
@TASK_NAME NVARCHAR(250),
@ProcessedRows BIGINT
AS
BEGIN
DECLARE @Task_list_check int
select @Task_list_check = count(1) from dbo.ETL_TASK_LIST where PACKAGE_NAME = @PACKAGE_NAME and TASK_NAME = @TASK_NAME
if @Task_list_check > 0
BEGIN
UPDATE dbo.ETL_TASK_EXE_LOG
SET ProcessedRows = @ProcessedRows,
EndTime = GETDATE(),
EXECUTE_STATUS_ID = 1
WHERE EXECUTION_ID = @ExecutionID
AND TASK_ID = @TaskID
END
ELSE
BEGIN
select 1
END
END
GO
USE [NESTLEBI_LOG]
GO
/****** Object: StoredProcedure [dbo].[SP_ETL_TASK_START_INSERT] Script Date: 10/25/2017 10:38:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_ETL_TASK_START_INSERT]
@TARGET_TABLE_NAME NVARCHAR(50),
@EXECUTION_ID NVARCHAR(255) ,
@PACKAGE_NAME NVARCHAR(100),
@TASK_ID NVARCHAR(255),
@TASK_NAME NVARCHAR(250)
AS
BEGIN
DECLARE @Task_list_check int
select @Task_list_check = count(1) from dbo.ETL_TASK_LIST where PACKAGE_NAME = @PACKAGE_NAME and TASK_NAME = @TASK_NAME
if @Task_list_check > 0
BEGIN
DECLARE @ExistingRowsBefore BIGINT
EXECUTE dbo.SP_ETL_GET_TABLE_COUNT
@TABLE_NAME = @TARGET_TABLE_NAME,
@ROW_COUNT = @ExistingRowsBefore OUTPUT
INSERT INTO dbo.ETL_TASK_EXE_LOG
(
EXECUTION_ID
,PACKAGE_NAME
,TASK_ID
,TASK_NAME
---,TABLE_NAME
,StartTime
,EndTime
,ExistingRowsBefore
,ProcessedRows
,EXECUTE_STATUS_ID
)
VALUES
(
@EXECUTION_ID
,@PACKAGE_NAME
,@TASK_ID
,@TASK_NAME
----,@TARGET_TABLE_NAME
,GETDATE()
,NULL --@EndTime
,@ExistingRowsBefore
,NULL --ProcessedRows
,0 -- In process
)
END
ELSE
BEGIN
select 1
END
END
GO
第三步:创建需要的视图
USE [NESTLEBI_LOG]
GO
/****** Object: View [dbo].[VW_ERROR_LOG] Script Date: 10/25/2017 10:39:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[VW_ERROR_LOG] as
select a.PROCESS_LOG_ID,a.PCKG_NAME,c.COMPONENT_NAME,a.EXECUTE_USER,a.EXECUTION_ID,a.JOB_FINISH_TIME,a.JOB_START_TIME
,a.MACHINE_NAME,b.STATUS_DESC,c.ERROR_CODE,c.ERROR_MSG from ETL_JOB_PROCESS_LOG a
join ETL_EXECUTE_STATUS b on a.EXECUTE_STATUS_ID=b.STATUS_ID
join ETL_JOB_ERROR_LOG c on a.PROCESS_LOG_ID=c.PROCESS_LOG_ID
GO
USE [NESTLEBI_LOG]
GO
/****** Object: View [dbo].[VW_SUCCESS_LOG] Script Date: 10/25/2017 10:40:24 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[VW_SUCCESS_LOG]
as
select * from [dbo].[ETL_JOB_PROCESS_LOG] where EXECUTE_STATUS_ID='1'
GO
第四步:SSIS 创建流程
0.插件参数
1.1.创建start_insert_log
1.2.start_insert_log 配置
EXECUTE
SP_ETL_PROCESS_LOG_START_INSERT
@PACKAGE_NAME = ? ,
@MACHINE_NAME = ? ,
@EXECUTE_USER = ? ,
@START_TIME = ? ,
@EXECUTION_ID = ? ,
@DATA_DT = ? ,
@PROCESS_LOG_ID = ? OUTPUT
2.1.创建suc_end_update_log
2.2 suc_end_update_log 配置
EXECUTE
SP_ETL_PROCESS_LOG_END_UPDATE
@EXECUTE_STATUS_ID = 1 ,
@PROCESS_LOG_ID = ?
3.1 创建异常捕获
3.2 error_end_update_log 配置
EXECUTE
SP_ETL_PROCESS_LOG_END_UPDATE
@EXECUTE_STATUS_ID = -1 ,
@PROCESS_LOG_ID = ?
3.3 insert_error_log 配置
EXECUTE
SP_ETL_ERROR_LOG_INSERT
@PROCESS_LOG_ID = ? ,
@ERROR_MESSAGE = ? ,
@COMPONENT_NAME = ? ,
@ERROR_CODE = ?
4.1 建立 onpostexecute
4.2 Execute SP_ETL_TASK_END_UPDATE 配置
execute dbo.SP_ETL_TASK_END_UPDATE
@ExecutionID = ?
,@TaskID = ?
,@PACKAGE_NAME = ?
,@TASK_NAME = ?
,@ProcessedRows = ?
5.1 创建 onpreexecute
5.2 配置 Execute SP_ETL_TASK_START_INSERT
execute dbo.SP_ETL_TASK_START_INSERT
@TARGET_TABLE_NAME = ?
,@EXECUTION_ID = ?
,@PACKAGE_NAME = ?
,@TASK_ID = ?
,@TASK_NAME = ?
使用