VB script to document all the packages and the connections used by the ExecuteSQLTasks on a SQL 2000 server. The results are saved to c:\dtslog.txt.
Replace ..server.., ..login.., ..password.. with the appropriate values
Razvan Petrescu http://razvan.petrescu.googlepages.com
v1 July 2006
Dim package, o, task, custask, connection
Dim output, i, iTotal
Dim DTSApp, PkgSQL, PkgInfos, info, prp, msg
Dim fs, f, ts, s
output = ""
Set DTSApp = CreateObject("DTS.Application")
Set package = CreateObject("DTS.Package")
Set PkgSQL = DTSApp.GetPackageSQLServer("..servername..", "..login..", _
"..password..", DTSSQLStgFlag_Default)
Set PkgInfos = PkgSQL.EnumPackageInfos("", True, "")
Set info = PkgInfos.Next
Do Until PkgInfos.EOF
package.LoadFromSQLServer "..servername..", "..login..", "..password..", _
DTSSQLStgFlag_Default, "", "", "", info.Name, o
MsgBox info.Name
output = output & vbCrLf & "********************************************" & _
vbCrLf & "Package: " & info.Name & vbCrLf & _
"********************************************" & vbCrLf
i = 0
iTotal = package.Tasks.count
For Each task in package.Tasks
i = i+1
output = output & vbCrLf & "Task: " & CStr(i) & ". " & _
task.Name & vbCrLf & "Description: " & task.description & _
" CustomId: " & task.CustomTaskId
If task.CustomTaskId = "DTSExecuteSQLTask" then
Set custask = task.CustomTask
output = output & vbCrLf & "SQL:" & vbCrLf & _
"-------------------------------------------------"
output = output & vbCrLf & custask.SQLStatement
output = output & vbCrLf & _
"-------------------------------------------------"
For Each connection in package.Connections
If connection.ID = custask.ConnectionId then
output = output & vbCrLf & vbCrLf & _
"Connection: " & connection.Name & " DS: " _
& connection.DataSource & vbCrLf
End If
Next
End If
Next
Set info = PkgInfos.Next
Set package = Nothing
Set package = CreateObject("DTS.Package")
Loop
Set fs = CreateObject("Scripting.FileSystemObject")
Set ts = fs.OpenTextFile("c:\dtslog.txt", 2, 1)
ts.Write output
ts.Close
WScript.Echo "Finished"
Have your categories and subcategories all in one table, and retrieve a list of all categories with the subcategories that belong to it in one statement. No more nested loops!
Table schema:
id - auto-increment ID for this row
parentid - the ID of the row this row is a child of
title - the title of this row
select *
from cats c1
Left outer Join cats c2 On c1.parentid = c2.id
order by IsNull(c2.id, c1.id), c1.id
Bring back a list of categories, and a limited number of items in each category, as well as a count of the total number of items in that category.
Select c.title, i.itemname,
(
Select count(id)
from items where category_id =
i.category_id
) As countitems
from items i
inner Join categories c On c.id = i.category_id
where (
Select count(*) from items
where itemname >= i.itemname And category_id = i.categoryid
) <= 2
order by c.category, i.itemname
Code will show you a list of rows that are duplicated in a table - very useful on old databases/tables
Replace the column names with yours, and the table name.
select id_column, other_columns_to_check
from your_table
group by id_column, other_columns_to_check
having count(*) > 1