SSIS- Read Variable In Script Component and Generate Sequence Number

Scenario : 

We got this requirement that we need to read the max  Sequence Number value from Table and then generate sequence by adding 1 in Data Flow for our SequenceNumber column.

Solution: 

Step 1: 
First we have to read the max(sequencenumber) from our table and store in some variable that we can use in script component later.

Bring Execute SQL Task on Control Flow pane and configure as shown below


Set the values to Variable seqno

Step 2: 
Now bring data flow task to Control Flow pane. Double Click on Data Flow task and then bring the Source from which you need to read the data.
Bring Script Component to Data Flow Pane and then connect with your Source. We will be using Script task as transformation.

Configure the Script Component as shown below
Select the Scripting Language and ReadOnlyVariable

Choose the columns those you want to pass through

Add a new output Column Seqno and set the data type as shown

Go to General and Click on Edit Script and then make changes as shown in RED

' Microsoft SQL Server Integration Services Script Component
' Write scripts using Microsoft Visual Basic 2008.
' ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

_
_
Public Class ScriptMain
    Inherits UserComponent
    Dim rowNumber As Int32

    Public Overrides Sub PreExecute()
        rowNumber = Me.Variables.seqno
        MyBase.PreExecute()
        ' Add your code here for preprocessing or remove if not needed
        ''
    End Sub
    Public Overrides Sub PostExecute()
        MyBase.PostExecute()
        ' Add your code here for postprocessing or remove if not needed
        ' You can set read/write variables here, for example:
        ' Me.Variables.MyIntVar = 100
        ''
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        '
        rowNumber += 1
        Row.Seqno = rowNumber

    End Sub

End Class


Final Output:

I have added dataviewer to show the data. Name is my input column and Seqno is created in Script Component.


SSIS - Data Conversion and Derived Column

Scenario:

Lets say we have some data in text file that we need to load into our SQL table. We have to perform data conversion on SaleDate and then also have to get previous date from SaleDate to insert into our final Table.

Solution :

Step 1:
Create file file source by using Flat File Source.
Step 2:
Bring Data Conversion and connect to Flate File Source as shown and convert the data type to DT_DBDATE

Step 3:
Bring Derived column Transformation so we can get previous date from SaleDate coming from Source. Remember we have converted the SaleDate to DT_DBDate and now we will use column Copy of SaleDate in our Derived Column.


Final Output:

Put Data Viewer between Derived column and Destination so see if all values are transformed correctly.



DBA - Create DDL Trigger for Auditing


Scenario: 

Sometime we have to capture Audit information such as who dropped the table, who created indexes, who altered Stored procedure etc.

Solution : 

To capture this type of information we can create DDL Trigger on Server.
Here is the list of Events for which DDL Trigger can be fired

http://technet.microsoft.com/en-us/library/ms189871(v=sql.90).aspx

I have selected few events according to my requirement. You can include or exclude events according to your requirements.

Sample Code: 


USE [master]

GO

/****** Object:  Table [dbo].[Audit_Log]    Script Date: 08/14/2013 11:21:00 ******/
SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Audit_Log]
  (
     [EventTime]    [DATETIME] NULL,
     [LoginName]    [VARCHAR](255) NULL,
     [UserName]     [VARCHAR](255) NULL,
     [DatabaseName] [VARCHAR](255) NULL,
     [SchemaName]   [VARCHAR](255) NULL,
     [ObjectName]   [VARCHAR](255) NULL,
     [ObjectType]   [VARCHAR](255) NULL,
     [DDLCommand]   [VARCHAR](max) NULL
  )
ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO 

--Create Trigger With Selected Events
/****** Object:  DdlTrigger [Log_Table_DDL]    Script Date: 08/14/2013 11:06:04 ******/
SET ANSI_NULLS ON

GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [Log_Table_DDL]
ON ALL SERVER

FOR DDL_TABLE_EVENTS,
DDL_PROCEDURE_EVENTS,
DDL_FUNCTION_EVENTS,
DDL_VIEW_EVENTS,
DDL_TRIGGER_EVENTS,
DDL_DATABASE_SECURITY_EVENTS,
CREATE_Database,DROP_DATABASE,
DDL_LOGIN_EVENTS,
DDL_INDEX_EVENTS

AS
SET ANSI_PADDING ON
DECLARE       @eventInfo XML

SET           @eventInfo = EVENTDATA()
INSERT INTO Audit_Log VALUES

(

       REPLACE(CONVERT(VARCHAR(50),

              @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),

       CONVERT(VARCHAR(255),

              @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')),

       CONVERT(VARCHAR(255),

              @eventInfo.query('data(/EVENT_INSTANCE/UserName)')),

       CONVERT(VARCHAR(255),

              @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')),

       CONVERT(VARCHAR(50),

              @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')),

       CONVERT(VARCHAR(255),

              @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')),

       CONVERT(VARCHAR(50),

              @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')),

       CONVERT(VARCHAR(MAX),

              @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')))
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [Log_Table_DDL] ON ALL SERVER
GO 


TSQL - DML After Trigger for Insert,Update and Delete Operation

Scenario: 

Sometime we have to write a trigger on table to capture changes for different operation such as Insert, Update and Delete. Here is sample Code that can be modified according to the Source Table.

Solution:


I have created dbo.Customer Table as Source Table on which I want to create DML After trigger. After that I have created an Audit Table for dbo.Customer with name dbo.Customer_Audit which is going to save all changes.


USE TestDB
GOCREATE TABLE dbo.Customer
  (
     [CustomerID] INT IDENTITY(1, 1),
     [Name]       VARCHAR(50),
     [ADDRESS]    VARCHAR(100)
  )
GO
CREATE TABLE dbo.Customer_Audit
  (
     [CustomerID]    INT,
     [Name]          VARCHAR(50),
     [ADDRESS]       VARCHAR(100),
     [OperationDate] [DATETIME] NOT NULL,
     [Operation]     [VARCHAR](50) NOT NULL,
     [OperationBy]   [VARCHAR](100) NOT NULL
  )


--Create DML After Trigger

CREATE TRIGGER [dbo].[Tr_Customer_Audit]ON [dbo].[Customer]FOR INSERT, UPDATE, DELETE
AS
    SET NOCOUNT ON;
 --Capture the Operation (Inserted, Deleted Or Updated)

    DECLARE @operation AS VARCHAR(10)
    DECLARE @Count AS INT

    SET @operation = 'Inserted'

    SELECT @Count = COUNT(*)
    FROM   DELETED

    IF @Count > 0
      BEGIN
          SET @operation = 'Deleted'

          SELECT @Count = COUNT(*)
          FROM   INSERTED

          IF @Count > 0
            SET @operation = 'Updated'
      END
--Capturing Delete Operation

    IF @operation = 'Deleted'
      BEGIN
          INSERT INTO dbo.Customer_Audit
                      ([CustomerID],
                       [Name],
                       [ADDRESS],
                       [OperationDate],
                       [Operation],
                       [OperationBy])
          SELECT [CustomerID],
                 [Name],
                 [ADDRESS],
                 GETDATE()    AS [OperationDate],
                 'Deleted'    AS [Operation],
                 Suser_name() AS [OperationBy]
          FROM   deleted
      END
    ELSE
      BEGIN
--Capturing Insert Operation

          IF @operation = 'Inserted'
            BEGIN
                INSERT INTO dbo.Customer_Audit
                            ([CustomerID],
                             [Name],
                             [ADDRESS],
                             [OperationDate],
                             [Operation],
                             [OperationBy])
                SELECT [CustomerID],
                       [Name],
                       [ADDRESS],
                       GETDATE()    AS [OperationDate],
                       'Inserted'   AS [Operation],
                       Suser_name() AS [OperationBy]
                FROM   inserted
            END
          
--Capture Update Operation

          ELSE
            BEGIN
                INSERT INTO dbo.Customer_Audit
                            ([CustomerID],
                             [Name],
                             [ADDRESS],
                             [OperationDate],
                             [Operation],
                             [OperationBy])
                SELECT [CustomerID],
                       [Name],
                       [ADDRESS],
                       GETDATE()    AS [OperationDate],
                       'Updated'    AS [Operation],
                       Suser_name() AS [OperationBy]
                FROM   inserted
            END
      END

DBA - Find Members of AD Group in SQL Server

Sometime a user comes to you and ask you that he does not have permission on some table. You start checking if that user has required permission on object but you do not see user account but AD group added as User in your database but you are not sure if User is part of this AD group. To Find out quickly the members of AD Group we use below statement.


EXEC master.dbo.xp_logininfo 'DomainName\GroupName', 'members'

xp_logininfo also can be use to find out what Windows group a particular window user is member of.

 EXEC master.dbo.xp_logininfo 'DomainName\UserName'