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]

SQL to Track Running Jobs

Below is the query to identify the SQL Jobs running in a server:

Exec msdb..sp_help_job @execution_status = 1

T-SQL to Disable all Jobs in SQL Agent

USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO

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

T-SQL to identify long running queries

Below queries helps to identify long running query and hanging queries:

USE MASTER
GO
SELECT SPID,ER.percent_complete,

CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
DATEADD(second,estimated_completion_time/1000, getdate()) as est_completion_time,

/* End of Article Code */

ER.command,ER.blocking_session_id, SP.DBID,LASTWAITTYPE, 

DB_NAME(SP.DBID) AS DBNAME, 

SUBSTRING(est.text, (ER.statement_start_offset/2)+1,
((CASE ER.statement_end_offset
WHEN -1 THEN DATALENGTH(est.text)
ELSE ER.statement_end_offset
END - ER.statement_start_offset)/2) + 1) AS QueryText,
TEXT,CPU,HOSTNAME,LOGIN_TIME,LOGINAME,
SP.status,PROGRAM_NAME,NT_DOMAIN, NT_USERNAME
FROM SYSPROCESSES SP
INNER JOIN sys.dm_exec_requests ER
ON sp.spid = ER.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST
ORDER BY CPU DESC

// This code has been referred from an article.

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

CDC Logging Reset SQL Code

Use TestDB
GO

Update cdc_logging
set StartDateTime = getdate() - 13, 
    EndDateTime = getdate(), 
    ExtractTime = null 

--select * from dbo.cdc_logging

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**'

Google Analytics Tables Structure Design

Google Analytics tables structure design with SingleMonth, Quarter and FiscalYearToDate(FYTD) format

USE [TestDataBase]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [web].[CriteriaBySite](
[ProfileId] [int] NOT NULL,
[ProfileValue] [varchar](24) NOT NULL,
[Criteria] [varchar](100) NOT NULL,
[AuditTaskID] [int] NULL,
[RecordExists] [bit] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[PagesDetailByURL_FYTD](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[PageURL] [nvarchar](max) NULL,
[PageViewCount] [int] NULL,
[UniquePageViewCount] [int] NULL,
[AvgTimeOnPage] [numeric](18, 2) NULL,
[EntranceCount] [int] NULL,
[BounceRate] [numeric](18, 2) NULL,
[ExitPercent] [numeric](18, 2) NULL,
[TotalValue] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[PagesDetailByURL_Quarter](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[PageURL] [nvarchar](max) NULL,
[PageViewCount] [int] NULL,
[UniquePageViewCount] [int] NULL,
[AvgTimeOnPage] [numeric](18, 2) NULL,
[EntranceCount] [int] NULL,
[BounceRate] [numeric](18, 2) NULL,
[ExitPercent] [numeric](18, 2) NULL,
[TotalValue] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [web].[PagesDetailByURL_SingleMonth](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[PageURL] [nvarchar](max) NULL,
[PageViewCount] [int] NULL,
[UniquePageViewCount] [int] NULL,
[AvgTimeOnPage] [numeric](18, 2) NULL,
[EntranceCount] [int] NULL,
[BounceRate] [numeric](18, 2) NULL,
[ExitPercent] [numeric](18, 2) NULL,
[TotalValue] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[PagesDetailByURLByCriteria_FYTD](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Criteria] [varchar](100) NOT NULL,
[PageURL] [nvarchar](max) NULL,
[PageViewCount] [int] NULL,
[UniquePageViewCount] [int] NULL,
[AvgTimeOnPage] [numeric](18, 2) NULL,
[EntranceCount] [int] NULL,
[BounceRate] [numeric](18, 2) NULL,
[ExitPercent] [numeric](18, 2) NULL,
[TotalValue] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[PagesDetailByURLByCriteria_Quarter](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Criteria] [varchar](100) NOT NULL,
[PageURL] [nvarchar](max) NULL,
[PageViewCount] [int] NULL,
[UniquePageViewCount] [int] NULL,
[AvgTimeOnPage] [numeric](18, 2) NULL,
[EntranceCount] [int] NULL,
[BounceRate] [numeric](18, 2) NULL,
[ExitPercent] [numeric](18, 2) NULL,
[TotalValue] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[PagesDetailByURLByCriteria_SingleMonth](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Criteria] [varchar](100) NOT NULL,
[PageURL] [nvarchar](max) NULL,
[PageViewCount] [int] NULL,
[UniquePageViewCount] [int] NULL,
[AvgTimeOnPage] [numeric](18, 2) NULL,
[EntranceCount] [int] NULL,
[BounceRate] [numeric](18, 2) NULL,
[ExitPercent] [numeric](18, 2) NULL,
[TotalValue] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[PagesDetailByURLByCriteriaHistory_FYTD](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Criteria] [varchar](100) NOT NULL,
[PageURL] [nvarchar](max) NULL,
[PageViewCount] [int] NULL,
[UniquePageViewCount] [int] NULL,
[AvgTimeOnPage] [numeric](18, 2) NULL,
[EntranceCount] [int] NULL,
[BounceRate] [numeric](18, 2) NULL,
[ExitPercent] [numeric](18, 2) NULL,
[TotalValue] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[PagesDetailByURLByCriteriaHistory_Quarter](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Criteria] [varchar](100) NOT NULL,
[PageURL] [nvarchar](max) NULL,
[PageViewCount] [int] NULL,
[UniquePageViewCount] [int] NULL,
[AvgTimeOnPage] [numeric](18, 2) NULL,
[EntranceCount] [int] NULL,
[BounceRate] [numeric](18, 2) NULL,
[ExitPercent] [numeric](18, 2) NULL,
[TotalValue] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[PagesDetailByURLByCriteriaHistory_SingleMonth](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Criteria] [varchar](100) NOT NULL,
[PageURL] [nvarchar](max) NULL,
[PageViewCount] [int] NULL,
[UniquePageViewCount] [int] NULL,
[AvgTimeOnPage] [numeric](18, 2) NULL,
[EntranceCount] [int] NULL,
[BounceRate] [numeric](18, 2) NULL,
[ExitPercent] [numeric](18, 2) NULL,
[TotalValue] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [web].[SiteProfile](
[ProfileId] [int] NOT NULL,
[ProfileValue] [varchar](24) NOT NULL,
[SiteURL] [varchar](2000) NOT NULL,
[Title] [varchar](120) NOT NULL,
[InceptionDate] [date] NOT NULL,
[Account] [varchar](120) NOT NULL,
[UAID] [varchar](120) NOT NULL,
[AuditTaskID] [int] NULL,
[RecordExists] [bit] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [web].[SiteProfileHistory_Date](
[UniqueId] [int] IDENTITY(1,1) NOT NULL,
[ProfileId] [varchar](24) NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[MonthStartDate] [date] NULL,
[MonthEndDate] [date] NULL,
[QuarterStartDate] [date] NULL,
[QuarterEndDate] [date] NULL,
[YearStartDate] [date] NULL,
[YearEndDate] [date] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [web].[SiteProfileHistoryDate_FYTD](
[UniqueId] [int] IDENTITY(1,1) NOT NULL,
[ProfileId] [varchar](24) NULL,
[FYTDStartDate] [date] NULL,
[FYTDEndDate] [date] NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [web].[SiteProfileHistoryDate_Quarter](
[UniqueId] [int] IDENTITY(1,1) NOT NULL,
[ProfileId] [varchar](24) NULL,
[FiscalQuarterStartDate] [date] NULL,
[FiscalQuarterEndDate] [date] NULL,
[FiscalYear] [smallint] NULL,
[FiscalQuarter] [tinyint] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[Visitor_FYTD](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Visits] [int] NULL,
[UniqueVisitors] [int] NULL,
[PageViews] [int] NULL,
[PagesPerVisit] [numeric](18, 2) NULL,
[AvgVisitDuration] [numeric](18, 2) NULL,
[BounceRate] [numeric](18, 2) NULL,
[PercentNewVisits] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[Visitor_Quarter](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Visits] [int] NULL,
[UniqueVisitors] [int] NULL,
[PageViews] [int] NULL,
[PagesPerVisit] [numeric](18, 2) NULL,
[AvgVisitDuration] [numeric](18, 2) NULL,
[BounceRate] [numeric](18, 2) NULL,
[PercentNewVisits] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [web].[Visitor_SingleMonth](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Visits] [int] NULL,
[UniqueVisitors] [int] NULL,
[PageViews] [int] NULL,
[PagesPerVisit] [numeric](18, 2) NULL,
[AvgVisitDuration] [numeric](18, 2) NULL,
[BounceRate] [numeric](18, 2) NULL,
[PercentNewVisits] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [web].[VisitorHistory](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalQuarter] [tinyint] NULL,
[FiscalMonth] [tinyint] NULL,
[Visits] [int] NULL,
[UniqueVisitors] [int] NULL,
[PageViews] [int] NULL,
[PagesPerVisit] [numeric](18, 2) NULL,
[AvgVisitDuration] [numeric](18, 2) NULL,
[BounceRate] [numeric](18, 2) NULL,
[PercentNewVisits] [numeric](18, 2) NULL,
[NewVisits] [numeric](18, 0) NULL,
[ReturningVisits] [numeric](18, 0) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [web].[VisitorHistory_FYTD](
[ProfileID] [int] NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalQuarter] [tinyint] NULL,
[FiscalMonth] [tinyint] NULL,
[Visits] [int] NULL,
[UniqueVisitors] [int] NULL,
[PageViews] [int] NULL,
[PagesPerVisit] [numeric](18, 2) NULL,
[AvgVisitDuration] [numeric](18, 2) NULL,
[BounceRate] [numeric](18, 2) NULL,
[PercentNewVisits] [numeric](18, 2) NULL,
[NewVisits] [int] NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [web].[VisitorHistory_Quarter](
[ProfileID] [int] NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Visits] [int] NULL,
[UniqueVisitors] [int] NULL,
[PageViews] [int] NULL,
[PagesPerVisit] [numeric](18, 2) NULL,
[AvgVisitDuration] [numeric](18, 2) NULL,
[BounceRate] [numeric](18, 2) NULL,
[PercentNewVisits] [numeric](18, 2) NULL,
[NewVisits] [int] NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [web].[VisitorHistory_SingleMonth](
[ProfileID] [int] NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Visits] [int] NULL,
[UniqueVisitors] [int] NULL,
[PageViews] [int] NULL,
[PagesPerVisit] [numeric](18, 2) NULL,
[AvgVisitDuration] [numeric](18, 2) NULL,
[BounceRate] [numeric](18, 2) NULL,
[PercentNewVisits] [numeric](18, 2) NULL,
[NewVisits] [int] NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[VisitorType_FYTD](
[ProfileID] [int] NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalQuarter] [tinyint] NULL,
[FiscalMonth] [tinyint] NULL,
[VisitorType] [varchar](20) NULL,
[Visits] [int] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[VisitorType_Quarter](
[ProfileID] [int] NOT NULL,
[FiscalYear] [int] NULL,
[FiscalQuarter] [int] NULL,
[VisitorType] [varchar](20) NULL,
[Visits] [int] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[VisitorType_SingleMonth](
[ProfileID] [int] NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalQuarter] [tinyint] NULL,
[FiscalMonth] [tinyint] NULL,
[VisitorType] [varchar](20) NULL,
[Visits] [int] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO