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.