December 9, 2004 at 4:14 pm
Hi,
I need to find out all connections being used by all DTS packages saved as local packages in SQL Server 2K. Is any way to get this info without opening each package and looking for the connections?
Thank you,
JP
December 13, 2004 at 5:06 am
I came up with this active-x script that prompts for a package and then lists all the connection details. You should be able to get a list of packages from the msdb system files and perhaps use this to put an outer loop. I only dabble in this unfortunately so perhaps someone else could help you fill in the gaps.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
dim opackage
dim oconnection
dim tPackageName
dim tServer
tServer = InputBox("Server name")
tPackageName = InputBox("First package" )
Do
LoadPackage tPackageName, tServer
tPackageName = InputBox("Next package" )
Loop while not IsEmpty( tPackageName )
'LoadPackage tPackageName tServer
Main = DTSTaskExecResult_Success
End Function
sub LoadPackage (tPackageName, tServer)
dim tSQLName
dim tConnectionType
dim tDiskFile
dim tMsgString
tDiskFile = DTSglobalVariables("gvFileLocation").value + tPackageName
tSQLName = tPackageName + "_In"
msgbox "Reading " + tPackageName
set opackage = CreateObject("DTS.Package")
opackage.LoadFromSQLServer tServer,"sa","<password>",,,,,tPackageName
for each oconnection in oPackage.Connections
tMsgString = oConnection.name + ", " _
+ oConnection.DataSource + ", " _
+ oConnection.UserId + ", "_
+ oConnection.udlpath + ", " _
+ oConnection.Catalog + ", " _
+ oConnection.ProviderId
msgbox tMsgString
next
end sub
Hope this helps a little
J
December 13, 2004 at 12:59 pm
What value do you have for: DTSglobalVariables("gvFileLocation")?
Thank you,
JP
December 14, 2004 at 6:48 am
Sorry, gvFileLocation is used as a global variable set to a favourite local directory (e.g. c:\SQL Packages\Connections
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply