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())