Logging in the SSIS Script Task and Script Component

Recently I had some fun with logging in the SSIS script component and I thought I’d share it. I have an SSIS package that uses scripts in a data flow to write to Dynamics CRM Web Services. I wanted to add some logging to record the records that got updated. I found the MSDN article Logging in the Script Task but was unable to follow it. The article says that I should be able to use:

Dts.Log(“Rows processed: ” + rowsProcessed.ToString(), 0, emptyBytes);

However when I tried it there was no Dts.Log to be found. Well the answer is this.

Control Flows have Script Tasks and Data Flows have Script Components. Logging is separate and the way it is handled is different.

To Enable Logging for the Script Task:

  • On the SSIS Menu select Logging.
  • Add a Provider (I’m using the Log Provider for SQL Server and under its configuration have selected an OLEDB connection manager that points at the database that I want to record the logs in
  • Enable Logging for the Package in the Containers section
  • Click the grey tick for the script task that you want to log (sequence containers that house it/them)
  • Add log code as per the link above to your scripts
Log Provider for SQL Server Image

Log Provider for SQL Server

Script Task Log Settings Image

Script Task Log Settings

To enable a Script Component of a Data Flow task to be logged, select the data flow task and enable the appropriate logging:

Script Component Log Image

Script Component Log

Once the logging is enabled then you just need to add a few lines of code to the script component and/or the script task.

To test and demonstrate I created a new, simple package on a test system.  My simple package looks like:

Control Flow

Control Flow for simple package

In my single data flow I have an XML source that takes its XML from a string variable with a manually typed two lines and then a script component:

Script Component in Data Flow Task

Script Component in Data Flow Task

In my Script Task I can use code from the article linked at the beginning:

//script task
 public void Main()
 {
   byte[] emptyBytes = new byte[0];
   Dts.Log("Log from a Script Task", 0, emptyBytes);
   Dts.TaskResult = (int)ScriptResults.Success;
 }

There is an example of the code to use in a script component on a data flow task. Logging in the Script Component. The example uses VB.Net. I had a go at converting that to C# and came up with

//script component
public override void PreExecute()
{
  base.PreExecute();
  byte[] emptyBytes = new byte[0];
  this.Log("Script Task in Dataflow Component startup", 0, emptyBytes);
}

What are the results of this then, my SYSSSISLog table shows the following

SYSSSISLog

SYSSSISLog

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to Logging in the SSIS Script Task and Script Component

  1. Tejas says:

    I am tasked to create SSIS framework. Part of this framework is logging component. I am aware of basic logging feature of SSIS. However, my task is to come up with a ‘logging component’ that can be used across company. In framework, other teams will add more projects.
    1. Project A (which is part of framework) can use this logging component and it will be stored projectA.log
    2. Project B (which is part of framework) can use this logging component and it will be stored projectB.log and so forth.
    3. This logging component should rotate weekly. That means projectA_monday.log, projectA_tuesday.log. Next Monday, log will be overwritten. However, on same day, you should be able to see all entries for projectA

    I would love to hear your advice for this. I have already log file which does step 3, but I am not sure how to start framework and ‘logging component for other projects. ’
    Will appreciate your advice and suggestion/solution

  2. Scott C says:

    Here’s one way to do it, if you’re still looking for an answer.

    You can’t dynamically set the logging connection properties with package variables or expressions, because the logging connection is opened before the package starts executing. But you can set it dynamically through package configuration. (Note: this is what I learned for SSIS 2005 and 2008. Things may be different in newer versions, but this approach will still work.)

    Pick a SQL server database somewhere that should be readable by all servers running your framework packages, and create a package configuration view.
    CREATE VIEW dbo.SSIS_Logging_Config AS
    SELECT
    ConfigurationFilter = CAST(‘SSISFrameworkLogging’ AS VARCHAR(100)),
    PackagePath = CAST(‘\Package.Connections[Logging].Properties[ConnectionString]’ AS NVARCHAR(255)),
    ConfiguredValueType = CAST(‘String’ AS NVARCHAR(20)),
    ConfiguredValue = CAST(‘\\server\share\path\’ + PROGRAM_NAME() + ‘_’ + DATENAME(WEEKDAY, GETDATE()) + ‘.log’ AS NVARCHAR(1000)
    );

    Grant SELECT permissions on this view to all accounts likely to be running your SSIS packages.

    Create a SQL connection manager for configuration. Set the server name and initial catalog properties to the location of the view created above. Set the Application Name property to the project name part of the log file (projectA, projectB, etc).

    Create a flat file connection manager named “Logging”. (If you want to use a different name, replace the word “Logging” in the PackagePath column of the view.)

    Use the Package Configuration wizard to configure the Logging connection from the SQL configuration connection manager, using the configuration filter “SSISFrameworkLogging”.

    Set up whatever logging events you like, and they should appear in the appropriate files.

    This doesn’t cover how you will delete last weeks file before writing to it. There are various difficulties doing it within the package, not least because the log file is already open when the first task executes. I would suggest having a separate scheduled task that runs somewhere around 11:45 PM and deletes any log files older than six days ago at 12:00 AM.

    Another suggestion would be to put a date stamp in the filename instead of the day name. You still want a cleanup job to delete old log files, but you have the option of keeping them for whatever duration you like.

    ConfiguredValue = CAST(‘\\server\share\path\’ + PROGRAM_NAME() + ‘_’ + CONVERT(CHAR(8), GETDATE(), 120) + ‘.log’ AS NVARCHAR(1000)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s