SSIS 日志管理系统精简版

来源：互联网发布：识别脸型的软件编辑：程序博客网时间：2024/05/01 21:29

第一步：创建需要的表

第二步：创建相应的存储过程

第三步：创建需要的视图

第四步：SSIS 创建流程

第一步创建需要的表：

USE [NESTLEBI_LOG]GO/****** Object:  Table [dbo].[ETL_EXECUTE_STATUS]    Script Date: 10/25/2017 10:30:47 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOUSE [NESTLEBI_LOG]GO/****** Object:  Table [dbo].[ETL_JOB_ERROR_LOG]    Script Date: 10/25/2017 10:31:20 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOUSE [NESTLEBI_LOG]GO/****** Object:  Table [dbo].[ETL_JOB_PROCESS_LOG]    Script Date: 10/25/2017 10:31:51 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOUSE [NESTLEBI_LOG]GO/****** Object:  Table [dbo].[ETL_PACKAGE_INFO]    Script Date: 10/25/2017 10:32:37 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOUSE [NESTLEBI_LOG]GO/****** Object:  Table [dbo].[ETL_TASK_EXE_LOG]    Script Date: 10/25/2017 10:33:12 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOUSE [NESTLEBI_LOG]GO/****** Object:  Table [dbo].[ETL_TASK_LIST]    Script Date: 10/25/2017 10:33:37 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOcreate PROCEDURE [dbo].[SP_ETL_ERROR_LOG_INSERT]    @PROCESS_LOG_ID INTEGER,    @ERROR_MESSAGE NVARCHAR(255),    @COMPONENT_NAME NVARCHAR(255),    @ERROR_CODE NVARCHAR(50)     ASBEGIN        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    )ENDGOUSE [NESTLEBI_LOG]GO/****** Object:  StoredProcedure [dbo].[SP_ETL_GET_TABLE_COUNT]    Script Date: 10/25/2017 10:37:13 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[SP_ETL_GET_TABLE_COUNT]@TABLE_NAME NVARCHAR(255),@ROW_COUNT BIGINT OUTPUT ASBEGIN     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_COUNTENDGOUSE [NESTLEBI_LOG]GO/****** Object:  StoredProcedure [dbo].[SP_ETL_PROCESS_LOG_END_UPDATE]    Script Date: 10/25/2017 10:37:27 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[SP_ETL_PROCESS_LOG_END_UPDATE]     @EXECUTE_STATUS_ID INTEGER,    @PROCESS_LOG_ID INTEGERASBEGIN        UPDATE dbo.ETL_JOB_PROCESS_LOG    SET JOB_FINISH_TIME = GETDATE(),        EXECUTE_STATUS_ID = @EXECUTE_STATUS_ID    WHERE PROCESS_LOG_ID = @PROCESS_LOG_ID    ENDGOUSE [NESTLEBI_LOG]GO/****** Object:  StoredProcedure [dbo].[SP_ETL_PROCESS_LOG_START_INSERT]    Script Date: 10/25/2017 10:37:59 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 OUTPUTASBEGIN        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 = @@IDENTITYENDGOUSE [NESTLEBI_LOG]GO/****** Object:  StoredProcedure [dbo].[SP_ETL_TASK_END_UPDATE]    Script Date: 10/25/2017 10:38:13 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[SP_ETL_TASK_END_UPDATE]@ExecutionID NVARCHAR(250),@TaskID NVARCHAR(250),@PACKAGE_NAME NVARCHAR(100),@TASK_NAME NVARCHAR(250),@ProcessedRows BIGINTASBEGIN        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    ENDEND GOUSE [NESTLEBI_LOG]GO/****** Object:  StoredProcedure [dbo].[SP_ETL_TASK_START_INSERT]    Script Date: 10/25/2017 10:38:45 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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) ASBEGIN        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   ENDENDGO

第三步：创建需要的视图

USE [NESTLEBI_LOG]GO/****** Object:  View [dbo].[VW_ERROR_LOG]    Script Date: 10/25/2017 10:39:52 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate view [dbo].[VW_ERROR_LOG] asselect 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_IDGOUSE [NESTLEBI_LOG]GO/****** Object:  View [dbo].[VW_SUCCESS_LOG]    Script Date: 10/25/2017 10:40:24 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate view [dbo].[VW_SUCCESS_LOG]asselect * 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