Tag Archives: Script

Create dbo.SysSSISLOG Table in your Database

When you script your database and want to include the dbo.SYSSSISLOG table, because you’ve created Views or procedures referencing it, the following piece of code does the job:

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sysssislog]'))
CREATE TABLE [sysssislog] (	[id] [int] NOT NULL IDENTITY PRIMARY KEY,	[event] [sysname] NOT NULL,	[computer] [nvarchar] (128) NOT NULL,	[operator] [nvarchar] (128) NOT NULL,	[source] [nvarchar] (1024) NOT NULL,	[sourceid] [uniqueidentifier] NOT NULL,	[executionid] [uniqueidentifier] NOT NULL,	[starttime] [datetime] NOT NULL,	[endtime] [datetime] NOT NULL,	[datacode] [int] NOT NULL,	[databytes] [image] NULL,	[message] [nvarchar] (2048) NOT NULL,) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
go
CREATE PROCEDURE [dbo].[sp_ssis_addlogentry]  @event sysname,  @computer nvarchar(128),  @operator nvarchar(128),  @source nvarchar(1024),  @sourceid uniqueidentifier,  @executionid uniqueidentifier,  @starttime datetime,  @endtime datetime,  @datacode int,  @databytes image,  @message nvarchar(2048)AS  INSERT INTO sysssislog (      event,      computer,      operator,      source,      sourceid,      executionid,      starttime,      endtime,      datacode,      databytes,      message )  VALUES (      @event,      @computer,      @operator,      @source,      @sourceid,      @executionid,      @starttime,      @endtime,      @datacode,      @databytes,      @message )  RETURN 0
go
execute [sp_MS_marksystemobject] N'[dbo].[sp_ssis_addlogentry]'
go
execute [sp_MS_marksystemobject] N'[sysssislog]'
go

The previous script is the same code that Visual Studio or SSIS itself executes when running a ETL Package the first time.