Finding DTS step names in DTS System tables

Question

Finding DTS step names in DTS System tables

EamonSQL

SSCrazy

Points: 2307
More actions
March 30, 2007 at 2:33 am

#70000

Hi,
I'm looking at a problem where a DTS package failed and which to get exactly which step caused the problem.
I run
select stepname,datediff(mi,starttime,endtime) as minutes_taken,*
from msdb..sysdtssteplog nolock
where starttime > '29-mar-2007 18:00:00' and starttime < '30-mar-2007 10:00:00'
and stepname in ('DTSStep_DTSExecuteSQLTask_11','DTSStep_DTSActiveScriptTask_5')
order by starttime asc
and get step names like DTSStep_DTSExecuteSQLTask_11 and DTSStep_DTSActiveScriptTask_5 but would like to know how I can return the names given in the DTS package itself.
Any ideas?
Any help welcome.
Thanks in advance,
Eamon

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Hi Eamon,

I'll bet the names you'd like to see are the descriptions you entered when you created the tasks. Unfortunately, those aren't stored in sysdtssteplog. Are you set on finding this information with T-SQL? I've always depended on package logging to tell me which step failed.

Greg

EamonSQL SSCrazy Points: 2307 More actions · Answer 2

Cheers Greg,

thanks for that, luckily I was able to figure it out at with the logs and by counting through the steps.

thanks for this.

Eamon

Ghanta Hall of Fame Points: 3999 More actions · Answer 3

It is a good practice to include error message on dts packages if you are scheduling it.

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 4

Open up your DTS package and select the Properties/Disconnected Edit menu.

In the window that appears, expand the "Steps" section. Select one of the steps in your package. Double-click on the "Name" property. Enter the same value stored in the "Description" property.

Repeat for each step in the package, then save the package. Execute the package then run your query. The stepname will now be what you want it to be.