Implement logging in a Script Task (SSIS Series)

Introduction

This post is a follow-up on two older posts and will be the last one before my summer break.

Today I want to enhance the SSIS package made in post Fixing corrupt csv files in a SSIS Package (SSIS Series) by using the Plug and Play Logging Solution.

Problem

As long as a script runs well, you might not need logging inside. BUT .. when something GOES wrong, having logging in the script can reduce the time needed to troubleshoot the issue.
In the example for fixing csv files, anything can be wrong with a file, causing an error in the script. In that case it is for instance useful to know which file was being processed when the error occurred.

Prerequisite

For this to work, you have to install my Plug and Play Logging Solution, which can be downloaded from this post. It is just one SQL Script you have to run, that creates a [logdb] database with objects inside. You can also add those objects to a different database that already exists.
It is however not mandatory to rebuild the Fixing corrupt csv files in a SSIS Package example, as you can also add logging to your own Script Task.

Solution

This post is a enhancement on the Fixing corrupt csv files in a SSIS Package post, so you might need to read that post first, if you did not do that already.

Therefore I will dive directly into the changes that are needed to add logging in the Script Task.
This will be done with lots of screenprints with some comment in between.

SSIS-S01E07-169Adding a package parameter “LoggingConnectionString”
First you need to add a package parameter LoggingConnectionString that can be used in an expression of the OLE DB Connection and as input variable for the Script Task.

SSIS-S01E07-170OLE DB Connection configuration ..
Then add an OLE DB Connection for the logging database.

SSIS-S01E07-171OLE DB Connection properties ..
Use an expression to let the ConnectionString of the OLE DB Connection be equal to the value of the package parameter LoggingConnectionString.

SSIS-S01E07-173OLE DB Connection – Expression configuration [1]

SSIS-S01E07-172OLE DB Connection – Expression configuration [2]

SSIS-S01E07-174Final result
By the fx icon you can see that the connection manager uses an expression.

SSIS-S01E07-176Enable logging
Now enable logging for the package.

SSIS-S01E07-177Enable logging [2]
Add a log provider for SQL Server and let it use the logdb.oledbConnection by selecting it under the Configuration column header.

SSIS-S01E07-178Enable logging [3]
Then select all events. Filtering on what is actually logged is done by the logging solution (by the value of @MaxMessageClass, see this blog post for more details).

SSIS-S01E07-179Select the Script Task
Select the Script Task and add the following Variables to ReadOnlyVariables:

  • System::ExecutionInstanceGUID
  • System::PackageID
  • $Package::LoggingConnectionString


  • SSIS-S01E07-180The added ReadOnlyVariables in the red rectangles

    Below you will find a number of screenprints of the script task to talk you through the changes.
    You can download the C# script here.

    SSIS-S01E07-181Overview
    First make sure the Namespaces region is as shown.
    Then fold the namespace with the guid in the name, and paste the entire namespace HansMichielsCom.PlugAndPlaySSISLoggingSolution underneath it.
    This code is put in a separate namespace, so that it could also be placed in a .NET assembly that is added to the GAC (Global Assembly Cache). When you would do this, you do not have to add the code to every Script Task.
    For the example of today, we just put this namespace inside the Script Task to make things not too complicated for now.

    SSIS-S01E07-182Using the HansMichielsCom.PlugAndPlaySSISLoggingSolution namespace
    As a result, you have to tell the first guid-like namespace, that you want to call code inside the second namespace. Therefore add the using statement as shown above.

    SSIS-S01E07-183Constant used for logging
    Below you will see some printscreens with changed parts in the script.

    SSIS-S01E07-184Method GetSsisLogWriter to instantiate a SsisLogWriter object

    SSIS-S01E07-187Method Main is extended with logging.

    SSIS-S01E07-188Pass the logWriter as parameter to other methods ..

    SSIS-S01E07-189IMPORTANT: Bugfix in CheckAndReturnHeader!

    SSIS-S01E07-190IMPORTANT: Bugfix in CheckAndReturnHeader!
    (header == null) is added to cope with empty files.

    Testing, testing, one, two ..



    SSIS-S01E07-191Test preparations [1]

    SSIS-S01E07-193Test preparations [2]

    SSIS-S01E07-194Test execution

    SSIS-S01E07-195Test result: logging rows done inside the script are in the log table.

    Conclusion / Wrap up

    In this post I have demonstrated how to implement logging in SSIS Script Tasks using my Plug and Play Logging Solution.
    This type of logging gives more control on what to log and how to log it than when you implement logging using SSIS events.
    The examples given are very basic. You can use your imagination to implement logging of errors using a try .. catch block, or use all available parameters of logWriter.AddLogEntry to change the Retention Class, Message Class, and so on.

    In the summer I will take some time for study, reflection, holiday, and still .. work.
    My next post will be early September at the latest, maybe earlier.

    Download the C# script here.

    (c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

    Hans Michiels

    Hans Michiels

    Hans is an Independent Business Intelligence and Datawarehouse Consultant & Microsoft SQL Server Consultant, working in the Netherlands. He has been working in the software industry since 1996, with SQL Server since the year 2001, and since 2008 he has a primary focus on datawarehouse- and business intelligence projects using Microsoft technology, using a Datavault and Kimball architecture. He has a special interest in Datawarehouse Automation and Metadata driven solutions. * Certified in Data Vault Modeling: Certified Data Vault 2.0 Practitioner, CDVDM (aka Data Vault 1.0) * Certified in MS SQL Server: * MCSA (Microsoft Certified Solutions Associate) SQL Server 2012 - MCITP Business Intelligence Developer 2005/2008 - MCITP Database Developer 2005/2008 - MCITP Database Administrator 2005/2008

    More Posts

Leave a Reply

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