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

1. Added two variables inside package as follows

             a. FileExists - Boolean type to indicate whether file path exists or not

             b. LogFilePath - String variable to store the full file path.

2. Configured Logging in the package using SSIS log provider for text files.

3. Made the connection string for the logfile dynamic based on FilePath variable using expression builder

4. Added the variable to the configuration to set values from outside

5. Added a script task as the first task in the package with LogFilePath variable passed in read only mode and 

FileExists in read write. The code block will be simple as below

Public Sub Main()

'

' Add your code here

        '

        Dts.Variables("FileExists").Value = IIf(Len(Dir(Dts.Variables("LogFilePath").Value)) > 0, True, False)

Dts.TaskResult = ScriptResults.Success

End Sub

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

http://technet.microsoft.com/en-us/library/ms141214.aspx

Now that you've set up the expression for the LoggingMode property you can try running the package.

First set a valid file path for the variable and try running the package and you can see log file within the specified path.

Now change the value to a non-existent path and you can notice that the package will still run without any failure but with no file created for the logging data.

This provides an effective  method to implement conditional logging in SSIS packages.