Microsoft SQL Server Integration Services (SSIS) 2008 and logging with the SSIS log provider for SQL Server

If you use the SSIS log provider for SQL Server in Microsoft SQL Server Integration Services (SSIS) 2008, the provider will create a table ‘sysssislog’ in the destination database. The table will be created with schema name. If the SSIS package is run under the “dbo” the table will be created as “dbo.sysssislog”, but if the package is run under a database user which is not “dbo” of the database the tabled will be created like: “Domain1\User2.sysssislog”. The system stored procedures that are used for logging, aspect de table schema name to be “dbo” en not “Domain1\User2”, so it will fail with error, see below.

We use a sql server agent job to run a SSIS package. The job step is set to “Run as” a proxy account, which is authorized for the subsystem “SQL Server Integration Services Package”. The proxy is coupled to a credential, the credential is coupled to a windows account that has “Run as a Job” permissions and is member of the msdb database role “’SQLAgentUserRole”. The account that runs the SSIS package is not “dbo”, because the database is created by another account.

 

Workarround
Because we want the database to be installed by user “Domain1\User1” and the “sql server agent job step that executes the SSIS package” to be run as user “Domain1\User2”, we can’t let the SSIS package create the table automatically. We create the table during database installation by the “Domain1\User1” account. The table is then created as “dbo.sysssislog”.

Table create script

create table [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]
go

Error Message

Executed as user: Domain1\User2. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.1600.22 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  2:39:01 PM  Error: 2009-07-30 14:39:03.59     Code: 0xC0202009     Source: Controleer nieuwe foutmeldingen Ophalen foutmeldingen [31]     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Invalid object name ‘sysssislog’.".  End Error  Error: 2009-07-30 14:39:03.59     Code: 0xC020204A     Source: Controleer nieuwe foutmeldingen Ophalen foutmeldingen [31]     Description: Unable to retrieve column information from the data source. Make sure your target table in the database is available.  End Error  Error: 2009-07-30 14:39:03.59     Code: 0xC004706B     Source: Controleer nieuwe foutmeldingen SSIS.Pipeline     Description: "component "Ophalen foutmeldingen" (31)" failed validation and returned validation status "VS_ISBROKEN".  End Error  Error: 2009-07-30 14:39:03.59     Code: 0xC004700C     Source: Controleer nieuwe foutmeldingen SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2009-07-30 14:39:03.59     Code: 0xC0024107     Source: Controleer nieuwe foutmeldingen      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  2:39:01 PM  Finished: 2:39:03 PM  Elapsed:  2.109 seconds.  The package execution failed.  The step failed.

Leave a Reply

Your email address will not be published. Required fields are marked *