The sysdtslog90 table is used by Microsoft SQL Server 2005 SSIS to log messages. If you want to use this table for custom logging, you can user event handlers on SSIS tasks.

– Select the task that uses a package variable

– Click on the tab [Event Handlers]

– Select [OnPreExecute], if you want to log the value of the parameter before the task is executed.

– Select [OnPostExecute], if you want to log the value of the parameter after the task is executed.

– Add a SQL Task from the Toolbox (control flow items)

image

– Use the following parameter mappings (the User::FileOutputPath is the package variable I want to log):

image

– Use as query:

image

-- Determine current date and time
declare @now datetime
select @now = getdate()

declare @up1 nvarchar(128)
declare @up2 nvarchar(128)
declare @up3 nvarchar(128)
declare @up4 nvarchar(128)
declare @up5 nvarchar(128)
set @up1 = ?
set @up2 = ?
set @up3 = ?
set @up4 = ?
set @up5 = ?

declare @message nvarchar(2048)
set @message = 'FileOutputPath=[' + isnull(?, ' ') + ']'

-- Add a record to the table [ZKP_Audit].[dbo].[sysdtslog90]
exec sp_dts_addlogentry 

'OnPreExecute',                -- [event]:        Eventname

@up1,                    -- [computer]:    Machinename

@up2,                    -- [operator]:    Username

@up3,                    -- [source]:    Taskname

@up4,                    -- [sourceid]:    Task GUID

@up5,                    -- [executionid]:    Package GUID

@now,                    -- [starttime]:    Current date and time

@now,                    -- [endtime]:    Current date and time

0,                    -- [datacode]:    Not used, always 0

0x,                    -- [databytes]:    Not used, always 0x

@message                 -- [message]:    Message containing te parameterinformation

Result in the table sysdtslog90:

 

14    14    OnPreExecute    DEV2005    DEV2005\Administrator    Log package variables    5325CAAB-AC92-456E-B206-8DF8DA4081A8    013DC323-7A0D-48FC-BAA0-07074FC765EE    2011-05-12 11:28:44.947    2011-05-12 11:28:44.947    0    0x    FileOutputPath=[C:\Projects\De Grote Rivieren\Dagstaten\Solution\SSIS\ZKP_Dagstaten2Psygis\Files\Export\]

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.