Tag Archives: SSIS

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.

SSIS Error: 0xc001000e The connection manager “” is not found.

Solution:

If your .dtsx XML file (search for LogProv) does not contain the tag “DTS:ConfigString=“, you missed to populate the Connection Configuration DropDown in the Logging-Wizard dialog:

<DTS:LogProviders>
    <DTS:LogProvider
      DTS:ConfigString="CON_DWH"
      DTS:CreationName="Microsoft.LogProviderSQLServer"
      DTS:Description="Schreibt Protokolleinträge für Ereignisse in eine SQL Server-Datenbank."
      DTS:DTSID="{9FE46402-8C22-49AB-887C-056C89DD00CE}"
      DTS:ObjectName="SSIS-Protokollanbieter für SQL Server">
      <DTS:ObjectData>
        <InnerObject />
      </DTS:ObjectData>
    </DTS:LogProvider>
  </DTS:LogProviders>