ttajic
10/26/2016 - 8:43 AM

Create SSIS Log Database and Table

Create SSIS Log Database and Table

-- !!! SQL Connection User must have create db permissions
-- Create SQL Execute Task and Copy/Paste the script into it ath the beginning

IF NOT EXISTS(SELECT name FROM sys.databases WHERE name = 'NAVCRM_SYNC')
  BEGIN
    CREATE DATABASE NAVCRM_SYNC
  END
 
USE NAVCRM_SYNC;
IF NOT EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'Log' )
CREATE TABLE [dbo].[LOG]
(
[TimeStamp] DATETIME NOT NULL CONSTRAINT DF_LOG_TIMESTAMP DEFAULT GETDATE(), 
[ErrorStatus] int NULL,
[RowID] NVARCHAR(4000) NULL,
[Description] NVARCHAR(4000) NULL, 
[Description1] NVARCHAR(4000) NULL, 
[Description2] NVARCHAR(4000) NULL, 
[Source] NVARCHAR(30),
[Destination] NVARCHAR(30)
)

INSERT INTO [NAVCRM_SYNC].[dbo].[LOG]
		([ErrorStatus]
		,[RowID]
		,[Description]
		,[Description1]
		,[Description2]
		,[Source]
		,[Destination] )
	VALUES
		(0
		,NULL
		,'START NAV to CRM'
		,NULL
		,NULL
		,'CRM'
		,'NAV');
		
		
	-- DELETE OLD Logs
	
	DELETE FROM LOG WHERE TimeStamp < DATEADD(day, -60, GETDATE())