如何查询SQL dts包

Question

我有dts包在SQL和文件系统(.dts文件)。我想查询这两个包，以检索包名称的数量、任务信息(任务名称等)等信息。

这样做的正确方法是什么？

我尝试执行给出dts包列表的命令。然而，我不知道如何检索其余的信息。exec msdb..sp_enum_dtspackages

Atul

Answer 1

不久前我也遇到了同样的问题，并找到了答案。请看一下我的脚本，看看它是否有用。

dim strInputFile, strOutputFile

for each arg in WScript.Arguments
    select case mid(arg, 1, 3)
        case "/i:"
            strInputFile = mid(arg, 4)
        case "/o:" 
            strOutputFile = mid(arg, 4)
    end select
next

dim oPackage
set oPackage = CreateObject("DTS.Package2")
oPackage.LoadFromStorageFile strInputFile, "", "", "", ""

dim FileSys, OutputFile
set FileSys = CreateObject("Scripting.FileSystemObject")
set OutputFile = FileSys.OpenTextFile (strOutputFile, 2, 1)

dim oTasks, oProperties
set oTasks = oPackage.Tasks

for each oTask in oTasks
    OutputFile.write (vbCrLf)
    OutputFile.write (string(12 + len(oTask.Description), "-"))
    OutputFile.write (vbCrLf)
    OutputFile.write ("-- Task: "  & oTask.Description & " --") 
    OutputFile.write (vbCrLf)
    OutputFile.write (string(12 + len(oTask.Description), "-"))
    OutputFile.write (vbCrLf)

    set oProperties = oTask.Properties

    for each oProperty in oProperties
        OutputFile.write (oProperty.Name & " = [" & trim(oProperty.Value) & "]")
        OutputFile.write (vbCrLf)
    next 
next 

OutputFile.close

set OutputFile = Nothing
set oTasks = Nothing
set oProperties = Nothing
set oPackage = Nothing
set FileSys = Nothing

关键是要知道从文件系统或SQL Server加载DTS包的方法。

例如

...
dim oPackage
set oPackage = CreateObject("DTS.Package2")
oPackage.LoadFromStorageFile strInputFile, "", "", "", ""
...

有关更多详细信息，请访问MSDN：

http://msdn.microsoft.com/en-us/library/aa197290%28v=sql.80%29.aspx (LoadFromSQLServer方法)

http://msdn.microsoft.com/en-us/library/aa197287%28v=sql.80%29.aspx (LoadFromStorageFile方法)