SSIS 2008 Logging – Missing In Action??

It’s been a while since I’ve created production-level SSIS packages. I’ve been fortunate enough to hand that responsibility over to our development team…

So, when someone mentioned today that SQL 2008 “broke” the SSIS logging feature, I got typically defensive and immediately set out to prove that the SQL team would never be that short-sighted.

So I created a simple “Move Data From A to B” package and enabled the trusty SSIS SQL logging; logging the OnError, OnPreExecute and OnPostExecute events as I always do. I ran the package, and promptly moved across to SQL Management Studio to open my trusty sysdtslog90 table, and prove my friend wrong.

I did a quick refresh on the table list, and lo-and-behold, the trusty sysdtslog90 table was nowhere to be found.

(So much for my faith in the basic goodness of developers.)

After a few minutes of checking package properties, the penny dropped; the trusty logging table hasn’t been ignored, it’s simply been moved and renamed.

It can now be found in your target database under System Tables, and it’s called sysssislog.

Two main benefits to this:

1. Name is no longer version specific, so hopefully it’ll keep the same name going forward.

2. System tables can’t be renamed, so it should be more robust.

(Faith in humanity restored, if only it had been on the new feature list somewhere…)

  1. #1 by Vidas on October 27, 2009 - 7:57 pm

    It is not missing, but it broken :-(. There is known issue where many of the log records are not created in SQL 2008. And it is not fixed in SP1. Check connect open ticket: SSIS 2008 has logging, but you cannot use it when your job is executed from SQL Server Agent.

  2. #2 by Gavin on November 5, 2009 - 9:39 pm

    thanks for that Vidas. I noticed similar behaviour in SQL 2005, specifically the OnPreExecute wasn’t launched when executing jobs from the SQL Agent (although it worked fine from Visual Studio)I was able to force the event to fire, even through the SQL Agent, by simply enabling the Event Handler for the events that aren’t firing. so, in my OnPreExecute example, I just went to the Event Handler Tab, selected OnPreExecute, and clicked the following link "Click here to enable the ‘OnPreExecute’ event handler for executable ‘Package’"I found that doing this, even when leaving the event handler itself empty, caused the events to fire.

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: