DDL Triggers Implementation

Lately I have been bug by QA about developers doing changes to the database objects using store procedures or logging using the application account which is getting very annoying, eventually I search for some old post that I made is SQLServerCentral 

and found the below code...Now lets the hunt begin..Developers will be developers so if it was up to them we would be doing changes everywhere.

-----create audit tables
--Table to Store Function Changes

create table dbo.t_function
(
ID int identity (1,1)
,DataChange xml not null
,createdDate smalldatetime default getutcdate()
,ModifyDate smalldatetime default getutcdate()
,CreateBY nvarchar(80) default suser_name ()
,ModifiedBy nvarchar(80) default suser_name()
,IsDeleted bit default 'FALSE'

constraint [t_function_PK] primary Key clustered
(
[ID] asc
)
)
go
--Create Table for table changes
create table dbo.t_table
(
ID int identity (1,1)
,DataChange xml not null
,createdDate smalldatetime default getutcdate()
,ModifyDate smalldatetime default getutcdate()
,CreateBY nvarchar(80) default suser_name ()
,ModifiedBy nvarchar(80) default suser_name()
,IsDeleted bit default 'FALSE'

constraint [t_table_PK] primary Key clustered
(
[ID] asc
)
)
go
---Create Table for procedure changes
create table dbo.t_procedure
(
ID int identity (1,1)
,DataChange xml not null
,createdDate smalldatetime default getutcdate()
,ModifyDate smalldatetime default getutcdate()
,CreateBY nvarchar(80) default suser_name ()
,ModifiedBy nvarchar(80) default suser_name()
,IsDeleted bit default 'FALSE'

constraint [t_procedure_PK] primary Key clustered
(
[ID] asc

)
)
Go
---Create table for View changes

create table dbo.t_view
(
ID int identity (1,1)
,DataChange xml not null
,createdDate smalldatetime default getutcdate()
,ModifyDate smalldatetime default getutcdate()
,CreateBY nvarchar(80) default suser_name ()
,ModifiedBy nvarchar(80) default suser_name()
,IsDeleted bit default 'FALSE'

constraint [t_views_PK] primary Key clustered
(
[ID] asc

)
)
go
--Create Table for Trigger Changes
create table dbo.t_trigger
(
ID int identity (1,1)
,DataChange xml not null
,createdDate smalldatetime default getutcdate()
,ModifyDate smalldatetime default getutcdate()
,CreateBY nvarchar(80) default suser_name ()
,ModifiedBy nvarchar(80) default suser_name()
,IsDeleted bit default 'FALSE'

constraint [t_triggers_PK] primary Key clustered
(
[ID] asc

)
)
go
----Now Create a View to Query the Data
---Also is a way to put it all together

CREATE view [dbo].[V_DDLAudit]
as
SELECT
DataChange.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(25)') as ObjectType,
DataChange.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(50)') as EventType,
DataChange.value('(/EVENT_INSTANCE/PostTime)[1]','smalldatetime') as ChangedDated,
DataChange.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(80)') as ObjectName,
DataChange.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(80)') as ChangedBy,
DataChange.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(15)') as DBRole,
DataChange.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') as CommandText

from dbo.t_table
union
SELECT
DataChange.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(25)') as ObjectType,
DataChange.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(50)') as EventType,
DataChange.value('(/EVENT_INSTANCE/PostTime)[1]','smalldatetime') as ChangedDated,
DataChange.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(80)') as ObjectName,
DataChange.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(80)') as ChangedBy,
DataChange.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(15)') as DBRole,
DataChange.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') as CommandText
from dbo.t_function
union
SELECT
DataChange.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(25)') as ObjectType,
DataChange.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(50)') as EventType,
DataChange.value('(/EVENT_INSTANCE/PostTime)[1]','smalldatetime') as ChangedDated,
DataChange.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(80)') as ObjectName,
DataChange.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(80)') as ChangedBy,
DataChange.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(15)') as DBRole,
DataChange.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') as CommandText
from dbo.t_procedure
union
SELECT
DataChange.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(25)') as ObjectType,
DataChange.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(50)') as EventType,
DataChange.value('(/EVENT_INSTANCE/PostTime)[1]','smalldatetime') as ChangedDated,
DataChange.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(80)') as ObjectName,
DataChange.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(80)') as ChangedBy,
DataChange.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(15)') as DBRole,
DataChange.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') as CommandText
from dbo.t_view
union
SELECT
DataChange.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(25)') as ObjectType,
DataChange.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(50)') as EventType,
DataChange.value('(/EVENT_INSTANCE/PostTime)[1]','smalldatetime') as ChangedDated,
DataChange.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(80)') as ObjectName,
DataChange.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(80)') as ChangedBy,
DataChange.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(15)') as DBRole,
DataChange.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') as CommandText
from dbo.t_trigger

---Finaly Create DDL Triggers

----Create Triggers
create trigger trig_function_DDL
on database
for
create_function,drop_function,alter_function
as
INSERT INTO [Sch_AuditDdl].[t_function]
 ([DataChange]

 )
 VALUES
 (eventdata())
go
create trigger trig_procs_DDL
on database
for
create_procedure,drop_procedure,alter_procedure
as
INSERT INTO [Sch_AuditDdl].[t_procedure]
 ([DataChange]

 )
 VALUES
 (eventdata())
go
create trigger trig_table_DDL
on database
for
create_table,drop_table,alter_table
as
INSERT INTO [Sch_AuditDdl].[t_table]
 ([DataChange]

 )
 VALUES
 (eventdata())
go
create trigger trig_views_DDL
on database
for
create_view,drop_view,alter_view
as
INSERT INTO [Sch_AuditDdl].[t_view]
 ([DataChange]

 )
 VALUES
 (eventdata())
go
create trigger trig_triggers_DDL
on database
for
create_trigger,drop_trigger,alter_trigger
as
INSERT INTO [Sch_AuditDdl].[t_trigger]
 ([DataChange]

 )
 VALUES
 (eventdata())
go

SSIS Reverse Engineering Part II Revision I

After some thoughts on how to made the code simple and better I created new changes to the previous post, the class to traverse ssis packages; however, trying to get the best balance is hard work. So, I fired the changes and after arriving home I continue minor changes in the progress of other classes. As a short note my son's discover Japanese anime and I can't get him to help me as my personal assistant ( He is only 12 ) now he is watching Mysterious Girlfriend X and I just starting look like an angry martian. Parenting is almost like coding you make it, refined, love it, but at the end there are those little things that sometime drives us crazy.

Well here is the code change enjoy:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.IO;

namespace LPS.ETL.SSIS
{
    public class ssisReverse
    {
        ssisPackage _package;
        #region constructor
      
        #endregion
        #region public properties
        public ssisReverse(String PackageDirectoryPath, String PackageName)
        {
            String FullFilePath = PackageDirectoryPath + "\\" + PackageName + ".dtsx";
            if (File.Exists(FullFilePath))
            {
                _package = new ssisPackage(FullFilePath);
               
            }
            else
            {
                Console.WriteLine("Error: {0} Package Not found", FullFilePath);
               
            }
        }
        #endregion
        #region public sub modules

        public void DisplayResults()
        {
            SearchPackage(_package.getExecutables);
        }

        #endregion

        #region private sub modules
        // Search Packge Module to find Components
     
        private void SearchPackage (Executables CollExec)
        {
            int count = 0;
            int count2 = (CollExec.Count);
            while (count != (count2))
           
           // foreach (Executable exc in CollExec)
            {
              
              Executable exc = CollExec[count++];
             switch (exc.GetType().Name)
                {
                    case "TaskHost":
                        TaskHost th = (TaskHost)exc;
                        GetTaskHost(th);
                        GetEventHandlers(th.EventHandlers);
                      
                       
                        //th = null;
                       continue;
                    case "Sequence":   
                        Sequence seq = (Sequence) exc;
                        Console.WriteLine("Sequence Container - {0}", seq.Name);
                        SearchPackage(seq.Executables);
                        GetEventHandlers(seq.EventHandlers);
                        GetPrecedence(seq.PrecedenceConstraints);
                       // seq = null;
                        continue;
                    case "ForEachLoop":
                        ForEachLoop fel = (ForEachLoop)exc;
                        Console.WriteLine("ForEachLoop Container - {0}", fel.Name);
                        SearchPackage(fel.Executables);
                        GetEventHandlers(fel.EventHandlers);
                        GetPrecedence(fel.PrecedenceConstraints);
                      //  fel = null;
                        continue;
                 case "ForLoop":
                        ForLoop fl = (ForLoop)exc;
                            Console.WriteLine("ForLoop Container - {0}", fl.Name);
                            SearchPackage(fl.Executables);
                            GetEventHandlers(fl.EventHandlers);
                            GetPrecedence(fl.PrecedenceConstraints);
                        //    fl = null;
                        continue;
                       
               }
             
              
              
            }

                      

               
            }

       
        // Recursive to find evenths
        private void GetEventHandlers(DtsEventHandlers colleventh)
        {
            foreach (DtsEventHandler eventh in colleventh)
            {
                SearchPackage(eventh.Executables);
            }
        }
        // Find dataflows
        private void GetTaskHost(TaskHost th)
        {
            if (th == null)
            {
                return;
            }
            Console.WriteLine("TaskHost : {0}", th.Name);
        
            MainPipe pipeline = th.InnerObject as MainPipe;
            if (pipeline != null)
            {
          GetPipeline(pipeline);
            }
        }
        //SQL 2005
        private void GetPipeline(MainPipe pipe)
        {
            foreach (IDTSComponentMetaData90 componentMetadata in pipe.ComponentMetaDataCollection)
            {
                Console.WriteLine("DataFlow Components - {0}", componentMetadata.Name);

             }
        }
        private void GetPrecedence(PrecedenceConstraints precedenceconstraints)
        {
            foreach (PrecedenceConstraint  pre in precedenceconstraints)
            {
                Console.WriteLine("PrecedenceConstrain: {0}", pre.PrecedenceExecutable.ToString());
            }
        }

        #endregion

    }
}
 

SSIS Template Generator and Reverse Engineering Part II

After completing a new schema for my new ssis framework finally is done, now is time to start coding the classes here is the First class to traverse a package enjoy:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.IO;

namespace ETL.SSIS
{
    class ssisReverse 
    {
        ssisPackage _package;
        string DirectoryPath = null;
        String PackageName = null;

        #region constructor
       
        #endregion
        #region public properties
        public ssisReverse(String PackageDirectoryPath, String PackageName)
        {
            String FullFilePath = PackageDirectoryPath + "\\" + PackageName + ".dtsx";
            if (File.Exists(FullFilePath))
            {
                _package = new ssisPackage(PackageDirectoryPath, PackageName);
                SearchPackage(_package.getExecutables);
            }
            else
            {
                Console.WriteLine("Error: {0} Package Not found", FullFilePath);
            }
        }
        #endregion 
        #region private sub modules
        // Search Packge Module to find Components 
      
        private void SearchPackage (Executables CollExec)
        {
            foreach (Executable exc in CollExec)
            {
                Sequence sequence = (Sequence)exc;
                if (sequence != null)
                {
                    Console.WriteLine("Sequence Container - {0}", sequence.Name);
                    SearchPackage(sequence.Executables);
                    GetEventHandlers(sequence.EventHandlers);
                    continue; 
                }
                ForEachLoop forEachLoop = (ForEachLoop) exc;
                if (forEachLoop != null)
                {
                    Console.WriteLine("For Each Loop - {0}", forEachLoop.Name);
                    SearchPackage(forEachLoop.Executables);
                    GetEventHandlers(forEachLoop.EventHandlers);
                    continue;
                }
                ForLoop floop = (ForLoop)exc;
                if (floop != null)
                {
                    Console.WriteLine("Sequence Container - {0}", floop.Name);
                    SearchPackage(floop.Executables);
                    GetEventHandlers(floop.EventHandlers);
                    continue; 

                }

                TaskHost taskHost = (TaskHost)exc;
                if (taskHost != null)
                {
                    GetTaskHost(taskHost);
                    GetEventHandlers(taskHost.EventHandlers);
                    continue;
                }

                
            }

        }
        // Recursive to find evenths
        private void GetEventHandlers(DtsEventHandlers colleventh)
        {
            foreach (DtsEventHandler eventh in colleventh)
            {
                SearchPackage(eventh.Executables);
            }
        }
        // Find dataflows
        private static void GetTaskHost(TaskHost th)
        {
            if (th == null)
            {
                return;
            }

            Console.WriteLine("TaskHost - {0}", th.Name);

           //DataFlow
            MainPipe pipeline = th.InnerObject as MainPipe;
            if (pipeline != null)
            {
                GetPipeline(pipeline);
            }
        }
        //SQL 2005
        private static void GetPipeline(MainPipe pipe)
        {
            foreach (IDTSComponentMetaData90 componentMetadata in pipe.ComponentMetaDataCollection)
            {
                Console.WriteLine("DataFlow Components - {0}", componentMetadata.Name);

             }
        }

        #endregion

    }
}