Shawn Weisfeld

I find when I talk to myself nobody listens. - Shawn Weisfeld
posts - 365, comments - 180, trackbacks - 34

My Links

News


Shawn Weisfeld's Facebook profile

The views expressed in this blog are mine and mine alone, not that of my employer, Microsoft, or anyone else’s. No warrantee is given for the quality of any material on this site.

Archives

Post Categories

Upgrade from SSIS 2005 to 2008 broke SSIS Logging

My DBA upgraded our server from 2005 to 2008 and when it did my SSIS packages stopped logging messages.

Problem 1:

Well the default logging mechanism in 2005 logs to the dbo.sysdtslog90 table. Well in 2008 they changed the table name to dbo.sysssislog. Not a big deal, but you will never find something if you look in the wrong place.

Problem 2:

Well now that I am looking at the right table, I noticed that my events were NOT showing up. In particular I was looking for the OnPreExecute and OnPostExecute methods. Poof gone, after many searches I stumbled across the following posts:

https://connect.microsoft.com/SQLServer/feedback/details/491508/logging-of-ssis-tasks-are-incomplete-when-run-by-sql-agent-job?wa=wsignin1.0

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/511502a6-99d6-4693-94da-2c317c3f59c6

Ahh, bug in SSIS, I am not nuts, whew, now what. Well on the forum John Welch suggests adding event handlers to the package and using an Execute SQL task to log the event. I only have two packages, it is late, while painful, I will do that. However, by dumb luck I forgot to add the SQL Task to one of my packages, and it still worked. Interesting.

So long story short all I needed to do was add the event handlers, nothing else.

So my package when from this:

image

To This:

image

and Poof Success!

Print | posted on Wednesday, February 3, 2010 8:02 AM | Filed Under [ SSIS ]

Feedback

Gravatar

# re: Upgrade from SSIS 2005 to 2008 broke SSIS Logging

did you add an event handler for every single task you wanted to log?

or was it enough to add the handler for one task and the bug was 'fixed' ( or workarounded ;) ) for the whole package and all its tasks
2/10/2010 1:51 PM | Jay
Gravatar

# re: Upgrade from SSIS 2005 to 2008 broke SSIS Logging

I just added the event handler at the "package level". so it was one per package. . . (one per step would have been a lot of work, I don't like work)
2/10/2010 1:55 PM | shawnweisfeld
Gravatar

# re: Upgrade from SSIS 2005 to 2008 broke SSIS Logging

today I tried that workaround ... it seems to work! thank you!
2/11/2010 10:27 AM | Jay
Gravatar

# re: Upgrade from SSIS 2005 to 2008 broke SSIS Logging

Glad it worked for you! Happy to help!
2/11/2010 11:26 AM | shawnweisfeld
Gravatar

# re: Upgrade from SSIS 2005 to 2008 broke SSIS Logging

Kudos ! Kudos ! Kudos !
I am so glad I found your workaround .... you just saved me alot of work writing custom code as I could not figure out why the logging wasn't working via SQL JOB. I was resigned to the fact I would have to write custom code :(
thanks mate
4/2/2010 9:55 AM | Craig
Gravatar

# re: Upgrade from SSIS 2005 to 2008 broke SSIS Logging

I have created a package and enabled logging on sql2008. when run in BIDS, it creates the sysssislog table in the specified database and logs all events I have checked in the logging options.
after this I uploaded it into the ssis repository and created a job in the sql server agent. when i run the same package through this job, the log lines are incomplete. the are missing lines for some tasks.
I enabled logging by checking just the first checkbox, so that all child elements in the package should inherit the logging settings. I think the sql server agent does not work correctly with the inherited log settings.
8/25/2010 6:32 AM | all the best sms
Gravatar

# re: Upgrade from SSIS 2005 to 2008 broke SSIS Logging

I am trying to upgrade a server from 2005 to 2008 and am having this same problem. This solution is not working for me.
I am at the latest lever of SQL 2008 standard edition on a 2008 Windows Server.
I had to use a proxy account to even get the scheduler ro execute the task but the OnInformation entries will not show up in the log
if run from the scheduler.
Does anyone have any updated information on this problem?
9/7/2012 2:30 PM | Tim Bishop

Post Comment

Title  
Name  
Email
Url
Comment   
Please add 1 and 3 and type the answer here:

Powered by: