CODEWITHSUNDEEP
sql-serverssis
Greg
Greg

Reputation: 47164

SSIS - Log to table other than SYSSSISLOG

SSIS seems to insist on logging to the system table SYSSSISLOG. Is there a way to make it use a different table?

I want each package to log to a different table.

Upvotes: 6

Views: 19443

Answers (3)

Mark Timson
Mark Timson

Reputation: 33

The above is quite correct however not written well. When you specify your logging in SSIS you can log to a specific data provider IE SSIS Log provider for SQL Server. When you point this to a specific database it will create a [dbo].[sysssislog] table under the System Tables folder in your database. If you navigate in SSMS to your database and programmability -> Stored Procedures there will be a procedure called [dbo].[sp_ssis_addlogentry] this will insert log entries from SSIS. You can repoint this stored procedure to point to the table you want to log to instead of the one generated by SSIS within your database.

Upvotes: 0

William Salzman
William Salzman

Reputation: 6446

Quick answer is the same as John Sansom's answer: When logging is used, it creates a table and a stored proc (name varies with version between 2005 and 2008) The stored proc can be modified to do whatever you want. If the stored proc is removed Sql server re-creates it, but if the stored proc is there, Sql server assumes it is OK and leaves it alone. This allows you to modify the stored proc to write to whatever table/tables you want.

Upvotes: 6

Denis Valeev
Denis Valeev

Reputation: 6015

Well, you can query that huge-ass log table with something like this:

--first, we identify the packages
;with DetectedPackages as (
select source, s.executionid
from dbo.sysssislog as s
where event = 'PackageStart'
group by source, s.executionid
)
--then we use those executionids to display results
select * from dbo.sysssislog as s
join DetectedPackages dp on s.executionid = dp.executionid
where dp.source = 'PackageName'

And if you want to encapsulate every package in a view, now you know how to do that.

Upvotes: 2

Related Questions