How do I determine whether or not a specific DTS Package (I know the name of
the package) exists on the Server?

Thanks for your help,

- Joe Geretz -

Select * from msdb.dbo.sysdtspackages where name='packageName'

Francesco Anti


"Joseph Geretz" <jgeretz (AT) nospam (DOT) com> wrote

Open the Data Transformation Services folder in EM, and check within
"Local Packages" and "Meta Data Services" packages.
A DTS Package can also be saved as a structured storage file on the hard
disk.

You can also query msdb
USE msdb
GO

SELECT DISTINCT
Name
FROM
sysdtspackages
ORDER BY
Name

Simon Worth

Joseph Geretz wrote:

Thanks Francesco and Simon for your help.

I actually opted for a third approach, using the DTS object model. I'm
posting the code below, perhaps others will find this useful.

- Joe Geretz -

Public Function PackageExists(DBConnect As String, Package As String) As
Boolean

Dim SQLServer As String
Dim SQLUID As String
Dim SQLPWD As String
Dim DTSPkg As DTS.Package2

On Error GoTo ErrHandle

Set DTSPkg = New DTS.Package2

zGetDBCredentials DBConnect, SQLServer, SQLUID, SQLPWD

DTSPkg.LoadFromSQLServer SQLServer, _
SQLUID, _
SQLPWD, _
, _
, _
, _
, _
Package

PackageExists = True

Exit Function

ErrHandle:

PackageExists = False

End Function

"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote

I think loading a package just to check if it exists is not a good idea.
If you insist on using the object model then lookup the EnumPackageInfos
method in Books Online, although I would stick with the sysdtspackages
query myself. Simple, fast and does the job.


In message <O73iYo9KFHA.4052 (AT) tk2msftngp13 (DOT) phx.gbl>, Joseph Geretz
<jgeretz (AT) nospam (DOT) com> writes
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org