query dts packages...

Question

query dts packages...

neotokyo

SSCertifiable

Points: 7239
More actions
January 30, 2007 at 1:27 pm

#153407

i'm trying to query the connection objects
in a dts packages, but having trouble getting
to that kind of information.
is this possible?
i've already checked the following tables,
and couldn't find any thing.
sysdtscategories
sysdtspackagelog
sysdtspackages
sysdtssteplog
sysdtstasklog
just wondering if there was another way to do this.
thanks in advance.
_________________________

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

If the dts package stored in MSDB, it will be stored in sysdtspackages table but you can't query the Package configuration...because it is stored in hex format in sysdtspackages.packagedata column.

You may need to write VB code...to do this...OR save the package as VB file and search...

MohammedU
Microsoft SQL Server MVP

neotokyo SSCertifiable Points: 7239 More actions · Answer 2

well... instead of reinventing the wheel up front, i'll

think i'll browse the net for some scripts which will do this.

i'll also deconstruct one of the packages via vb, and see what

i can see.

thanks for the reply.

_________________________

Carlos Urbina SSCommitted Points: 1850 More actions · Answer 3

I received help from Nigel Rivett's Activex script which I have modified and is listed below. Remember to include the necessary global variables in your DTS package. Each package is scripted in its own file in the directory specified in one of the global variables.

'**********************************************************************

' Visual Basic ActiveX Script

' Author Nigel Rivett

' http://www.nigelrivett.net/ScriptDTSProperties.html

'************************************************************************

Function Main()

Dim sServerName

Dim sPath

Dim sUserName

Dim sPassword

Dim iTrusted

sServerName = DTSGlobalVariables("ServerName").Value

sPath = DTSGlobalVariables("Path").Value

sUserName = DTSGlobalVariables("UserName").Value

sPassword = DTSGlobalVariables("Password").Value

if len(sUserName) = 0 then

iTrusted = "Y"

else

iTrusted = "N"

end if

iTrusted = "Y"

ScriptDTSPackages sServerName, sPath, iTrusted, sUserName, sPassword

Main = DTSTaskExecResult_Success

End Function

Private Sub ScriptDTSPackages(sServerName, sPath, iTrusted, sUserName, sPassword)

Dim objCon

Dim objCmd

Dim objRsPackageNames

Set objCon = CreateObject("ADODB.Connection")

if iTrusted = "Y" then

objCon.ConnectionString = "Provider=sqloledb;" & _

"Data Source=" & sServerName & ";" & _

"Initial Catalog=" & "msdb" & ";" & _

"Integrated Security=SSPI"

else

objCon.ConnectionString = "Provider=sqloledb;" & _

"Data Source=" & sServerName & ";" & _

"User Id=" & sUserName & ";" & ";" & _

"Password=" & sPassword & ";" & _

"Initial Catalog=" & "msdb"

end if

objCon.CursorLocation = 3 'adUseClient

objCon.Open

Set objCmd = CreateObject("ADODB.Command")

objCmd.ActiveConnection = objCon

objCmd.CommandType = 1 'adCmdText

objCmd.CommandText = "select distinct name from sysdtspackages order by name"

Set objRsPackageNames = CreateObject("ADODB.Recordset")

Set objRsPackageNames = objCmd.Execute

'ScriptDTSPackage "alantest", frmInitial.txtFolder, frmInitial.txtServer, frmInitial.chkTrusted, frmInitial.txtUsername, frmInitial.txtPassword

Do While Not objRsPackageNames.EOF

ScriptDTSPackage2 objRsPackageNames("Name"), sPath, sServerName, iTrusted, sUserName, sPassword

objRsPackageNames.MoveNext

Loop

Set objRsPackageNames = Nothing

Set objCmd = Nothing

Set objCon = Nothing

End Sub

Private Sub ScriptDTSPackage2(sPackageName, sFolder, sServer, iTrusted, sUID, sPWD)

Dim objFileScript

Dim objFStream

Dim objDTSPackage

Dim objDTSTask

Dim objDTSConnection

Dim objDTSTransformation

Dim objDTSDataPumpTask

Dim objDTSGlobalVariable

Dim objDTSCustomTask

Dim objDTSProperty

Dim objDTSDynamicPropertiesTask

Dim objDTSDynamicPropertiesTaskAssignment

Dim i

Dim s1

Dim s2

Set objFileScript = CreateObject("Scripting.FileSystemObject")

Set objFStream = objFileScript.CreateTextFile(sFolder & sPackageName & "_properties.txt")

' load package

Set objDTSPackage = CreateObject("DTS.Package2")

If iTrusted = "Y" Then

objDTSPackage.LoadFromSQLServer sServer, , , 256, , , , sPackageName

Else

objDTSPackage.LoadFromSQLServer sServer, sUID, sPWD, , , , , sPackageName

End If

' Global variables