THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences the Microsoft Data Platform

Using NLog With BIDS Helper to add logging to BIML Script executions

When using BIML within BIDS Helper, if your BIML Script files get complex it may be quite hard to debug BIML Script Execution in order to understand what’s going on behind the scenes.

On Windows Server 2008 it was possible to use the System.Diagnostic.Debug.WriteLine and DebugViewer from SysInternal to do the trick but it seems that this approach doesn’t work anymore on Windows Server 2012. Or, at least, I wasn’t able to make it work. Anyway, in addition to that, I was also trying to have everything logged to a file so one doesn’t have to use and configure DebugViewer to do its job. DebugViewer is great tool, but it’s not really suitable for junior developers.

So I tried to use the fantastic NLog framework in order to create a “standard” way of logging BIML. First of all download it (just get the standard version, no need to grab the “Extended” one) and unpack into a folder named “NLog-3.0” where you prefer.

Now in your SSIS Solution create a BIML file named “BimlLogger.biml” and copy and paste the following code:

<#@ template language="C#" #>
<#@ assembly name="C:\Work\Lab\BIML-Debug\NLog\NLog-3.0\net40\NLog.dll" #>
<#@ import namespace="System.Diagnostics" #>
<#@ import namespace="NLog" #>
<#@ import namespace="NLog.Config" #>
<#@ import namespace="NLog.Targets" #>

<#
    Logger logger = LogManager.GetLogger("BIML");

    LoggingConfiguration config = new LoggingConfiguration();

    FileTarget fileTarget = new FileTarget();
    config.AddTarget("file", fileTarget);

    fileTarget.FileName = @"${nlogdir}\..\..\biml.log";
    fileTarget.Layout = "${longdate}|${level:uppercase=true}|${message}";
    fileTarget.DeleteOldFileOnStartup = true;

    LoggingRule loggingRule = new LoggingRule("*", LogLevel.Trace, fileTarget);
    config.LoggingRules.Add(loggingRule);

    LogManager.Configuration = config;   
#>

Change the second line (the “assembly name” line) in order to reflect the path where you unpacked NLog and then you can start to log anything you think it can help you just referencing this file from any other BIML files and then using the “logger” object to write to the log file. Here’s an example of a BIML script file that creates a test package.

<#@ template language="C#" #>
<#@ include file="BimlLogger.biml" #>

<#
    logger.Info("Generating Package...");
#>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="Test" ConstraintMode="Linear" ProtectionLevel="EncryptSensitiveWithUserKey" />
  </Packages>
</Biml>

<#
    logger.Info("Done.");
#>

The log file will be created in the “NLog-3.0” folder you created before. Of course you can change this and many other option, since NLog is really flexible and powerful. Documentation and tutorial are here: https://github.com/nlog/nlog/wiki

2014-12-13 Update

In order to have the trick working, you have to be sure that the NLog assembly is *not* blocked…which is something will happen automatically if you download the zip from internet. In order to unblock the assembly you have to right-click on it and then select “Unblock”:

image

Published Wednesday, December 3, 2014 6:28 PM by Davide Mauri

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Judith Mcentee said:

Tried to add this and received an error Could not resolve assembly reference 'C:\Program Files (x86)\NLog-3.0\net35\NLog.dll'.  This is where my Nlog was extracted to and it does exist.  Is there something I'm missing?

December 11, 2014 12:35 PM
 

Davide Mauri said:

Hi Judith

Are you using Visual Studio 2010? It seems it has some problems when T4 templates reference specific assemblies....I'm looking at it.

In the meantime, if you can use Visual studio 2012 (which is 100% compatible with Visual Studio 2010 projects), this will solve the problem.

December 13, 2014 4:09 AM
 

Davide Mauri said:

Hi again Judith

I've figure out what's the problem that prevent the trick from working, and it's not a Visual Studio fault at the end. I've updated the post to show how to handle the situation you faced.

Let me know if it works :)

December 13, 2014 8:41 AM
 

Judith Mcentee said:

It did.  That's wonderful that you were able to figure out what was causing the issue.  I'm a biml newbie and this will help so much.  

Thanks much!

December 15, 2014 11:55 AM
 

Jozef Riskalcik said:

Hi, is it possible to set relative path for assembly and log file? I need to have nlog encapsulated with all Visual Studio project files.

Thanks a lot

March 27, 2015 12:12 PM
 

Johan van den Brink said:

If you are working with SQL Server 2008(R2) then you will need to reference the net35 folder for the assembly.

November 10, 2015 3:37 AM

Leave a Comment

(required) 
(required) 
Submit

About Davide Mauri

Director of Software Development & Cloud Infrastructure @ Sensoria, an innovative smart garments and wearable company. After more than 15 year playing with the Microsoft Data Platform, with a specific focus on High Performance databases, Business Intelligence, Data Science and Data Architectures, he's now applying all his skills to IoT, defining architectures to crunch numbers, create nice user experiences and provide meaningful insights, all leveraging Microsoft Azure cloud. MVP on Data Platform since 2006 he has a very strong background development and love both the ER model and OO principles. He is also a fan of Agile Methodology and Automation, which he tries to apply everywhere he can, to make sure that "people think, machines do".

This Blog

Syndication

Privacy Statement