SSIS Logging Package Execution Results

I have seen some other posts like this one out there, but I am biased and think my posts are better because I have “prettier” pictures and generally go into more depth on each specific subject. 

In some ETL scenarios, it is necessary to log some or all events at a number of levels. In SSIS, there are a number of ways to do that.  This post will describe how to create a new uniquely named log file for each package execution.

Once you have created your dataflow task, click on SSIS in the Visual Studio menu and choose “Logging…”

01

Here is where you can set up the log.  You can log events at the package level or at lower task levels.  Click Add to add a SSIS log provider for text files.  Click the package textbox on the left side to capture all events inside of the package.  Click the textbox SSIS log provider for text files to configure your log file.

02

If you click on the details page, you can select what type of events you would like to capture in your log file. 

NOTE: Choosing  the “OnError” event will capture all events at the Computer, Operator, SourceName, SourceID, ExecutionID, MessageText & DataBytes level.  You will probably not need that much detail.  To trim down that detail, choose the “Advanced>>” button to choose what information you will need.

03

Configuring Dynamic Log File

Once you have specified the details you would like to capture, you can set up a connection to which the log provider can write events.  To do so, click on Configuration and new connection.

04

Set up a connection manager to “Create file”

Create blank log.txt file where you would like your dynamic log file to be created on each package execution.

05

 Click OK twice.

 Click the newly created log.txt connection manager and navigate to the properties pane

06

Click on the expression ellipse (…)

Choose ConnectionString as the property and then click on the Expression ellipse (…)

 07

 

This will open up the expression editor.  The below example shows a log file that will get created on each package execution at \SERVER\Share\Directory\Subdirectory\.  The log file will take the package name, the date and time stamp of the execution.

08

Here is a copy/paste version of the code used:

“\\\\SERVER\\Share\\Directory\\Subdirectory\\” +  @[System::PackageName] + (DT_STR,4,1252)DATEPART( “yyyy” , @[System::StartTime]   ) + RIGHT(“0” + (DT_STR,4,1252)DATEPART( “mm” ,  @[System::StartTime]  ), 2) + RIGHT(“0” + (DT_STR,4,1252)DATEPART( “dd” , @[System::StartTime]   ), 2) + RIGHT(“0” + (DT_STR,4,1252)DATEPART( “hh” , @[System::StartTime]   ), 2) + RIGHT(“0” + (DT_STR,4,1252)DATEPART( “mi” ,  @[System::StartTime]  ), 2) + RIGHT(“0” + (DT_STR,4,1252)DATEPART( “ss” ,  @[System::StartTime]  ), 2) + “.csv”

Advertisements

Tags: , , ,

One Response to “SSIS Logging Package Execution Results”

  1. Thoughts on Error Handling in SSIS « data integrity Says:

    […] will follow up this post with some posts on logging and error […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: