I’m just trying to configure a custom logging mechanism for my SSIS packages and had some problems with the location and structure of the dbo.sysssislog table which is automagically created by the Integration Services (if you happen to use a integrated security database connection; else you need to grant CREATE rights to the corresponding database user). While googling I found some helpful tips by Joe The Business Intelligence Guy:

1. Can I change the dbo.sp_ssis_addlogentry system stored procedure to non-system stored procedure and if so how is this done?
   Yes – you must drop and recreate the procedure (and don’t run [sp_MS_marksystemobject] N’[dbo].[sp_ssis_addlogentry]‘)
   
   
2. Can I change the dbo.sysssislog system table to non-system table and if so how is this done?
   Yes – you must drop and recreate the table (and don’t run execute [sp_MS_marksystemobject] N’[sysssislog]‘)
   
   
3. Is the new SSIS 2008 logging stored procedure dbo.sp_ssis_addlogentry different than the SSIS 2005 logging table dbo.sp_dts_addlogentry?
   No
   
   
4. Is the new SSIS 2008 logging table dbo.sysssislog different than the SSIS 2005 logging table dbo.sysdtslog90?
   No, the schema is the same
   
   
5. Can I change the dbo.sp_ssis_addlogentry procedure to reference a different named table other than dbo.sysssislog?
   Yes – you must drop and recreate the procedure changing the referenced table name found after INSERT INTO
   I often name this table as Audit.ETLAuditSSISLog
   
   
6. Can I change the name of the dbo.sysdtslog90 table?
   Yes – you must drop and recreate the table
   Additionally, you must drop and recreate the SSIS 2008 logging stored procedure dbo.sp_ssis_addlogentry
   in order to change the referenced table name found after INSERT INTO
   I often name this table as Audit.ETLAuditSSISLog
   
   
7. Can I change the name of the dbo.sp_ssis_addlogentry procedure?
   No – SSIS will simply recreate the procedure if it is missing while the solution/package is configured to used SQL server logging.

Additionally, if you intend to use the sysssislog table in database different from msdb, you just need to use a custom connection manager, pointing to the appropriate database. Here’s the CREATE script for your conveniance:

CREATE TABLE [dbo].[sysssislog](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [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,
PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON)
 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

You’ll also need to create the stored procedure sp_ssis_addlogentry:

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

Unfortunately I could not find a way to create the table or procedure in a different schema. The package logging mechanism expects them to be in the dbo schema and I couldn’t find a way to customize that behaviour to my likings.

(via)