"fail package on step failure"

Question

"fail package on step failure"

mirekg

SSC Rookie

Points: 45
More actions
September 25, 2003 at 9:12 am

#106235

I'm looking way to find which objects in my DTS packages are
with properties "fail package on step failure".
(I mean some sql query to execute to find it)
I should be written in some table in msdb base. I'm I right?
Regs,
MG
Edited by - mirekg on 09/25/2003 09:12:33 AM

Viewing 6 posts - 1 through 5 (of 5 total)

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

philcart SSC-Forever Points: 47796 More actions · Answer 1

You won't be able to run an SQL query to determine the properties of a DTS package. This is because the packages are stored in msdb..sysdtspackages as BLOB's.

Best method would be to write an ActiveXScript to iterate through the pacakges and their steps , outputting the results to a table or text file.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

mirekg SSC Rookie Points: 45 More actions · Answer 2

mirekg

SSC Rookie

Points: 45

September 26, 2003 at 12:08 am

#475921

Thanks.

Any help how to write such a script?

MG

philcart SSC-Forever Points: 47796 More actions · Answer 3

Have a look at this,



Function Main()

Dim sSvr ' server name

Dim sDb ' database name

Dim sUser ' user id

Dim sPwd ' password

Dim iSec ' security to use

Dim oConn ' ADO connection object

Dim oRS ' ADO recordset

Dim sConn ' ADO connection string

Dim sSQL ' SQL string to execute

Dim bTrusted ' whether to use trusted connections

Dim sPkgName ' DTS Package name

Dim oPkg ' DTS Package object

Dim oStp ' DTS step object

Dim oTsk ' DTS task object



bTrusted = True

sSvr = "<your server>"

sDb = "msdb"

' these two are not required if using trusted connection

sUser = "<your sql username>"

sPwd = "<your sql password>"



' build ADO connection string from global variables

sConn = "Provider=SQLOLEDB.1"

' check if using Integrated security or not

If bTrusted = True Then

' use trusted connection, no username and password required

sConn = sConn & ";Integrated Security=SSPI;Persist Security Info=False"

iSec = 256 ' use Windows integrated security to load package

Else

' use SQL security, so must provide username and password

sConn = sConn & ";Persist Security Info=True"

sConn = sConn & ";User ID=" & sUser

sConn = sConn & ";Password=" & sPwd

iSec = 256 ' use SQL security to load package

End If

sConn = sConn & ";Data Source=" & sSvr

sConn = sConn & ";Initial Catalog=" & sDb

' add a where clause to this SQL statement to limit 

' the list of packages you retrieve

sSQL = "SELECT DISTINCT name FROM dbo.sysdtspackages"

' setup connection and recordset objects

Set oConn = CreateObject("ADODB.Connection")

Set oRS = CreateObject("ADODB.Recordset")

' open ADO connection 

oConn.Open sConn

' open recordset

oRS.Open sSQL, oConn

While oRS.EOF = False

' assign values in recordset to global variables for propagation to parent package

sPkgName = oRS.Fields(0).Value

' create new instance of the DTS package object

Set oPkg = CreateObject("DTS.Package2")

' load the package from teh server

oPkg.LoadFromSQLServer sSvr, sUser, sPwd, iSec, "", "", "", sPkgName

iStps = oPkg.Steps.Count

' initialise loop counter

iCntr = 1

' loop through the steps collection

While (iCntr <= iStps)

' set reference to step and task

Set oStp = oPkg.Steps(iCntr)

Set oTsk = oPkg.Tasks(oPkg.Steps(iCntr).TaskName)

' display some properties of each object

MsgBox "The Step Description is " & oStp.Description

MsgBox "The Task Description is " & oTsk.Description

iCntr = iCntr + 1

Set oTsk = Nothing

Set oStp = Nothing

Wend

Set oPkg = Nothing

oRS.MoveNext

Wend

Main = DTSTaskExecResult_Success

End Function

This script will iterate through all the packages on the server and display the description for each Step and Task in message boxes.

Have a look through Disconnected Edit to determine the properties that you want. At the location where the two MsgBox statements are, you'll want to put some code to save the values of those properties. Either to a file, search for FileSystemObject in Books Online, or to a table, check the section titled "Executing Queries" in the ADO and SQL Server section of Books Online.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

Edited by - phillcart on 09/26/2003 12:35:52 AM

--------------------
Colt 45 - the original point and click interface

mirekg SSC Rookie Points: 45 More actions · Answer 4

mirekg

SSC Rookie

Points: 45

September 26, 2003 at 1:05 am

#475927

Thanks,

MG

MegaDBA Hall of Fame Points: 3518 More actions · Answer 5

As usual Phill, you are the man!!!

This was very handy!

Aurora01

Aurora