Tuesday, April 1, 2014

SSIS Tips: Implementing Conditional Logging

The impetus for this post comes from a recent conversation I had with one of my colleagues. He asked me whether we could implement logging conditionally for a SSIS package. I told it should be possible keeping in mind that we will be able to use expression builder for the same. Anyways I wanted to see for myself how this can be implemented so thought of doing a sample to illustrate it which I'm sharing through this blog.

The package for the illustration looks like below

I wont be going into details of main package here as thats not the purpose of this article. From the logging perspective this is what I've done

What this does is to check the existence of the passed file path and sets boolean variable value accordingly ie True if file path exists else false. This variable would be used for setting the conditional value for logging in the package

6. Now we need to set conditional expression for logging property within package. For this right click anywhere on package body and select properties to launch property window on right side. Inside that set an expression for Logging property as shown

One thing to note here is that though the values you see for LoggingMode property are Enabled,Disabled etc they're just friendly names. While setting property through expressions you need to set the values as 0,1,2 etc as these are the actual values within the enumeration DTSLoggingMode used for setting the Logged property value. This is specified in the below link