{"id":2349,"date":"2014-11-07T06:59:44","date_gmt":"2014-11-07T06:59:44","guid":{"rendered":"http:\/\/bicortex.com\/?p=2349"},"modified":"2017-12-10T06:09:37","modified_gmt":"2017-12-10T06:09:37","slug":"how-to-create-a-simple-etlstored-procedure-error-capturing-database-schema-model-sql-code-and-implementation-part-2","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/how-to-create-a-simple-etlstored-procedure-error-capturing-database-schema-model-sql-code-and-implementation-part-2\/","title":{"rendered":"How to create a simple ETL\/stored procedure error capturing database (schema model, SQL code and implementation) \u2013 Part 2"},"content":{"rendered":"

\ufeff<\/span>In PART 1<\/a> of this series I analysed the execution error tracking database schema as well as the code for ‘scraping’ SQL Server metadata in order to populate the database tables with instance, database, schema and objects-specific information. In this part I will briefly describe SQL code modifications which are applied the original stored procedure (see PART 1<\/a>) to customize it in order to handle metadata changes as well as the actual implementation and error-logging mechanism during package\/stored procedure execution. Also, all the code and any additional files presented in this and previous part can be downloaded from my OneDrive folder HERE<\/a>.<\/p>\n

Before I get into the nuts and bolts on how to log into LogSSISErrors_Error table, which in this case will form the base for all errors entries, let create a sample database, schema, table and stored procedure containing an error-invoking code i.e. executing division by 0. This stored procedure, which will later be appended with additional error-handling code it in order to capture and log execution error(s) through TRY\/CATCH T-SQL, will be used as the basis for demonstrating error capturing mechanism.<\/p>\n

\r\nUSE master\r\nIF EXISTS ( SELECT TOP 1\r\n                    *\r\n            FROM    sys.databases\r\n            WHERE   name = 'TestDB' )\r\n    DROP DATABASE TestDB\r\nCREATE DATABASE TestDB\r\nGO\r\nUSE TestDB\r\nEXEC sp_executesql N'CREATE SCHEMA dummyschema;';\r\nGO\r\nCREATE TABLE dummyschema.DummyTable\r\n    (\r\n      ID INT IDENTITY(1, 1) ,\r\n      Column1 VARCHAR(56) ,\r\n      Column2 VARCHAR(56) ,\r\n      Column3 VARCHAR(56)\r\n    )\r\nINSERT  INTO dummyschema.DummyTable\r\n        ( Column1 ,\r\n          Column2 ,\r\n          Column3\r\n        )\r\n        SELECT  'dummy value1' ,\r\n                'dummy value1' ,\r\n                'dummy value1'\r\n        UNION ALL\r\n        SELECT  'dummy value1' ,\r\n                'dummy value1' ,\r\n                'dummy value1'\r\n        UNION ALL\r\n        SELECT  'dummy value1' ,\r\n                'dummy value1' ,\r\n                'dummy value1' \r\nGO\r\nCREATE PROCEDURE dummyschema.usp_DummySP\r\n    (\r\n      @ExecutionInstanceGUID UNIQUEIDENTIFIER ,\r\n      @PackageName NVARCHAR(256)\r\n    )\r\nAS\r\n    BEGIN\r\n        INSERT  INTO TestDB.dummyschema.DummyTable\r\n                    ( Column1 )\r\n                    SELECT  1 \/ 0\r\n    END\r\n<\/pre>\n
As this database is supposed to be working mainly in conjunction with an SSIS package (standalone stored procedure execution error capture is also fine but in this case the SSIS component would have to be omitted and replaced with a place-holder value), I have also created a sample package used primarily to trigger stored procedure execution, with a couple of system variables capturing package execution instance GUID identifier and package name metadata. This package needs to be saved somewhere on the C:\\ drive for the metadata updating stored procedure to find the relevant info and populate\u00a0LogSSISErrors_Package table accordingly.<\/p>\n
\"Error_Capture_DB_Package_SQL_Statement\"<\/a><\/p>\n
\"Error_Capture_DB_Package_Params_Mapping\"<\/a><\/p>\n
Most of that code from PART 1<\/a> is also applicable to maintaining the error tracking database by means of modifying INSERT statements into MERGE statements, thus allowing for applying changes to already existing data and inserting new records. The core difference is that in PART 1<\/a> all AdminSQL database objects required to be (re)created before we could proceed with SQL Server instance metadata analysis and capture thus making its execution only applicable to a new environment, where the database needed to be rebuilt from scratch. Once SQLAdmin database has been created and initially populated with the code from PART 1<\/a>, any subsequent execution should account for updates and new data inserts only which are implemented by means of subtle changes to the stored procedure code, mainly replacing INSERTS with MERGE T-SQL. Below is a couple of examples where the code from PART 1<\/a> stored procedure was replaced with MERGE SQL statements with the complete code available for download from HERE<\/a>.<\/p>\n
\r\n-------------------------------------------------------------------------------------------------------\r\n--Update LogSSISErrors_Schema table\r\n-------------------------------------------------------------------------------------------------------\t\r\nMERGE INTO AdminDBA.dbo.LogSSISErrors_Schema AS t\r\nUSING\r\n    ( SELECT  DISTINCT\r\n                db.ID AS ID ,\r\n                meta.SchemaID AS SchemaID ,\r\n                meta.SchemaName AS SchemaName ,\r\n                meta.SchemaOwner AS SchemaOwner ,\r\n                1 AS CurrentlyUsed\r\n      FROM      @EnumDBMeta meta\r\n                JOIN AdminDBA.dbo.LogSSISErrors_DB db ON meta.DBName = db.DBName\r\n      WHERE     db.CurrentlyUsed = 1\r\n    ) s\r\nON ( t.SchemaName = s.SchemaName\r\n     AND s.ID = t.FKDBID\r\n   )\r\nWHEN MATCHED THEN\r\n    UPDATE SET [SchemaID] = s.[SchemaID] ,\r\n               [SchemaName] = s.[SchemaName] ,\r\n               [SchemaOwner] = s.[SchemaOwner] ,\r\n               [CurrentlyUsed] = s.[CurrentlyUsed]\r\nWHEN NOT MATCHED THEN\r\n    INSERT ( [FKDBID] ,\r\n             [SchemaID] ,\r\n             [SchemaName] ,\r\n             [SchemaOwner] ,\r\n             [CurrentlyUsed]\r\n           )\r\n    VALUES ( s.[ID] ,\r\n             s.[SchemaID] ,\r\n             s.[SchemaName] ,\r\n             s.[SchemaOwner] ,\r\n             s.[CurrentlyUsed]\r\n           )\r\nWHEN NOT MATCHED BY SOURCE THEN\r\n    UPDATE SET [CurrentlyUsed] = 0;\r\n\r\n\r\n-------------------------------------------------------------------------------------------------------\r\n--Update LogSSISErrors_Process table\r\n-------------------------------------------------------------------------------------------------------\t\r\nMERGE INTO AdminDBA.dbo.LogSSISErrors_Process AS t\r\nUSING\r\n    ( SELECT DISTINCT\r\n                meta.ProcessObjectID ,\r\n                meta.ProcessObjectName ,\r\n                sch.ID ,\r\n                1 AS CurrentlyUsed\r\n      FROM      @EnumDBMeta meta\r\n                JOIN AdminDBA.dbo.LogSSISErrors_Schema AS sch ON sch.SchemaName = meta.SchemaName\r\n                                                              AND meta.ProcessObjectSchemaID = sch.SchemaID\r\n                JOIN AdminDBA.dbo.LogSSISErrors_DB db ON meta.DBName = db.DBName\r\n                                                         AND sch.FKDBID = db.ID\r\n      WHERE     sch.CurrentlyUsed = 1\r\n                AND db.CurrentlyUsed = 1\r\n                AND ProcessObjectType IN ( 'SQL Stored Procedure',\r\n                                           'Aggregate Function',\r\n                                           'SQL DML Trigger',\r\n                                           'Assembly DML Trigger',\r\n                                           'Extended Stored Procedure',\r\n                                           'Assembly Stored Procedure',\r\n                                           'Replication Filter Procedure',\r\n                                           'Assembly Scalar Function',\r\n                                           'SQL Scalar Function' )\r\n    ) s\r\nON ( t.ProcessName = s.ProcessObjectName\r\n     AND s.ID = t.FKSchemaID\r\n   )\r\nWHEN MATCHED THEN\r\n    UPDATE SET [ProcessID] = s.ProcessObjectID ,\r\n               [ProcessName] = s.ProcessObjectName ,\r\n               [CurrentlyUsed] = s.[CurrentlyUsed]\r\nWHEN NOT MATCHED THEN\r\n    INSERT ( [ProcessID] ,\r\n             [ProcessName] ,\r\n             [FKSchemaID] ,\r\n             [CurrentlyUsed]\r\n           )\r\n    VALUES ( s.ProcessObjectID ,\r\n             s.ProcessObjectName ,\r\n             s.ID ,\r\n             s.[CurrentlyUsed]\r\n           )\r\nWHEN NOT MATCHED BY SOURCE THEN\r\n    UPDATE SET [CurrentlyUsed] = 0;\r\n<\/pre>\n
Upon execution of the modified stored procedure from PART 1<\/a>, the code should pick up our new database, schema, table and stored procedure which in turn should be reflected in AdminDBA database tables’ entries i.e. LogSSISErrors_Package, LogSSISErrors_DB, LogSSISErrors_Schema, LogSSISErrors_Object, and LogSSISErrors_Process tables as per the image below.<\/p>\n
\"Error_Capture_DB_AdminDBA_Refreshed_Data\"<\/a><\/p>\n
When trying to execute\u00a0the package, an error message is thrown due to T-SQL in\u00a0usp_DummySP stored procedure attempting to divide by zero, which is in accordance with our expectations. The stored procedure does not contain error handling code therefore division by zero operation is handled by halting execution and returning the error message back to Execution Results view as per below. Also, the package invoking the stored procedure needs to be saved<\/p>\n
\"Error_Capture_DB_SSIS_Exec_Failure\"<\/a><\/p>\n
Now let’s alter the dummyschema.usp_DummySP stored procedure’s code to allow error capture and handling capabilities. By default SQL Server has a number of system functions available to troubleshoot execution flow and expose error details. In combination with SSIS package system parameters i.e. execution instance GUID and package name, we can capture and log those details in LogSSISErrors_Error table, referencing other tables which provide other metadata details for richer analysis.<\/p>\n
The following ALTER SQL statements implements TRY\/CATCH SQL Server error handling capabilities and allows for logging execution issues e.g. dividing by zero in the highlighted SELECT statement section.<\/p>\n
\r\nUSE [TestDB]\r\nGO\r\nALTER PROCEDURE [dummyschema].[usp_DummySP]\r\n    (\r\n      @ExecutionInstanceGUID UNIQUEIDENTIFIER ,\r\n      @PackageName NVARCHAR(256)\r\n    )\r\nAS\r\n    BEGIN \r\n        BEGIN TRY\r\n            BEGIN TRANSACTION\t\t\r\n            INSERT  INTO TestDB.dummyschema.DummyTable\r\n                    ( Column1 )\r\n                    SELECT  1 \/ 0\r\n            COMMIT TRANSACTION\r\n        END TRY\r\n        BEGIN CATCH\r\n            ROLLBACK TRANSACTION;\r\n            WITH    TempErr ( [ErrorNumber], [ErrorSeverity], [ErrorState], [ErrorLine], [ErrorMessage], [ErrorDateTime], \r\n\t\t\t\t\t\t\t[LoginName], [UserName], PackageName, [ObjectID], [ProcessID], [ExecutionInstanceGUID], [DBName] )\r\n                      AS ( SELECT   ERROR_NUMBER()\t\tAS ErrorNumber ,\r\n                                    ERROR_SEVERITY()\tAS ErrorSeverity ,\r\n                                    ERROR_STATE()\t\tAS ErrorState ,\r\n                                    ERROR_LINE()\t\tAS ErrorLine ,\r\n                                    ERROR_MESSAGE()\t\tAS ErrorMessage ,\r\n                                    SYSDATETIME()\t\tAS ErrorDateTime ,\r\n                                    SYSTEM_USER\t\t\tAS LoginName ,\r\n                                    USER_NAME()\t\t\tAS UserName ,\r\n                                    @PackageName ,\r\n                                    OBJECT_ID('TestDB.dummyschema.DummyTable') AS ObjectID ,\r\n                                    ( SELECT    a.objectid\r\n                                      FROM      sys.dm_exec_requests r\r\n                                                CROSS\tAPPLY sys.dm_exec_sql_text(r.sql_handle) a\r\n                                      WHERE     session_id = @@spid\r\n                                    ) AS ProcessID ,\r\n                                    @ExecutionInstanceGUID AS ExecutionInstanceGUID ,\r\n\t\t\t\t\t\t\t\t\tDB_NAME()\t\t\tAS DatabaseName\r\n                         )\r\n                INSERT  INTO AdminDBA.dbo.LogSSISErrors_Error\r\n                        ( [ErrorNumber] ,\r\n                          [ErrorSeverity] ,\r\n                          [ErrorState] ,\r\n                          [ErrorLine] ,\r\n                          [ErrorMessage] ,\r\n                          [ErrorDateTime] ,\r\n                          [FKLoginID] ,\r\n                          [FKUserID] ,\r\n                          [FKPackageID] ,\r\n                          [FKObjectID] ,\r\n                          [FKProcessID] ,\r\n                          [ExecutionInstanceGUID]\r\n                        )\r\n                        SELECT  ErrorNumber\t\t\t\t= COALESCE(err.ErrorNumber, -1) ,\r\n                                ErrorSeverity\t\t\t= COALESCE(err.[ErrorSeverity], -1) ,\r\n                                ErrorState\t\t\t\t= COALESCE(err.[ErrorState], -1) ,\r\n                                ErrorLine\t\t\t\t= COALESCE(err.[ErrorLine], -1) ,\r\n                                ErrorMessage\t\t\t= COALESCE(err.[ErrorMessage], 'Unknown') ,\r\n                                ErrorDateTime\t\t\t= ErrorDateTime ,\r\n                                FKLoginID\t\t\t\t= src_login.ID ,\r\n                                FKUserID\t\t\t\t= src_user.ID ,\r\n                                [FKPackageID]\t\t\t= src_package.ID ,\r\n                                [FKObjectID]\t\t\t= src_object.ID ,\r\n                                [FKProcessID]\t\t\t= src_process.ID ,\r\n                                [ExecutionInstanceGUID] = err.ExecutionInstanceGUID\t\t\r\n                        FROM    TempErr err\r\n                                LEFT JOIN AdminDBA.dbo.LogSSISErrors_Login src_login\r\n\t\t\t\t\t\t\t\tON err.LoginName = src_login.LoginName\r\n                                LEFT JOIN AdminDBA.dbo.LogSSISErrors_User src_user \r\n\t\t\t\t\t\t\t\tON err.UserName = src_user.UserName\t\r\n\t\t\t\t\t\t\t\tAND src_user.FKDBID = \r\n\t\t\t\t\t\t\t\t\t(SELECT ID \r\n\t\t\t\t\t\t\t\t\tFROM AdminDBA.dbo.LogSSISErrors_DB db \r\n\t\t\t\t\t\t\t\t\tWHERE db.DBName = err.DBName)\t\t\t\t\t\t\r\n                                LEFT JOIN AdminDBA.dbo.LogSSISErrors_Package src_package \r\n\t\t\t\t\t\t\t\tON err.PackageName = (LEFT(src_package.PackageName, CHARINDEX('.', src_package.PackageName) - 1)) \r\n                                LEFT JOIN AdminDBA.dbo.LogSSISErrors_Object src_object \r\n\t\t\t\t\t\t\t\tON err.ObjectID = src_object.ObjectID\r\n                                LEFT JOIN AdminDBA.dbo.LogSSISErrors_Process src_process \r\n\t\t\t\t\t\t\t\tON err.ProcessID = src_process.ProcessID\r\n\t\t\t\t\t\t\t\t--WHERE \r\n\t\t\t\t\t\t\t\t--src_login.CurrentlyUsed\t= 1 AND\r\n\t\t\t\t\t\t\t\t--src_user.CurrentlyUsed\t= 1 AND \r\n\t\t\t\t\t\t\t\t--src_package.CurrentlyUsed\t= 1 AND\r\n\t\t\t\t\t\t\t\t--src_object.CurrentlyUsed\t= 1 AND\r\n\t\t\t\t\t\t\t\t--src_process.CurrentlyUsed\t= 1\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n        END CATCH\r\n    END\r\n<\/pre>\n
Once altered, the stored procedure executed through the package will write into the table if BEGIN CATCH\/END CATCH statement is invoked as per image below.<\/p>\n
\"Error_Capture_DB_Package_Exec_OK_With_Error_Metadata\"<\/a><\/p>\n
Now that\u00a0error details have been inserted into our AdminDBA database table, we can query the data across all related tables if more comprehensive view is required e.g. schema, database etc. Below sample query sets up a view which can be used through a reporting platform of choice to quickly identify transactional SSIS execution issues or perform a quick analysis on errors and their related objects, schemas, processes, packages etc.<\/p>\n
\r\nCREATE VIEW [dbo].[vw_LogSSISErrors_CoreData] AS\r\nSELECT  dbo.LogSSISErrors_Error.ID AS Error_ID ,\r\n        dbo.LogSSISErrors_Error.ErrorNumber ,\r\n        dbo.LogSSISErrors_Error.ErrorSeverity ,\r\n        dbo.LogSSISErrors_Error.ErrorState ,\r\n        dbo.LogSSISErrors_Error.ErrorLine ,\r\n        dbo.LogSSISErrors_Error.ErrorMessage ,\r\n        dbo.LogSSISErrors_Error.ErrorDateTime ,\r\n        dbo.LogSSISErrors_Object.ObjectName ,\r\n        dbo.LogSSISErrors_Object.ObjectType ,\r\n        dbo.LogSSISErrors_Object.ObjectDescription ,\r\n        dbo.LogSSISErrors_Schema.SchemaName AS ObjectSchemaName ,\r\n        dbo.LogSSISErrors_Process.ProcessName ,\r\n        LogSSISErrors_Schema_1.SchemaName AS ProcessSchemaName ,\r\n        dbo.LogSSISErrors_Package.PackageName ,\r\n        dbo.LogSSISErrors_User.UserName ,\r\n        dbo.LogSSISErrors_Login.LoginName ,\r\n        dbo.LogSSISErrors_DB.DBName ,\r\n        dbo.LogSSISErrors_Instance.InstanceName\r\nFROM    dbo.LogSSISErrors_Error\r\n        INNER JOIN dbo.LogSSISErrors_Object ON dbo.LogSSISErrors_Error.FKObjectID = dbo.LogSSISErrors_Object.ID\r\n        INNER JOIN dbo.LogSSISErrors_Schema ON dbo.LogSSISErrors_Object.FKSchemaID = dbo.LogSSISErrors_Schema.ID\r\n        INNER JOIN dbo.LogSSISErrors_DB ON dbo.LogSSISErrors_Schema.FKDBID = dbo.LogSSISErrors_DB.ID\r\n        INNER JOIN dbo.LogSSISErrors_Instance ON dbo.LogSSISErrors_DB.FKInstanceID = dbo.LogSSISErrors_Instance.ID\r\n        INNER JOIN dbo.LogSSISErrors_Package ON dbo.LogSSISErrors_Error.FKPackageID = dbo.LogSSISErrors_Package.ID\r\n        INNER JOIN dbo.LogSSISErrors_Process ON dbo.LogSSISErrors_Error.FKProcessID = dbo.LogSSISErrors_Process.ID\r\n        INNER JOIN dbo.LogSSISErrors_User ON dbo.LogSSISErrors_Error.FKUserID = dbo.LogSSISErrors_User.ID\r\n                                             AND dbo.LogSSISErrors_DB.ID = dbo.LogSSISErrors_User.FKDBID\r\n        INNER JOIN dbo.LogSSISErrors_Schema AS LogSSISErrors_Schema_1 ON dbo.LogSSISErrors_DB.ID = LogSSISErrors_Schema_1.FKDBID\r\n                                                              AND dbo.LogSSISErrors_Process.FKSchemaID = LogSSISErrors_Schema_1.ID\r\n        INNER JOIN dbo.LogSSISErrors_Login ON dbo.LogSSISErrors_Error.FKLoginID = dbo.LogSSISErrors_Login.ID;\r\n<\/pre>\n
Based on the output of this code, we can set up a simple dashboard (Tableau in this case) to provide us with some core metrics on the ETL issues that require addressing at a glance (click on image to enlarge).<\/p>\n
\"Error_Capture_DB_Tableau_Report\"<\/a><\/p>\n
You can also download all the code samples, SSIS package solution files and database schema model (saved in a format compatible with Navicat Data Modeler database design tool) from my OneDrive folder HERE<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
\ufeffIn PART 1 of this series I analysed the execution error tracking database schema as well as the code for ‘scraping’ SQL Server metadata in order to populate the database tables with instance, database, schema and objects-specific information. In this part I will briefly describe SQL code modifications which are applied the original stored procedure […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32,5],"tags":[58,49,19],"class_list":["post-2349","post","type-post","status-publish","format-standard","hentry","category-how-tos","category-sql","tag-data-modelling","tag-sql","tag-sql-server"],"aioseo_notices":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/2349","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/comments?post=2349"}],"version-history":[{"count":17,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/2349\/revisions"}],"predecessor-version":[{"id":3115,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/2349\/revisions\/3115"}],"wp:attachment":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/media?parent=2349"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/categories?post=2349"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/tags?post=2349"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}