To send email when package failed or successed

Create user(project) variables to have exchange server name, from email id, to email id and subject.

Get script task in evenhandler at OnError event. Select the below mentioned varibles in Read only variable option and apply the script

System::ErrorDescription
System::SourceName
System::PackageName
System::StartTime

$Project::ExchangeServerName
$Project::MailFrom
$Project::MailTo
$Project::Subject

//required namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net.Mail;
using System.Text;

public void Main()
{
// TODO: Add your code here
            string MessageTo = Dts.Variables["$Project::MailTo"].Value.ToString();
            string MessageFrom = Dts.Variables["$Project::MailFrom"].Value.ToString();
            DateTime StartTime = (DateTime)Dts.Variables["System::StartTime"].Value;
            string packagename = Dts.Variables["System::PackageName"].Value.ToString();
            string MessageSubject = packagename + " " + "Package Failed";
            string sourcename = Dts.Variables["System::SourceName"].Value.ToString();
            string MessageBody = Dts.Variables["System::ErrorDescription"].Value.ToString();
            string smtpServer = Dts.Variables["$Project::ExchangeServerName"].Value.ToString();



            SendMailMessage(MessageTo, MessageFrom, StartTime, packagename, MessageSubject, sourcename, MessageBody, true, smtpServer);

Dts.TaskResult = (int)ScriptResults.Success;
}
        private void SendMailMessage(string SendTo, string From, DateTime Starttime, string Subject, string packagename, string sourcename, string Body, bool IsBodyHtml, string Server)
        {

            MailMessage htmlMessage;
            SmtpClient mySmtpClient;

            htmlMessage = new MailMessage(SendTo, From, Subject, Body);
            StringBuilder mailstring = new StringBuilder();
            mailstring.Append("<html><body>" + "<table border='1' width='100%'> <tr border=2 bgcolor='gray'><td border=2>Start Time</td><td>Package Name</td><td>Source Name</td><td>Error Description</td></tr><tr> <td border=2>"
            + Starttime + "</td><td>" +packagename + "</td><td>" + sourcename + "</td><td>" + Body + "</td></tr> </table></body></html>");
           

            mySmtpClient = new SmtpClient(Server);
            mySmtpClient.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;
           
            htmlMessage.IsBodyHtml = true;
            htmlMessage.Body = mailstring.ToString();
            mySmtpClient.Send(htmlMessage);
        }

Batch File Script to deploy SSIS packages via DTUTIL command

The below script helps to deploy SSIS packages to MSDB folder in a server:

@Echo Off

Echo.

Echo.

Echo SSIS Package Installation Script

Echo.

if %1a == a goto Error

if %2a == a goto Error

if %3a == a goto Error

Echo.

Echo.

Echo Deployment Server: %1

Echo -----------------------------------------------------

Echo --This will delete any %3 data mart files

Echo --on the server, and reinstall from the local machine

Echo -----------------------------------------------------

Pause

REM Goto Out

REM Remove Existing files and directory on Server

for %%f in (%2"\*.dtsx") do (

Echo Now Removing: %%~nf

dtutil /Q /SourceS %1 /SQL "\%3\\%%~nf" /Del

)

dtutil /Q /SourceS %1 /FDe "SQL;\;%3"

:Create

Echo.

Echo Preparing to create folder

Echo.

pause

REM Create the Directory

dtutil /Q /SourceS %1 /FC "SQL;\;%3"

if errorlevel 1 goto End

Echo.

Echo Preparing to Copy Files to Server

Echo.

pause

:Out

REM copy the SSIS Packages to the server

for %%f in (%2"\*.dtsx") do (

Echo Now Copying: %%~nf

dtutil /Q /DestS %1 /Fi "%%f" /C "SQL;\%3\\%%~nf"

)

Echo.

Echo.

Echo Installation Complete!

Echo.

Echo.

Pause

Goto End

:Error

Echo.

Echo.

Echo Missing Servername!

Echo Syntax: Deploy SSIS Packages [servername] [Source File Path] [MSDB Deploy Folder]

Echo.

Echo.

Pause

:End

1. Copy the above code and crete a bat file (e.g., DeploySSIS).

2. Open Command Prompt and navigate to the batch file folder

3. Execute the command DeploySSIS.bat [SERVERNAME] [FILEPATH] [MSDB Sub-Folder]

Convert Unix Timestamp to Date format

SELECT *, DATE_FORMAT(FROM_UNIXTIME(TimeStamp), '%d-%m-%Y %h:%i:%s') AS LogDate
FROM dbo.Purchase
ORDER BY Timestamp DESC
LIMIT 10

Validating the Error Packages using Powershell

Create a Powershell file and save it in a required directory (For example: File name as ValidateProject.ps1 and directory like C: or D:)

Open the command prompt (open by run as an administrator)

Enter cd.. to go back and enter the directory where your powershell file is saved (type D: or C:)

Again enter as "Powershell" will get to the windows powershell mode

after this you may required the following commands (Get-ExecutionPolicy OR Set-ExecutionPolicy RemoteSigned)

//Refer - http://technet.microsoft.com/en-us/library/ee176949.aspx

Now enter the powershell file name prefix with .\ and end with which database you want to check. (For example:  .\ValidateProject.ps1 test)

This will validate all the packages in the test database and load the result in log file
// log file path mentioned in the ValidateProject.ps1 file

Below is the example Powershell code to validate error packages:

<#
.SYNOPSIS
    Validate SSIS Packages in a project
.DESCRIPTION
    This script will execute dtexec to validate all ssis packages in a directory and write the results to a log file.
.NOTES
    File Name  : ValidateProject.ps1
    Author     : Babu Kannan M
    Requires   : PowerShell V4
.LINK
#>

Param([string]$ProjectName)

if ($ProjectName -eq "imports") {
$PackageDirectory = "D:\Kannan WorkArea\Imports20\Imports_20\"
}
elseif ($ProjectName -eq "edw") {
$PackageDirectory = "D:\Kannan WorkArea\EDW20\EDW_20\"
}
elseif ($ProjectName -eq "lsdm") {
$PackageDirectory = "D:\Kannan WorkArea\LSDM20\LSDM_20\"
}
elseif ($ProjectName -eq "padm") {
$PackageDirectory = "D:\Kannan WorkArea\PADM20\PADM_20\"
}
elseif ($ProjectName -eq "webdm") {
$PackageDirectory = "D:\Kannan WorkArea\WEBDM\WEBDM\"
}
elseif ($ProjectName -eq "masterworksdatamart") {
$PackageDirectory = "D:\Kannan WorkArea\MasterWorksDataMart\MasterWorksDataMart\"
}
elseif ($ProjectName -eq "crmdm") {
$PackageDirectory = "D:\Kannan WorkArea\CRMDM\"
}
elseif ($ProjectName -eq "fdm") {
$PackageDirectory = "D:\Kannan WorkArea\FDM\FDM\"
}
else { $PackageDirectory -eq "Invalid Project" }

$LogDirectory = "D:\Powershell_Logs\"
$LogDate = get-date -f yyyy-MM-dd
$Log = [string]::Format("{0}Build_{1}.log",$LogDirectory,$LogDate)
Remove-Item $Log

$MsgOut = [string]::Format("Please pick up log files at: {0}\{1}",$LogDirectory,$Log)
write-output $MsgOut
$MsgOut = [string]::Format("Project Path for this validation is: {0}", $PackageDirectory)
write-output $MsgOut

$PackageFiles = gci $PackageDirectory"*.*" -Include *.dtsx | Where-Object {$_.PSIsContainer -eq $false} | Select-Object name

$PkgCount = $PackageFiles.Length
if ($PkgCount -eq $null) {$PkgCount = 1} #Account for 0 length array
$FailedCount = 0

$MsgOut =  [string]::Format("Validating {0} SSIS Package[s] in the {1} Project ", $PkgCount, $ProjectName)
write-output $MsgOut

foreach ($Package in $PackageFiles) {
$cmdargs = [string]::Format("-f ""{0}{1}"" /va /Report E",$PackageDirectory,$Package.Name)
$process = New-Object System.Diagnostics.Process
$process.StartInfo.FileName = "dtexec.exe"
$process.StartInfo.Arguments = $cmdargs
$process.StartInfo.UseShellExecute = $false
$process.StartInfo.RedirectStandardOutput = $true
$process.Start() | out-null;

$result = $process.StandardOutput.ReadToEnd()
$eval = $result.contains("DTSER_FAILURE")

if ($eval -eq $True) {
$MsgOut = [string]::Format("Package: {0} failed validation.", $Package.Name)
write-output $MsgOut
Add-content $Log -value $MsgOut
$errorheader = [string]::Format("==================={0} ERROR MESSAGE===================", $Package.Name)
write-output $errorheader
Add-content $Log -value $errorheader
write-output $result
Add-content $Log -value $result

$FailedCount = $FailedCount + 1
}
$process.WaitForExit()

}

$MsgOut = [string]::Format("Build Summary: {0} Package[s] of {1} failed validation during the build", $FailedCount, $PkgCount)
write-output $MsgOut
Add-content $Log -value $BuildSummary

New DataBase Creation Basic SQL Script

USE [master]
GO

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'TEST')
BEGIN
CREATE DATABASE TEST ON  PRIMARY
( NAME = N'TEST',
FILENAME = N'G:\MSSQL10.SS2008\MSSQL\Data\TEST.mdf',
SIZE = 1024 MB,
MAXSIZE = Unlimited,
FILEGROWTH = 10% )
 LOG ON
( NAME = N'TEST_Log',
FILENAME = N'H:\MSSQL10.SS2008\MSSQL\Data\TEST_log.ldf',
SIZE = 1024 MB,
MAXSIZE = 2 TB,
 FILEGROWTH = 10% )
END

GO

ALTER DATABASE TEST SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC TEST.[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE TEST SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE TEST SET ANSI_NULLS OFF
GO
ALTER DATABASE TEST SET ANSI_PADDING OFF
GO
ALTER DATABASE TEST SET ANSI_WARNINGS OFF
GO
ALTER DATABASE TEST SET ARITHABORT OFF
GO
ALTER DATABASE TEST SET AUTO_CLOSE OFF
GO
ALTER DATABASE TEST SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE TEST SET AUTO_SHRINK OFF
GO
ALTER DATABASE TEST SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE TEST SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE TEST SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE TEST SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE TEST SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE TEST SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE TEST SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE TEST SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE TEST SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE TEST SET TRUSTWORTHY OFF
GO
ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE TEST SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE TEST SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE TEST SET  READ_WRITE
GO
ALTER DATABASE TEST SET RECOVERY SIMPLE
GO
ALTER DATABASE TEST SET  MULTI_USER
GO
ALTER DATABASE TEST SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE TEST SET DB_CHAINING OFF
GO
/*End Create Database Section */


Use TEST
GO

/*Create MDR Security*/
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'AppSupport' AND type = 'R')
CREATE ROLE [AppSupport] AUTHORIZATION [dbo]
GO

--Create MDR_Designer Role
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Designer' AND type = 'R')
CREATE ROLE [Designer] AUTHORIZATION [dbo]
GO

--Create MDR_Developer Role
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Developer' AND type = 'R')
CREATE ROLE [Developer] AUTHORIZATION [dbo]
GO

--Create MDR_Tester Role
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Tester' AND type = 'R')
CREATE ROLE [Tester] AUTHORIZATION [dbo]
GO

PRINT '**Begin DB User Additions**'
/*=================================================================*/
-- ADD USERS AND ROLES for Database

declare @UserList table
( ID int IDENTITY(1, 1) NOT NULL,
ServerName varchar(250) NOT NULL,
DBName varchar(250) NOT NULL,
LoginName varchar(250) NOT NULL,
DBRole varchar(250) NOT NULL,
IsNTLogin bit NOT NULL,
SQLPWD varchar(50) NULL
)

/*-------------------- Custom Data Here ---------------------------*/
/*Add Groups*/
insert into @UserList (Servername, DBName, LoginName, DBRole, IsNTLogin)
values ('<ServerName>', 'TEST', 'MIS\gg_AppSupport', 'AppSupport', 1)
insert into @UserList (Servername, DBName, LoginName, DBRole, IsNTLogin)
values ('<ServerName>', 'TEST', 'MIS\gg_Tester', 'Tester', 1)
insert into @UserList (Servername, DBName, LoginName, DBRole, IsNTLogin)
values ('<ServerName>', 'TEST', 'MIS\gg_Developer', 'Developer', 1)
insert into @UserList (Servername, DBName, LoginName, DBRole, IsNTLogin)
values ('<ServerName>', 'TEST', 'MIS\gg_Designer', 'Designer', 1)

/*-----------------------------------------------------------------*/

declare @ID int
declare @ServerName varchar(250)
declare @DBName varchar(250)
declare @LoginName varchar(250)
declare @DBUserName varchar(250)
declare @DBRole varchar(250)
declare @IsNT bit
declare @PrevID int
declare @PWD varchar(50)

Set @ID = 0

WHILE @ID is NOT NULL
BEGIN
SELECT top 1 @ID = ID, @ServerName = ServerName, @DBName = DBName, @LoginName = LoginName, @DBRole = DBRole, @IsNT = IsNTLogin, @PWD = SQLPWD
FROM @UserList
WHERE ServerName = @@SERVERNAME and DB_Name() like DBName and ID > @ID
ORDER BY ID

print @ServerName + ' ' + @DBName + ' ' + @LoginName + ' ' + @DBRole

IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE name = @LoginName)
IF @IsNT = 1
EXEC master.dbo.sp_grantlogin @loginame = @LoginName
ELSE
EXEC master.dbo.sp_addlogin @loginame = @LoginName, @passwd = @PWD

set @DBUserName = (SELECT U.name FROM dbo.sysusers U join master.dbo.syslogins L on L.sid = U.sid WHERE L.loginname = @LoginName)

IF @DBUserName IS NULL BEGIN
EXEC dbo.sp_grantdbaccess @loginame = @LoginName, @name_in_db = @LoginName
SET @DBUserName = @LoginName
END

EXEC dbo.sp_addrolemember @DBRole, @DBUserName

IF @PrevID = @ID
set @ID = NULL
ELSE
set @PrevID = @ID



--Grant Role Permissions
--Grant Tester Permissions
if @DBRole = N'Tester' Begin
EXEC sp_addrolemember N'db_datareader', N'Tester'
Grant View Definition to Tester
Grant execute to Tester
END
--Grant MDR_AppSupport Permissions
if @DBRole = N'AppSupport' Begin
EXEC sp_addrolemember N'db_datareader', N'AppSupport'
Grant View Definition to AppSupport
Grant execute to AppSupport
END

--Grant MDR_Developer Permissions
if @DBRole = N'MDR_Developer' Begin
EXEC sp_addrolemember N'db_datareader', N'MDR_Developer'
Grant View Definition to MDR_Developer
Grant execute to MDR_Developer
END
--Grant MDR_Designer Permissions
if @DBRole = N'Designer' and (@@SERVERNAME = N'<ServerName>' or @@SERVERNAME=N'<ServerName>')  Begin
EXEC sp_addrolemember N'db_owner', N'Designer'
END
ELSE Begin
EXEC sp_addrolemember N'db_datareader', N'Designer'
Grant View Definition to Designer
Grant execute to Designer
End
END

/*server level permissions*/
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MIS\svc_CS_SS2008Agent')
CREATE LOGIN [MIS\svc_CS_SS2008Agent] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

/*db level permissions*/
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MIS\svc_CS_SS2008Agent')
CREATE USER [MIS\svc_CS_SS2008Agent] FOR LOGIN [MIS\svc_CS_SS2008Agent] WITH DEFAULT_SCHEMA=[dbo]
GO

/*Apply permissions*/
EXEC sp_addrolemember N'db_owner', N'MIS\svc_CS_SS2008Agent'

/*server level permissions*/
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MIS\svc_CS_SS2008Agent')
CREATE LOGIN [MIS\svc_CS_SS2008Agent] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

/*db level permissions*/
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MIS\svc_CS_SS2008Agent')
CREATE USER [MIS\svc_CS_SS2008Agent] FOR LOGIN [MIS\svc_CS_SS2008Agent] WITH DEFAULT_SCHEMA=[dbo]
GO

/*Apply permissions*/
EXEC sp_addrolemember N'db_owner', N'MIS\svc_CS_SS2008Agent'

USE [master]
GO
GRANT VIEW SERVER STATE TO [MIS\gg_AppSupport]
GRANT VIEW SERVER STATE TO [MIS\gg_Designer]
GRANT VIEW SERVER STATE TO [MIS\gg_Developer]
GRANT VIEW SERVER STATE TO [MIS\gg_Tester]
GO

PRINT '**DB User Addition Script complete**'

Script to calculate number sequence and insert into a table

Calculating number sequence

DECLARE @idt bigINT
SET @idt = 0
WHILE (@idt < 1000)
BEGIN
SELECT @idt = @idt + 1
insert into dbo.NumberSequence (number)
values (@idt)
END

Inserting into a table

DECLARE @idt bigINT
SET @idt = 0
WHILE (@idt < 1000)
BEGIN
SELECT @idt = @idt + 1
insert into dbo.NumberSequence (number)
values (@idt)
END

Check Digit Calculation

DECLARE @Temp1 INT
       ,@Temp2 INT
       ,@UPC VARCHAR(20)
       ,@CheckDigit INT;

SET @UPC = 547

SELECT @UPC = rtrim(ltrim(@UPC))
WHILE LEN(@UPC) < 11 SET @UPC = '0' + @UPC

SELECT @Temp1 = 0 +
SUBSTRING(@upc,1,1) +
SUBSTRING(@upc,3,1) +
SUBSTRING(@upc,5,1) +
SUBSTRING(@upc,7,1) +
SUBSTRING(@upc,9,1) +
SUBSTRING(@upc,11,1) 

SELECT @Temp1 = @Temp1 * 3

SELECT @Temp2 = 0 +
SUBSTRING(@upc,2,1) +
SUBSTRING(@upc,4,1) +
SUBSTRING(@upc,6,1) +
SUBSTRING(@upc,8,1) +
SUBSTRING(@upc,10,1) 

SELECT @Temp1 = @Temp1 + @Temp2

SELECT @Temp1 = (10- (@Temp1 % 10)) % 10

SELECT @CheckDigit = @Temp1

SELECT @CheckDigit

To pad zeros in left with the numerical value

SELECT EmpID
,REPLICATE('0',11 - LEN(EmpID))+ CAST(EmpID AS varchar ) AS EmpKey
FROM dbo.DimEmployee

Script to count rows in all tables in a database

SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name
     , SCHEMA_NAME(t.schema_id) AS schema_name
     , t.name AS table_name
     , i.rows
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i 
ON t.object_id = i.id AND i.indid < 2

Script to Create a DataBase

USE [master]
GO

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDB')
BEGIN
CREATE DATABASE TestDB ON  PRIMARY 
( NAME = N'TestDB',
FILENAME = N'G:\MSSQL10.SS2008\MSSQL\Data\TestDB.mdf',
SIZE = 1024 MB,
MAXSIZE = Unlimited,
FILEGROWTH = 10% )
 LOG ON 
( NAME = N'TestDB_Log',
FILENAME = N'H:\MSSQL10.SS2008\MSSQL\Data\TestDB_log.ldf',
SIZE = 1024 MB,
MAXSIZE = 2 TB,
 FILEGROWTH = 10% )
END

GO

ALTER DATABASE TestDB SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC TestDB.[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE TestDB SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE TestDB SET ANSI_NULLS OFF 
GO
ALTER DATABASE TestDB SET ANSI_PADDING OFF 
GO
ALTER DATABASE TestDB SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE TestDB SET ARITHABORT OFF 
GO
ALTER DATABASE TestDB SET AUTO_CLOSE OFF 
GO
ALTER DATABASE TestDB SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE TestDB SET AUTO_SHRINK OFF 
GO
ALTER DATABASE TestDB SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE TestDB SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE TestDB SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE TestDB SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE TestDB SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE TestDB SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE TestDB SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE TestDB SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE TestDB SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE TestDB SET TRUSTWORTHY OFF 
GO
ALTER DATABASE TestDB SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE TestDB SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE TestDB SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE TestDB SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE TestDB SET  READ_WRITE 
GO
ALTER DATABASE TestDB SET RECOVERY SIMPLE 
GO
ALTER DATABASE TestDB SET  MULTI_USER 
GO
ALTER DATABASE TestDB SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE TestDB SET DB_CHAINING OFF 
GO
/*End Create Database Section */


Use TestDB
GO

/*Create MDR Security*/
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'AppSupport' AND type = 'R')
CREATE ROLE [AppSupport] AUTHORIZATION [dbo]
GO

--Create MDR_Designer Role
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Designer' AND type = 'R')
CREATE ROLE [Designer] AUTHORIZATION [dbo]
GO

--Create MDR_Developer Role
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Developer' AND type = 'R')
CREATE ROLE [Developer] AUTHORIZATION [dbo]
GO

--Create MDR_Tester Role
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Tester' AND type = 'R')
CREATE ROLE [Tester] AUTHORIZATION [dbo]
GO

PRINT '**Begin DB User Additions**'
/*=================================================================*/
-- ADD USERS AND ROLES for Database

declare @UserList table 
( ID int IDENTITY(1, 1) NOT NULL,
ServerName varchar(250) NOT NULL, 
DBName varchar(250) NOT NULL, 
LoginName varchar(250) NOT NULL, 
DBRole varchar(250) NOT NULL,
IsNTLogin bit NOT NULL,
SQLPWD varchar(50) NULL
)

/*-------------------- Custom Data Here ---------------------------*/
/*Add Groups*/
insert into @UserList (Servername, DBName, LoginName, DBRole, IsNTLogin)
values ('ddwdb01cs', 'TestDB', 'MIS\gg_AppSupport', 'AppSupport', 1)
insert into @UserList (Servername, DBName, LoginName, DBRole, IsNTLogin)
values ('ddwdb01cs', 'TestDB', 'MIS\gg_Tester', 'Tester', 1)
insert into @UserList (Servername, DBName, LoginName, DBRole, IsNTLogin)
values ('ddwdb01cs', 'TestDB', 'MIS\gg_Developer', 'Developer', 1)
insert into @UserList (Servername, DBName, LoginName, DBRole, IsNTLogin)
values ('ddwdb01cs', 'TestDB', 'MIS\gg_Designer', 'Designer', 1)

/*-----------------------------------------------------------------*/

declare @ID int
declare @ServerName varchar(250)
declare @DBName varchar(250)
declare @LoginName varchar(250)
declare @DBUserName varchar(250)
declare @DBRole varchar(250)
declare @IsNT bit
declare @PrevID int
declare @PWD varchar(50)

Set @ID = 0

WHILE @ID is NOT NULL
BEGIN
SELECT top 1 @ID = ID, @ServerName = ServerName, @DBName = DBName, @LoginName = LoginName, @DBRole = DBRole, @IsNT = IsNTLogin, @PWD = SQLPWD
FROM @UserList
WHERE ServerName = @@SERVERNAME and DB_Name() like DBName and ID > @ID
ORDER BY ID

print @ServerName + ' ' + @DBName + ' ' + @LoginName + ' ' + @DBRole

IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE name = @LoginName)
IF @IsNT = 1
EXEC master.dbo.sp_grantlogin @loginame = @LoginName
ELSE
EXEC master.dbo.sp_addlogin @loginame = @LoginName, @passwd = @PWD

set @DBUserName = (SELECT U.name FROM dbo.sysusers U join master.dbo.syslogins L on L.sid = U.sid WHERE L.loginname = @LoginName)

IF @DBUserName IS NULL BEGIN
EXEC dbo.sp_grantdbaccess @loginame = @LoginName, @name_in_db = @LoginName
SET @DBUserName = @LoginName
END

EXEC dbo.sp_addrolemember @DBRole, @DBUserName

IF @PrevID = @ID
set @ID = NULL
ELSE
set @PrevID = @ID



--Grant MDR Role Permissions
--Grant MDR_Tester Permissions
if @DBRole = N'Tester' Begin
EXEC sp_addrolemember N'db_datareader', N'Tester'
Grant View Definition to MDR_Tester
Grant execute to MDR_Tester
END
--Grant MDR_AppSupport Permissions
if @DBRole = N'AppSupport' Begin
EXEC sp_addrolemember N'db_datareader', N'AppSupport'
Grant View Definition to MDR_AppSupport
Grant execute to MDR_AppSupport
END

--Grant MDR_Developer Permissions
if @DBRole = N'Developer' Begin
EXEC sp_addrolemember N'db_datareader', N'Developer'
Grant View Definition to MDR_Developer
Grant execute to MDR_Developer
END
--Grant MDR_Designer Permissions
if @DBRole = N'Designer' and (@@SERVERNAME = N'DDWDB01CS' or @@SERVERNAME=N'DDWSBDB01CS')  Begin
EXEC sp_addrolemember N'db_owner', N'Designer'
END
ELSE Begin
EXEC sp_addrolemember N'db_datareader', N'Designer'
Grant View Definition to MDR_Designer
Grant execute to MDR_Designer
End 
END

/*server level permissions*/
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MIS\svc_CS_SS2008Agent')
CREATE LOGIN [MIS\svc_CS_SS2008Agent] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

/*db level permissions*/
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MIS\svc_CS_SS2008Agent')
CREATE USER [MIS\svc_CS_SS2008Agent] FOR LOGIN [MIS\svc_CS_SS2008Agent] WITH DEFAULT_SCHEMA=[dbo]
GO

/*Apply permissions*/
EXEC sp_addrolemember N'db_owner', N'MIS\svc_CS_SS2008Agent'

/*server level permissions*/
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MIS\svc_CS_SS2008Agent')
CREATE LOGIN [MIS\svc_CS_SS2008Agent] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

/*db level permissions*/
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MIS\svc_CS_SS2008Agent')
CREATE USER [MIS\svc_CS_SS2008Agent] FOR LOGIN [MIS\svc_CS_SS2008Agent] WITH DEFAULT_SCHEMA=[dbo]
GO

/*Apply permissions*/
EXEC sp_addrolemember N'db_owner', N'MIS\svc_CS_SS2008Agent'

USE [master]
GO
GRANT VIEW SERVER STATE TO [MIS\gg_AppSupport]
GRANT VIEW SERVER STATE TO [MIS\gg_Designer]
GRANT VIEW SERVER STATE TO [MIS\gg_Developer]
GRANT VIEW SERVER STATE TO [MIS\gg_Tester]
GO

PRINT '**DB User Addition Script complete**'

Script to Shrink TempDatabase

USE tempdb
GO
 
DBCC shrinkfile (tempdev, 1024)
GO

DBCC shrinkfile (templog, 1024)
GO

DBCC shrinkfile (tempdb2, 1024)
GO

DBCC shrinkfile (tempdb3, 1024)
GO

DBCC shrinkfile (tempdb4, 1024)
GO

DBCC shrinkfile (tempdb5, 1024)
GO

DBCC shrinkfile (tempdb6, 1024)
GO

DBCC shrinkfile (tempdb7, 1024)
GO

DBCC shrinkfile (tempdb8, 1024)
GO

Script to Shrink TransactionLog

USE TESTDB  
GO

DBCC SHRINKFILE (N'TESTDB_Log', 0, TRUNCATEONLY)
GO

How to Find Tables With Primary and Foreign Key Constraint in Database

Table with ForiegnKey constraint

USE EDW; --Database name
GO
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

Table with PrimaryKey constraint


USE EDW;  --Database name
GO
SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

Find a table in a database

Below is the code to find a table in a database

USE XXXX  --Database name

SELECT * FROM sys.Tables
WHERE name LIKE '%Address%'

Below is the code to find a table in all database with stored procedure


USE Master
GO
CREATE PROCEDURE usp_FindTableNameInAllDatabase
@TableName VARCHAR(256)
AS
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)
DECLARE @getDBName CURSOR
SET @getDBName = CURSOR FOR
SELECT name
FROM sys.databases
CREATE TABLE #TmpTable (DBName VARCHAR(256),
SchemaName VARCHAR(256),
TableName VARCHAR(256))
OPEN @getDBName
FETCH NEXT
FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @varSQL = 'USE ' + @DBName + ';
INSERT INTO #TmpTable
SELECT '''+ @DBName + ''' AS DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
name AS TableName
FROM sys.tables
WHERE name LIKE ''%' + @TableName + '%'''
EXEC (@varSQL)
FETCH NEXT
FROM @getDBName INTO @DBName
END
CLOSE @getDBName
DEALLOCATE @getDBName
SELECT *
FROM #TmpTable
DROP TABLE #TmpTable

To run the above store proc:
EXEC usp_FindTableNameInAllDatabase 'xxxTablenamexxx'    --Table name

If Exists Logic

If exists logic for a Database

IF EXISTS(select * from sys.databases where name='yourDBname')

If exists logic for a table

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimPACodes]') AND type in (N'U'))

If exists logic for a synonms

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N's_DimPACodes') AND type in (N'SN'))

If exists logic for a View

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'vw_DimProgram') AND type in (N'V'))

If exists logic for a column

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Dimprogram' and COLUMN_NAME = 'IAPKey')

If exists logic for a Schema

IF NOT EXISTS(SELECT * FROM sys.schemas where name = N'Test')

EXEC ('CREATE SCHEMA [telligent] Authorization [dbo]')

GO

(OR)

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'Test')

EXEC ('CREATE SCHEMA [Test] Authorization [dbo]')

GO

Interpreting type codes in sys.objects in SQL Server

AF = Aggregate function (CLR)
C  = CHECK constraint
D  = DEFAULT (constraint or stand-alone)
F  = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P  = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R  = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S  = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U  = Table (user-defined)
UQ = UNIQUE constraint
V  = View
X  = Extended stored procedure

To search a string in every column of every table in a specific database

To search a string in every column of every table in a specific database. Create the stored procedure on the database that you want to search in.


CREATE PROCEDURE FindMyData_String
    @DataToFind NVARCHAR(4000),
    @ExactMatch BIT = 0
AS
SET NOCOUNT ON

DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)

    INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
    SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
    FROM    Information_Schema.Columns AS C
            INNER Join Information_Schema.Tables AS T
                ON C.Table_Name = T.Table_Name
        AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
    WHERE   Table_Type = 'Base Table'
            And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')


DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)

SELECT  @SQLTemplate = CASE WHEN @ExactMatch = 1
                            THEN 'If Exists(Select *
                                          From   ReplaceTableName
                                          Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                       = ''' + @DataToFind + '''
                                          )
                                     Set @DataExists = 1
                                 Else
                                     Set @DataExists = 0'
                            ELSE 'If Exists(Select *
                                          From   ReplaceTableName
                                          Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                       Like ''%' + @DataToFind + '%''
                                          )
                                     Set @DataExists = 1
                                 Else
                                     Set @DataExists = 0'
                            END,
        @PARAMETERS = '@DataExists Bit OUTPUT',
        @i = 1

SELECT @i = 1, @MAX = MAX(RowId)
FROM   @Temp

WHILE @i <= @MAX
    BEGIN
        SELECT  @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
        FROM    @Temp
        WHERE   RowId = @i


        PRINT @SQL
        EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT

        IF @DataExists =1
            UPDATE @Temp SET DataFound = 1 WHERE RowId = @i

        SET @i = @i + 1
    END

SELECT  SchemaName,TableName, ColumnName
FROM    @Temp
WHERE   DataFound = 1
GO

To run it just do this:
exec FindMyData_string 'yahoo', 0     --here yahoo is the string name

Script to check the last run status of job

SQL script to check the last run status of a job. This code will retrieve the last run status.


ECLARE @last_run_outcome INT 
SET @last_run_outcome = ( 
SELECT MAX(sjs.last_run_outcome) 
FROM msdb.dbo.sysjobs_view sjv 
INNER JOIN msdb.dbo.sysjobsteps sjs ON sjs.job_id = sjv.job_id 
WHERE name in ('MDR20') 
AND last_run_outcome = 0) 

--SELECT @last_run_outcome  

IF @last_run_outcome = 0 
EXEC msdb.dbo.sp_stop_job N'MDR20' 

Below is the outcome of the job for the last time ran:

0 = Failed
1 = Succeeded
3 = Canceled
5 = Unknown

Using CHARINDEX in SQL Query

CHARINDEX returns a starting position of a string which helps developer to split a data based on the requirement.


DECLARE @AMT Varchar(50) = '$50K - $70K'
SELECT REPLACE(SUBSTRING('$50K - $70K',charindex('$', '$50K - $70K')+1, charindex('K', @AMT)),'K','') AS MinRange
,REPLACE(SUBSTRING('$50K - $70K',charindex('-', '$50K - $70K')+3, charindex('K', @AMT)),'K','') As MaxRange

Result:

MinRange         MaxRange
    50                      70

Get Hierarchial Level of Employee Using SQL

Create a new table as shown below:

CREATE TABLE [dbo].[Employee](
                    [EmployeeID] [int] NOT NULL,
                    [EmpName] [varchar](100) NULL,
                    [Department] [varchar](100) NULL,
                    [Designation] [varchar](100) NULL,
                    [Salary] [money] NULL,
                    [ManagerID] [int] NULL
    ) ON [PRIMARY]

Insert valuse to the table as required and apply the below script:

SELECT L3.EmpName AS LEVEL2, L2.EmpName LEVEL1, L1.EmpName LEVEL0
    ,CAST(ISNULL(L3.EmployeeID,'') AS VARCHAR(5))+'/' +
CAST(ISNULL(L2.EmployeeID,'') AS VARCHAR(5))+'/' +
CAST(ISNULL(L1.EmployeeID,'') AS VARCHAR(5)) AS LEVELPATH
,CAST(ISNULL(L3.EmpName,'') AS VARCHAR(5))+'/' +
CAST(ISNULL(L2.EmpName,'') AS VARCHAR(5))+'/' +
CAST(ISNULL(L1.EmpName,'') AS VARCHAR(5)) AS EMPLOYETREE
FROM Employee L1
LEFT JOIN Employee L2 ON L1.ManagerID = L2.EmployeeID
LEFT JOIN Employee L3 ON L2.ManagerID = L3.EmployeeID
LEFT JOIN Employee L4 ON L3.ManagerID = L4.EmployeeID
LEFT JOIN Employee L5 ON L4.ManagerID = L5.EmployeeID

Below is the result:

Configure Change Data Capture (CDC) Parameters in SQL Server

After enabling CDC in SQL Server, We can modify the retention period and the number of transactions that to be handled in Change Data Capture table.

Before configureing one should understand the basic terms in CDC Configuration:

Polling interval – (Default 5 seconds) the amount of time to wait between log scans

Maxtrans – (Default 500) the number of transactions to grab with each scan

Maxscans – (Default 10) the number of scans performed between each polling interval

Retention – (Default 72 hours, 4320 mins, 3 days). 

The period for which the new/updated/deleted data have to be retrieved and displayed in CDC tables. 

Execute the below query to get the CDC configured values:

SELECT * from msdb.dbo.cdc_jobs

Execute the below query to change capture instances:

EXEC sys.sp_cdc_change_job @job_type = 'capture'

       ,@maxtrans = 501

       ,@maxscans = 10

       ,@continuous = 1

       ,@pollinginterval = 5

Execute the below query to change retention period:

EXEC sys.sp_cdc_change_job @job_type = 'cleanup'

                          ,@retention = 4320 -- Number of minutes to retain (72 hours)

                          ,@threshold = 5000

Using this method we can use CDC hold the required period of historical data, i.e., for last 1 month, last 1 year or last 10 days, etc.