Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
LisaS
Starting Member
12 Posts |
 Posted - 2007-08-15 : 18:32:41
|
| [mods - if you think this would be better in the TransactSQL forum, please move]I'm trying to develop some basic documentation of DTS packages and the Jobs (SQL Agent) that execute the DTS packages. Rather than do this manually, I thought I'd use the tables in msdb.I can find the DTS in the 'command' column of sysjobsteps if I just dump that table but I'm having trouble with the SQL stmts to link from the DTS package (sysdtspackages). Ideally, I'd like to run a query driving from DTS package and tell me any/all jobsteps that use each package AND handle the null case (or a 2nd query) that tells me that no jobsteps use it. as an example, the command column in sysjobsteps might have content like this: DTSRun /S "(local)" /N "DW_AC_MYFILE1" /W "0" /Eand the sysdtpackages column name might have: DW_AC_MYFILE1I'd like to find those two as a match. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-15 : 19:21:57
|
select * from sysdtpackages as pinner join sysjobsteps as js on js.<command column> like '%"' + p.<name column> + '"%'
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
LisaS
Starting Member
12 Posts |
Posted - 2007-08-15 : 19:45:19
|
| I guess I couldn't wrap my head around how to split up that "like" clause to include the column. I had not seen that before. I did try this solution - and it did do the join properly. I found that my sysdtspackages table seems to have a history of packages/versioning and so I'll have to do something to deal with the "distinct" requirement - or pull only the max version or something - but other than that looks excellent. thanks. |
|
|
|
|
|
|
DTS & jobsteps - generating xref