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.

Adding 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.

OLE DB Connection configuration ..
Then add an OLE DB Connection for the logging database.

OLE 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.

OLE DB Connection – Expression configuration [1]

OLE DB Connection – Expression configuration [2]

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

Enable logging
Now enable logging for the package.

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

Enable 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).

Select the Script Task
Select the Script Task and add the following Variables to ReadOnlyVariables:

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



The 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.

Overview
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.

Using 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.

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

Method GetSsisLogWriter to instantiate a SsisLogWriter object

Method Main is extended with logging.

Pass the logWriter as parameter to other methods ..

IMPORTANT: Bugfix in CheckAndReturnHeader!

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

Testing, testing, one, two ..



Test preparations [1]

Test preparations [2]

Test execution

Test 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.