Enable SSIS package logging with dated output filename

Had to outline this for an associate today, so I thought I'd share it here as well.  Just a quick method for adding logging to your SSIS packages, and including the date in the output filename.

Within VS, open your package and right click the Control Flow back panel, choose Logging:

This brings up 'Configure SSIS Logs: PackageName' dialog box, where ‘PackageName’ is the name of the package you are managing.  Check the box next to your package name in Containers.  Note at the bottom of the dialog it tells us we need to enable the logging in the tree view in order to configure logging. 

Now hit the ‘Details’ tab to choose your log details.  

My suggestions for detail selection:

     ALWAYS:                                OFTEN HELPFUL:

OnError                                 OnInformation

OnTaskFailed                         Diagnostic

OnWarning

After choosing the log details, go back to Providers and Logs tab and choose ‘SSIS log provider for Text files’ provider type.  Check the Name box, choose ‘Configuration’ and then <New connection…>.  

This opens a File Connection Manager Editor, where you choose 'Create file' and browse to your output folder directory, using a descriptive name for your package.  I always use packagename.txt.

Hit ‘Ok’ here, and again at the bottom of the 'Configure SSIS Logs: packagename' dialog box. Now you will see a packagename.txt entry down below in your Connection Managers.  That is your new log file connection manager.

Now, a ‘packagename.txt’ log file is created with every execution of your SSIS package ...which will be overwritten every time. To avoid that, we need to use a variable and an expression to dynamically include the date in the logfile name.  

For the variable, right click the Control Flow back panel and choose 'Variables'.  Scope will be filled in with your package name, but you must enter Name, Type and value, which should correspond to the path you want the log file written to, like this:

For the expression, right click your new log file connection manager and choose properties.  

Choose 'Expressions' in the returned properties and hit the box to open the 'Property Expressions Editor' dialog box.  Under Property, choose 'Connection String' and hit the box to open the 'Expression Builder' dialog box. Here you will paste either statement below into the Expression box and choose 'Evaluate Expression' to see the final format on your log filename.  If it's good, choose Ok and then you must Save your package.

packagename_log_YYYYMMDD.txt -

@[User::VarLogFolderPath]+ @[System::PackageName]+"_log_"+ (DT_WSTR,4)DATEPART("yyyy",GetDate()) +

RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) +

RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2)+ ".txt"

packagename_log_YYYYMMDD_hhmm.txt -

@[User::VarLogFolderPath]+ @[System::PackageName]+"_log_"+ (DT_WSTR,4)DATEPART("yyyy",GetDate()) +

RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) +

RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + "_" +

RIGHT("0" + (DT_WSTR,2)DATEPART("hh",GetDate()),2)+

RIGHT("0" + (DT_WSTR,2)DATEPART("mi",GetDate()),2) + ".txt"

Of course, there are many other formats for outputting the datestamp into the log filenames, but those are the two I use most often.  

Hope to have helped!