TL;DR Version<\/h3>\n
My last client engagement required me to create an ETL errors tracking solution which would serve as a central repository for all data related to SSIS packages execution failures. Given the initial scope and assumptions pointed to a small project size, thus the number of ETL routines involved kept at a minimum, my first intention was to build a single table encompassing all key information related to the package metadata and execution state. However, as the project grew in size, execs started to demand richer, more detailed data and the constricting schema of a single table became too evident, I decided to expand it with a proper relational database schema with additional metadata attributes at all common data-grouping hierarchies i.e. instance, database, schema and objects. In addition, I have also extracted metadata pertaining to users, logins and packages which complemented the whole data set with a comprehensive suite of variables for error-related analysis. Please note that this model and the subsequently discussed implementation is based primarily on capturing errors resulting from stored procedures execution as per my project demands. Since version 2012, SQL Server included the\u00a0SSISDB\u00a0catalog as the central point for working with Integration Services (SSIS) projects deployed to the Integration Services server which contains a rich set of data for all execution eventualities and should be treated as a default logging option for comprehensive SSIS runtime events capture.<\/p>\n
Below is a breakdown of a database schema model which accounts for most of the database-related objects as well as some handy information on individual packages, logins and users. First up, we have instance and database data specific tables \u2013 LogSSISErrors_Instance and LogSSISErrors_DB – in a one-to-many relationship i.e. one SQL Server instance to multiple databases.<\/p>\n
As each database can have multiple schemas, LogSSISErrors_DB has a one-to-many relationship to LogSSISErrors_Schema table, which in turn can be further related to LogSSISErrors_Object and LogSSISErrors_Process tables, each storing object-level data. The only distinction between ‘Object’ and ‘Process’ specific tables is the fact that ‘Object’ table stores table and view metadata whereas ‘Process’ table is concerned with stored procedures, assemblies, functions and triggers, even though on the database level they are all technically treated as objects.<\/p>\n Both, LogSSISErrors_Object and LogSSISErrors_Process have a one-to-many relationship with LogSSISErrors_Error table \u2013 the centrepiece of all the logging activities. LogSSISErrors_Error table is also linked up with LogSSISErrors_User, LogSSISErrors_Package and LogSSISErrors_Login tables, storing users, local SSIS packages and logins metadata.<\/p>\n LogSSISErrors_user table is also connected to LogSSISErrors_DB table as one database can have multiple users created against it.<\/p>\n Full entity relational diagram for AdminDBA is as per the image below.<\/p>\n All the data represented by the above schema, with the exception of LogSSISErrors_Package table entries, should be available by querying either catalog views, information schema views or system stored procedures\/functions. Most of the tables names are self-explanatory and can be easily referred back to the function they play and relevant metadata information. In case of LogSSISErrors_Process and LogSSISError_Object, these tables’ data comes from the same query with the distinction made on the object type i.e. view and tables, whereas stored procedures are bucketed as processes (even though technically they are objects too), thus placed in the LogSSISErrors_Process table. In case of LogSSISErrors_Package table, the package metadata is not stored on the server level but can be sourced from a .dtsx file using T-SQL. This part of solution was made available thanks to Jamie Thompson’s scripts which you can read about in detail in his post HERE<\/a>, I simply adopted his technique for my solution.<\/p>\n The script used to create all database objects and populate those with SQL Server instance metadata is close to 1800 lines long so in this post I’ll only go through sections of it, skipping all the boilerplate SQL; however, a full working version is available for download from my OneDrive folder HERE<\/a>.\u00a0The script is divided into a few sections, main parts being AdminDBA database metadata collection for subsequent objects recreation and second part determined by\u00a0a conditional logic where\u00a0\u00a0a\u00a0counts of foreign key constraints and a count of tables from two table variables created is compared.<\/p>\n First part creates two table variables storing objects AdminDBA objects metadata and foreign key constraints referencing individual tables.\u00a0If those tables have already been created and relationships exist, than the rest of the script utilizes this metadata in subsequent execution. The following T-SQL generates table variables and their content.<\/p>\n By design, after the the database and objects have been (re)created, the number of foreign key constraints should be one fewer than the number of tables. The below piece of SQL compares the counts of foreign key constraints and the count of tables from two table variables at which point one of the two possible scenarios can be triggered \u2013 if the count is comparable than the objects are re-created using the data encapsulated in table variables which already store DDL code for individual tables as well as foreign key constraints. If, on the other hand, the count is not comparable, a different part of stored procedure gets executed, this time responsible for recreating objects based on the hard-coded DDL statements and foreign key constraints.<\/p>\n Most of the SQL Server databases’ metadata comes from storing the results of the below query which extracts database, schema and objects information for each database and temporarily stores it in a table variable.<\/p>\n Instance level metadata comes mainly from appending different arguments to a\u00a0SERVERPROPERTY function to return instance-specific information. Database users metadata comes from sys.database_principals, whereas login details are returned from sys.server_principals catalog views.<\/p>\n SSIS package metadata is a little bit more complex to interrogate. In case of LogSSISErrors_Package table, the package metadata is not stored on the server level but can be sourced from a .dtsx file using T-SQL. As mentioned before, this part of solution was made available thanks to Jamie Thompson’s scripts which you can read about in detail in his post HERE<\/a>.<\/strong>\u00a0This part of script accesses the file system and reads\u00a0.dtsx XML content, storing relevant attributes in\u00a0LogSSISErrors_Package table.<\/p>\n Upon execution completion this code should create a small set of tables starting with LogSSISErrors prefix and metadata entries in some or all tables based on your local SQL Server instance as per the image below.<\/p>\n In the NEXT POST<\/a> I will go through the process of creating a sample test environment, an SSIS package running an error-generating process and another variation of the above stored procedure used to update the database as the environment changes occur. Also, all the code used in this solution can be downloaded from my OneDrive folder HERE<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":" Introduction Two part series (second part can be found HERE) on how to create and implement an ETL execution error capturing database (schema model, SQL code, implementation etc.) based on Microsoft SQL Server stored procedures default error handling capabilities. TL;DR Version My last client engagement required me to create an ETL errors tracking solution which […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38,32,5],"tags":[58,49,19],"class_list":["post-2319","post","type-post","status-publish","format-standard","hentry","category-data-modelling","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\/2319","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=2319"}],"version-history":[{"count":33,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/2319\/revisions"}],"predecessor-version":[{"id":2367,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/2319\/revisions\/2367"}],"wp:attachment":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/media?parent=2319"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/categories?post=2319"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/tags?post=2319"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![\"Error_Capture_DB_Schema_ERD_Partial_1\"](\"http:\/\/bicortex.com\/bicortex\/wp-content\/post_content\/\/2014\/10\/Error_Capture_DB_Schema_ERD_Partial_1.png\")
<\/a><\/p>\n![\"Error_Capture_DB_Schema_ERD_Partial_2\"](\"http:\/\/bicortex.com\/bicortex\/wp-content\/post_content\/\/2014\/10\/Error_Capture_DB_Schema_ERD_Partial_2.png\")
<\/a><\/p>\n![\"Error_Capture_DB_Schema_ERD_Partial_3\"](\"http:\/\/bicortex.com\/bicortex\/wp-content\/post_content\/\/2014\/10\/Error_Capture_DB_Schema_ERD_Partial_3.png\")
<\/a><\/p>\n![\"Error_Capture_DB_Schema_ERD_Partial_4\"](\"http:\/\/bicortex.com\/bicortex\/wp-content\/post_content\/\/2014\/10\/Error_Capture_DB_Schema_ERD_Partial_4.png\")
<\/a><\/p>\n![\"Error_Capture_DB_Schema_ERD_Full\"](\"http:\/\/bicortex.com\/bicortex\/wp-content\/post_content\/\/2014\/10\/Error_Capture_DB_Schema_ERD_Full.png\")
<\/a><\/p>\n\r\nDECLARE @TempFKs TABLE\r\n (\r\n ID INT IDENTITY(1, 1)\r\n NOT NULL ,\r\n Foreign_Key_Name VARCHAR(512) ,\r\n Parent_Object_Name VARCHAR(256) ,\r\n Referenced_Object_Name VARCHAR(256) ,\r\n Parent_Column_Name VARCHAR(256) ,\r\n Referenced_Column_Name VARCHAR(256)\r\n )\t\t\t\t\r\n\r\nINSERT INTO @TempFKs\r\n ( Foreign_Key_Name ,\r\n Parent_Object_Name ,\r\n Referenced_Object_Name ,\r\n Parent_Column_Name ,\r\n Referenced_Column_Name\r\n )\r\n SELECT fk.name AS foreign_key_name ,\r\n po.name AS parent_object_name ,\r\n ro.name AS referenced_object_name ,\r\n pc.name AS parent_column_name ,\r\n rc.name AS referenced_column_name\r\n FROM sys.foreign_key_columns fc\r\n JOIN sys.columns pc ON pc.column_id = parent_column_id\r\n AND parent_object_id = pc.object_id\r\n JOIN sys.columns rc ON rc.column_id = referenced_column_id\r\n AND referenced_object_id = rc.object_id\r\n JOIN sys.objects po ON po.object_id = pc.object_id\r\n JOIN sys.objects ro ON ro.object_id = rc.object_id\r\n JOIN sys.foreign_keys fk ON fk.object_id = fc.constraint_object_id\r\n WHERE ro.type = 'U'\r\n AND fk.type = 'F'\t\t\t\t\t\t\t\t\r\n\r\nDECLARE @TempTbls TABLE\r\n (\r\n ID INT IDENTITY(1, 1)\r\n NOT NULL ,\r\n Table_Name VARCHAR(128) NOT NULL ,\r\n Table_Schema_Definition VARCHAR(MAX) NOT NULL\r\n )\t\t\t\t\r\n\r\nINSERT INTO @TempTbls\r\n ( Table_Name ,\r\n Table_Schema_Definition\r\n )\r\n SELECT so.name AS Table_Name ,\r\n 'create table [' + so.name + '] (' + STUFF(o.list, LEN(o.list),\r\n 1, '') + ')'\r\n + CASE WHEN tc.CONSTRAINT_NAME IS NULL THEN ''\r\n ELSE 'ALTER TABLE ' + so.name + ' ADD CONSTRAINT '\r\n + tc.CONSTRAINT_NAME + ' PRIMARY KEY ' + ' ('\r\n + LEFT(j.list, LEN(j.list) - 1) + ')'\r\n END AS Table_Schema_Definition\r\n FROM sysobjects so\r\n CROSS APPLY ( SELECT ' [' + COLUMN_NAME + '] ' + DATA_TYPE\r\n + CASE DATA_TYPE\r\n WHEN 'sql_variant' THEN ''\r\n WHEN 'text' THEN ''\r\n WHEN 'ntext' THEN ''\r\n WHEN 'xml' THEN ''\r\n WHEN 'decimal'\r\n THEN '('\r\n + CAST(NUMERIC_PRECISION AS VARCHAR)\r\n + ', '\r\n + CAST(NUMERIC_SCALE AS VARCHAR)\r\n + ')'\r\n ELSE COALESCE('('\r\n + CASE\r\n WHEN CHARACTER_MAXIMUM_LENGTH = -1\r\n THEN 'MAX'\r\n ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)\r\n END + ')', '')\r\n END + ' '\r\n + CASE WHEN EXISTS ( SELECT\r\n id\r\n FROM\r\n syscolumns\r\n WHERE\r\n OBJECT_NAME(id) = so.name\r\n AND name = column_name\r\n AND COLUMNPROPERTY(id,\r\n name,\r\n 'IsIdentity') = 1 )\r\n THEN 'IDENTITY('\r\n + CAST(IDENT_SEED(so.name) AS VARCHAR)\r\n + ','\r\n + CAST(IDENT_INCR(so.name) AS VARCHAR)\r\n + ')'\r\n ELSE ''\r\n END + ' '\r\n + ( CASE WHEN IS_NULLABLE = 'No'\r\n THEN 'NOT '\r\n ELSE ''\r\n END ) + 'NULL '\r\n + CASE WHEN INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT IS NOT NULL\r\n THEN 'DEFAULT '\r\n + INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT\r\n ELSE ''\r\n END + ', '\r\n FROM INFORMATION_SCHEMA.COLUMNS\r\n WHERE TABLE_NAME = so.name\r\n ORDER BY ORDINAL_POSITION\r\n FOR\r\n XML PATH('')\r\n ) o ( list )\r\n LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON tc.TABLE_NAME = so.name\r\n AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'\r\n CROSS APPLY ( SELECT '[' + COLUMN_NAME + '], '\r\n FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu\r\n WHERE kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME\r\n ORDER BY ORDINAL_POSITION\r\n FOR\r\n XML PATH('')\r\n ) j ( list )\r\n WHERE xtype = 'U'\r\n AND name NOT IN ( 'dtproperties' )\r\n<\/pre>\n\r\nIF NOT EXISTS ( SELECT COUNT(*) - 1 AS ct\r\n FROM @TempFKs a\r\n EXCEPT\r\n SELECT COUNT(*)\r\n FROM @TempTbls b )\r\n\t\tAND EXISTS ( SELECT TOP 1 1 FROM @TempTbls )\r\n<\/pre>\n
\r\nSET @SQL = N'';\r\nSELECT\r\n@SQL = @SQL + ' UNION ALL\r\nSELECT\r\n[database]\t\t\t\t\t=\t''' + name\t+ ''',\r\n[schema]\t\t\t\t\t=\ts.name COLLATE Latin1_General_CI_AI,\r\n[schemaid]\t\t\t\t\t=\ts.schema_id,\r\n[schemaowner]\t\t\t\t=\tdp.name COLLATE Latin1_General_CI_AI,\r\n[processobjectname]\t\t\t=\tob.name COLLATE Latin1_General_CI_AI,\r\n[processobjectid]\t\t\t=\tob.object_id,\r\n[processobjectschemaid]\t\t=\tob.schema_id,\r\n[ProcessObjectDescription]\t=\tNULL,\r\n[processobjecttype]\t\t\t=\tCASE\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''AF''\tTHEN\t''Aggregate Function''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''C''\tTHEN\t''Check Constraint''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''D''\tTHEN\t''Default Constraint''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''F''\tTHEN\t''Foreign Key Constraint''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''FN''\tTHEN\t''SQL Scalar Function''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''FS''\tTHEN\t''Assembly Scalar Function''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''FT''\tTHEN\t''Assembly Table Valued Function''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''IF''\tTHEN\t''SQL Inline Table-valued Function''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''IT''\tTHEN\t''Internal Table''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''P''\tTHEN\t''SQL Stored Procedure''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''PC''\tTHEN\t''Assembly Stored Procedure''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''PG''\tTHEN\t''Plan Guide''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''PK''\tTHEN\t''Primary Key Constraint''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''R''\tTHEN\t''Rule''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''RF''\tTHEN\t''Replication Filter Procedure''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''S''\tTHEN\t''System Base Table''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''SN''\tTHEN\t''Synonym''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''SO''\tTHEN\t''Sequence Object''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''SQ''\tTHEN\t''Service Queue''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''TA''\tTHEN\t''Assembly DML Trigger''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''TF''\tTHEN\t''SQL Table Table Valued Function''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''TR''\tTHEN\t''SQL DML Trigger''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''TT''\tTHEN\t''Table Type''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''U''\tTHEN\t''User Defined Table''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''UQ''\tTHEN\t''Unique Constraint''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''V''\tTHEN\t''View''\r\n\t\t\t\t\t\t\t\tWHEN ob.type =\t''X''\tTHEN\t''Extended Stored Procedure''\r\n\t\t\t\t\t\t\t\tELSE\t\t\t\t\t\t\t''Unknown Object Type''\r\n\t\t\t\t\t\t\t\tEND,\r\n[createdate]\t\t\t\t=\tCAST(ob.create_date as date),\r\n[modifieddate]\t\t\t\t=\tCAST(ob.modify_date as date)\r\nFROM ' + QUOTENAME(name) + '.sys.schemas AS s\r\nINNER JOIN ' + QUOTENAME(name)\r\n+ '.sys.database_principals dp\r\non s.principal_id = dp.principal_id\r\nINNER JOIN ' + QUOTENAME(name) + '.sys.objects AS ob\r\nON s.[schema_id] = ob.[schema_id]\r\nwhere s.name <> ''sys'''\r\nFROM sys.databases\r\nWHERE database_id > 4\r\n AND name NOT IN ( 'ReportServer$SQL2014_MULTIDIM',\r\n 'ReportServer$SQL2014_MULTIDIMTempDB' );\r\nSET @SQL = @SQL + N' ORDER BY [database],[schema];';\r\nSET @SQL = STUFF(@SQL, 1, 11, '');\r\n\r\nDECLARE @EnumDBMeta TABLE\r\n (\r\n ID INT NOT NULL\r\n IDENTITY(1, 1) ,\r\n DBName NVARCHAR(128) ,\r\n SchemaName NVARCHAR(128) ,\r\n SchemaID INT ,\r\n SchemaOwner NVARCHAR(128) ,\r\n ProcessObjectName NVARCHAR(128) ,\r\n ProcessObjectID INT ,\r\n ProcessObjectSchemaID INT ,\r\n ProcessObjectDescription NVARCHAR(2048) ,\r\n ProcessObjectType NVARCHAR(128) ,\r\n ProcessObjectCreatedDate DATE ,\r\n ProcessObjectModifiedDate DATE\r\n )\r\nINSERT INTO @EnumDBMeta\r\n ( DBName ,\r\n SchemaName ,\r\n SchemaID ,\r\n SchemaOwner ,\r\n ProcessObjectName ,\r\n ProcessObjectID ,\r\n ProcessObjectSchemaID ,\r\n ProcessObjectDescription ,\r\n ProcessObjectType ,\r\n ProcessObjectCreatedDate ,\r\n ProcessObjectModifiedDate\r\n )\r\nEXEC sp_executesql @SQL\r\n<\/pre>\n
\r\nEXEC sp_configure 'show advanced options', 1;\r\nRECONFIGURE;\r\nEXEC sp_configure 'xp_cmdshell', 1;\r\nRECONFIGURE;\r\n\r\nDECLARE @Path VARCHAR(2000);\r\nSET @Path = 'C:\\*.dtsx';\r\n\t\t--Must be of form [drive letter]\\...\\*.dtsx\r\n\r\nDECLARE @MyFiles TABLE\r\n (\r\n MyID INT IDENTITY(1, 1)\r\n PRIMARY KEY ,\r\n FullPath VARCHAR(2000)\r\n );\r\nDECLARE @CommandLine VARCHAR(4000);\r\n\r\nSELECT @CommandLine = LEFT('dir "' + @Path + '" \/A-D \/B \/S ', 4000);\r\nINSERT INTO @MyFiles\r\n ( FullPath )\r\n EXECUTE xp_cmdshell @CommandLine;\r\nDELETE FROM @MyFiles\r\nWHERE FullPath IS NULL\r\n OR FullPath = 'File Not Found'\r\n OR FullPath = 'The system cannot find the path specified.'\r\n OR FullPath = 'The system cannot find the file specified.'; \r\n\r\nIF EXISTS ( SELECT *\r\n FROM sys.tables\r\n WHERE name = N'pkgStats' )\r\n DROP\tTABLE pkgStats;\r\nCREATE\t TABLE pkgStats\r\n (\r\n PackagePath VARCHAR(900) NOT NULL\r\n PRIMARY KEY ,\r\n PackageXML XML NOT NULL\r\n );\r\n\r\nDECLARE @FullPath VARCHAR(2000);\r\nDECLARE file_cursor CURSOR\r\nFOR\r\n SELECT FullPath\r\n FROM @MyFiles;\r\nOPEN\tfile_cursor\r\nFETCH NEXT FROM file_cursor INTO @FullPath;\r\nWHILE @@FETCH_STATUS = 0\r\n BEGIN\r\n SET @SQL = '\r\n\t\t\t\t\tINSERT\tpkgStats (PackagePath,PackageXML)\r\n\t\tselect ''@FullPath'' as PackagePath\r\n\t\t,\t\tcast(BulkColumn as XML) as PackageXML\r\n\t\tfrom openrowset(bulk ''@FullPath'',\r\n\t\t\t\t\t\t\t\tsingle_blob) as pkgColumn';\r\n SELECT @SQL = REPLACE(@SQL, '@FullPath', @FullPath);\r\n EXEC sp_executesql @SQL;\r\n\r\n FETCH NEXT FROM file_cursor INTO @FullPath;\r\n END\r\nCLOSE\tfile_cursor;\r\nDEALLOCATE file_cursor;\r\n\r\nDECLARE @pkgStatsBase TABLE\r\n (\r\n PackagePath VARCHAR(900) ,\r\n PackageId UNIQUEIDENTIFIER ,\r\n PackageCreatorsName NVARCHAR(500) ,\r\n PackageFormatVersion SMALLINT ,\r\n PackageType NVARCHAR(50) ,\r\n PackageDescription NVARCHAR(2000) ,\r\n PackageVersionMajor SMALLINT ,\r\n PackageVersionMinor SMALLINT ,\r\n PackageVersionBuild SMALLINT ,\r\n PackageVersionGUID UNIQUEIDENTIFIER ,\r\n PackageXML XML\r\n );\r\n\r\nINSERT INTO @pkgStatsBase\r\n SELECT PackagePath ,\r\n CAST(PackageXML.value('declare namespace DTS="www.microsoft.com\/SqlServer\/Dts";\r\n\t\t\t\t\t\t\t\/DTS:Executable[1]\/DTS:Property[@DTS:Name=''DTSID''][1]',\r\n 'nvarchar(500)') AS UNIQUEIDENTIFIER) AS PackageID ,\r\n PackageXML.value('declare namespace DTS="www.microsoft.com\/SqlServer\/Dts";\r\n\t\t\t\t\t\t\t\/DTS:Executable[1]\/DTS:Property[@DTS:Name=''CreatorName''][1]',\r\n 'nvarchar(500)') AS PackageCreatorsName ,\r\n CAST(PackageXML.value('declare namespace DTS="www.microsoft.com\/SqlServer\/Dts";\r\n\t\t\t\t\t\t\t\/DTS:Executable[1]\/DTS:Property[@DTS:Name=''PackageFormatVersion''][1]',\r\n 'varchar(3)') AS SMALLINT) AS PackageFormatVersion ,\r\n CAST(PackageXML.value('declare namespace DTS="www.microsoft.com\/SqlServer\/Dts";\r\n\t\t\t\t\t\t\tDTS:Executable[1]\/@DTS:ExecutableType[1]',\r\n 'nvarchar(50)') AS NVARCHAR(50)) AS PackageType ,\r\n PackageXML.value('declare namespace DTS="www.microsoft.com\/SqlServer\/Dts";\r\n\t\t\t\t\t\t\t\/DTS:Executable[1]\/DTS:Property[@DTS:Name=''Description''][1]',\r\n 'nvarchar(2000)') AS PackageDescription ,\r\n CAST(PackageXML.value('declare namespace DTS="www.microsoft.com\/SqlServer\/Dts";\r\n\t\t\t\t\t\t\t\/DTS:Executable[1]\/DTS:Property[@DTS:Name=''VersionMajor''][1]',\r\n 'varchar(3)') AS SMALLINT) AS PackageVersionMajor ,\r\n CAST(PackageXML.value('declare namespace DTS="www.microsoft.com\/SqlServer\/Dts";\r\n\t\t\t\t\t\t\t\/DTS:Executable[1]\/DTS:Property[@DTS:Name=''VersionMinor''][1]',\r\n 'varchar(3)') AS SMALLINT) AS PackageVersionMinor ,\r\n CAST(PackageXML.value('declare namespace DTS="www.microsoft.com\/SqlServer\/Dts";\r\n\t\t\t\t\t\t\t\/DTS:Executable[1]\/DTS:Property[@DTS:Name=''VersionBuild''][1]',\r\n 'varchar(3)') AS SMALLINT) AS PackageVersionBuild ,\r\n CAST(PackageXML.value('declare namespace DTS="www.microsoft.com\/SqlServer\/Dts";\r\n\t\t\t\t\t\t\t\/DTS:Executable[1]\/DTS:Property[@DTS:Name=''VersionGUID''][1]',\r\n 'char(38)') AS UNIQUEIDENTIFIER) AS PackageVersionGUID ,\r\n PackageXML\r\n FROM pkgStats\r\n\r\nDECLARE @AllpkgStats TABLE\r\n (\r\n PackageId UNIQUEIDENTIFIER NULL ,\r\n PackagePath VARCHAR(900) PRIMARY KEY\r\n NOT NULL ,\r\n PackageName VARCHAR(900) NULL ,\r\n PackageCreatorsName NVARCHAR(500) NULL ,\r\n PackageFormatVersion SMALLINT NULL ,\r\n PackageType NVARCHAR(50) NULL ,\r\n PackageDescription NVARCHAR(2000) NULL ,\r\n PackageVersionMajor SMALLINT NULL ,\r\n PackageVersionMinor SMALLINT NULL ,\r\n PackageVersionBuild SMALLINT NULL ,\r\n PackageVersionGUID UNIQUEIDENTIFIER NULL ,\r\n PackageTasksNumber INT NULL ,\r\n PackageContainersNumber INT NULL ,\r\n PackageDataFlowsNumber INT NULL ,\r\n PackageConnectionManagersNumber INT NULL ,\r\n PackageVariablesInEntirePackageNumber INT NULL ,\r\n PackageXML XML NULL\r\n ); \r\n\r\nINSERT INTO @AllpkgStats\r\n ( PackageId ,\r\n PackagePath ,\r\n PackageName ,\r\n PackageCreatorsName ,\r\n PackageFormatVersion ,\r\n PackageType ,\r\n PackageDescription ,\r\n PackageVersionMajor ,\r\n PackageVersionMinor ,\r\n PackageVersionBuild ,\r\n PackageVersionGUID ,\r\n PackageXML\r\n )\r\n SELECT p.PackageId ,\r\n p.PackagePath ,\r\n SUBSTRING(PackagePath,\r\n LEN(PackagePath) - CHARINDEX('\\',\r\n REVERSE(PackagePath), 0)\r\n + 2, LEN(PackagePath)) AS PackageName ,\r\n p.PackageCreatorsName ,\r\n p.PackageFormatVersion ,\r\n p.PackageType ,\r\n p.PackageDescription ,\r\n p.PackageVersionMajor ,\r\n p.PackageVersionMinor ,\r\n p.PackageVersionBuild ,\r\n p.PackageVersionGUID ,\r\n p.PackageXML\r\n FROM @pkgStatsBase p;\r\n\t\t--Number of tasks\r\nMERGE INTO @AllpkgStats AS t\r\nUSING\r\n ( SELECT PackagePath ,\r\n COUNT(*) AS PackageTasksNumber\r\n FROM @pkgStatsBase p\r\n CROSS APPLY p.PackageXML.nodes('declare namespace DTS="www.microsoft.com\/SqlServer\/Dts";\r\n\t\t\t\t\t\t\t\t\t\/\/DTS:Executable[@DTS:ExecutableType!=''STOCK:SEQUENCE''\r\n\t\t\t\t\t\t\t\tand @DTS:ExecutableType!=''STOCK:FORLOOP''\r\n\t\t\t\t\t\t\t\tand @DTS:ExecutableType!=''STOCK:FOREACHLOOP''\r\n\t\t\t\t\t\t\t\tand not(contains(@DTS:ExecutableType,''.Package.''))]') Pkg ( props )\r\n GROUP BY PackagePath\r\n ) s\r\nON ( t.PackagePath = s.PackagePath )\r\nWHEN MATCHED THEN\r\n UPDATE SET PackageTasksNumber = s.PackageTasksNumber;\r\n\r\n\t\t--Number of containers\r\nMERGE INTO @AllpkgStats AS t\r\nUSING\r\n ( SELECT PackagePath ,\r\n COUNT(*) AS PackageContainersNumber\r\n FROM @pkgStatsBase p\r\n CROSS APPLY p.PackageXML.nodes('declare namespace DTS="www.microsoft.com\/SqlServer\/Dts";\r\n\t\t\t\t\t\t\t\t\t\/\/DTS:Executable[@DTS:ExecutableType=''STOCK:SEQUENCE''\r\n\t\t\t\t\t\t\t\t\tor @DTS:ExecutableType=''STOCK:FORLOOP''\r\n\t\t\t\t\t\t\t\t\tor @DTS:ExecutableType=''STOCK:FOREACHLOOP'']') Pkg ( props )\r\n GROUP BY PackagePath\r\n ) s\r\nON ( t.PackagePath = s.PackagePath )\r\nWHEN MATCHED THEN\r\n UPDATE SET PackageContainersNumber = s.PackageContainersNumber\r\nWHEN NOT MATCHED BY SOURCE THEN\r\n UPDATE SET PackageContainersNumber = 0;\r\n\r\n\t\t--Number of data flows\r\nMERGE INTO @AllpkgStats AS t\r\nUSING\r\n ( SELECT PackagePath ,\r\n COUNT(*) AS PackageDataFlowsNumber\r\n FROM @pkgStatsBase p\r\n CROSS APPLY p.PackageXML.nodes('declare namespace DTS="www.microsoft.com\/SqlServer\/Dts";\r\n\t\t\t\t\t\t\t\t\t\/\/DTS:Executable[contains(@DTS:ExecutableType,''.Pipeline.'')]') Pkg ( props )\r\n GROUP BY PackagePath\r\n ) s\r\nON ( t.PackagePath = s.PackagePath )\r\nWHEN MATCHED THEN\r\n UPDATE SET PackageDataFlowsNumber = s.PackageDataFlowsNumber\r\nWHEN NOT MATCHED BY SOURCE THEN\r\n UPDATE SET PackageDataFlowsNumber = 0;\r\n\r\n\t\t--Number of connection managers\r\nMERGE INTO @AllpkgStats AS t\r\nUSING\r\n ( SELECT PackagePath ,\r\n COUNT(*) AS PackageConnectionManagersNumber\r\n FROM @pkgStatsBase p\r\n CROSS APPLY p.PackageXML.nodes('declare namespace DTS="www.microsoft.com\/SqlServer\/Dts";\r\n\t\t\t\t\t\t\t\t\t\/\/DTS:ConnectionManager') Pkg ( props )\r\n GROUP BY PackagePath\r\n ) s\r\nON ( t.PackagePath = s.PackagePath )\r\nWHEN MATCHED THEN\r\n UPDATE SET PackageConnectionManagersNumber = s.PackageConnectionManagersNumber\r\nWHEN NOT MATCHED BY SOURCE THEN\r\n UPDATE SET PackageConnectionManagersNumber = 0;\r\n\r\n\t\t--Number of variables in entire package\r\nMERGE INTO @AllpkgStats AS t\r\nUSING\r\n ( SELECT PackagePath ,\r\n COUNT(*) AS PackageVariablesInEntirePackageNumber\r\n FROM @pkgStatsBase p\r\n CROSS APPLY p.PackageXML.nodes('declare namespace DTS="www.microsoft.com\/SqlServer\/Dts";\r\n\t\t\t\t\t\t\t\t\t\/\/DTS:Variable') Pkg ( props )\r\n GROUP BY PackagePath\r\n ) s\r\nON ( t.PackagePath = s.PackagePath )\r\nWHEN MATCHED THEN\r\n UPDATE SET PackageVariablesInEntirePackageNumber = s.PackageVariablesInEntirePackageNumber\r\nWHEN NOT MATCHED BY SOURCE THEN\r\n UPDATE SET PackageVariablesInEntirePackageNumber = 0;\r\n\r\nINSERT INTO AdminDBA.dbo.LogSSISErrors_Package\r\n ( PackageID ,\r\n PackageName ,\r\n PackagePath ,\r\n PackageCreatorsName ,\r\n PackageFormatVersion ,\r\n PackageType ,\r\n PackageDescription ,\r\n PackageVersionMajor ,\r\n PackageVersionMinor ,\r\n PackageVersionBuild ,\r\n PackageVersionGUID ,\r\n PackageTasksNumber ,\r\n PackageXML ,\r\n PackageContainersNumber ,\r\n PackageDataFlowsNumber ,\r\n PackageConnectionManagersNumber ,\r\n PackageVariablesInEntirePackageNumber ,\r\n CurrentlyUsed\r\n )\r\n SELECT PackageId ,\r\n PackageName ,\r\n PackagePath ,\r\n PackageCreatorsName ,\r\n PackageFormatVersion ,\r\n PackageType ,\r\n PackageDescription ,\r\n PackageVersionMajor ,\r\n PackageVersionMinor ,\r\n PackageVersionBuild ,\r\n PackageVersionGUID ,\r\n PackageTasksNumber ,\r\n PackageXML ,\r\n PackageContainersNumber ,\r\n PackageDataFlowsNumber ,\r\n PackageConnectionManagersNumber ,\r\n PackageVariablesInEntirePackageNumber ,\r\n 1 AS CurrentlyUsed\r\n FROM @AllpkgStats\r\n\r\nIF EXISTS ( SELECT *\r\n FROM sys.tables\r\n WHERE name = N'pkgStats' )\r\n DROP\tTABLE pkgStats;\r\n\r\nEXEC sp_configure 'xp_cmdshell', 0;\r\nRECONFIGURE;\r\nEXEC sp_configure 'show advanced options', 0;\r\nRECONFIGURE;\r\n<\/pre>\n![\"Error_Capture_DB_AdminDBA_Objects_OnStart\"](\"http:\/\/bicortex.com\/bicortex\/wp-content\/post_content\/\/2014\/10\/Error_Capture_DB_AdminDBA_Objects_OnStart.png\")
<\/a><\/p>\n