Post reply

SSIS Logging Variable Values

  • April 21, 2011 at 3:35 pm

    #145496

    I have a fairly basic SSIS package which contains several variables which can be overridden at runtime. I am using standard SQL Server logging which works well.

    I would like to log the variable values to the standard SQL SSIS log table when the package starts.

    Could someone provide some options to do this? Simple is preferred if possible.

    Thanks!

  • April 21, 2011 at 10:09 pm

    #1314778

    [font="Tahoma"]

    Hello Bob,

    I am not sure if we would be able to include columsn to store the values of the variables in the table created by the sql server log provider.

    sysssislog is the table which would be created and storing the log details after the logging is enabled. The values in these tables are inserted by the sp [sp_ssis_addlogentry] which would be created in the DB which was configured in the OLE DB Connection Manager while configuring the Logging.

    Rather you can create a user defined table and modify this stored procedure to add values of the variables to the user defined table.

    I had posted a topic on similar discussion and you can find the same in http://www.sqlservercentral.com/Forums/Topic1096762-364-1.aspx.

    Again, i am not sure if it would be possible for us to include columns in sysssistable.

    Thanks

    Regards

    [/font]

  • December 15, 2014 at 12:25 pm

    #1764778

    I have a blog post to show how I logged variable values to the default logging table [dbo].[sysssislog]. The log is created "when the variable value changes".
    http://www.sqlerudition.com/how-to-log-ssis-variable-values-during-execution-in-the-event-log/

    My example uses the OnVariableValueChanged event (and its scoped variable [System::VariableValue]) to run an Execute SQL Task that writes values to the table. Of course, the Execute SQL Task can be easily modified to log the variable values to your own custom table.

    If you want to log values "as soon as the package starts", then you'll need to write a Script Task or an Execute SQL Task in the Control Flow, or even the Pre-Execute event, of the package. Although the [System::VariableValue] will not be available to you because it is limited to the scope of OnVariableValueChanged event.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply