 | |
Re: failure message for entire package
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: failure message for entire package -
07-15-2004
, 02:04 PM
|
Is there a way to send a message if any part of a package fails. This way I'd only have to put one send mail task rather than have to put a send mail task on each step. Thanks, |
You can do some stuff outside of the package, perhaps by using the log
to SQL server option in the package and querying this afterwards for
example.
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org
#2
| |||
| |||
|
#3
| |||
| |||
|
|
That would normally be done as a post DTS process. You could fire the package in VB and look for errors there. If any were discovered you could send a mail from VB. Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com www.konesans.com - for all your consultancy needs |
#4
| |||
| |||
|
|
Do you mean querying the sysdtspackagelog and sysdtssteplog tables? Thanks, |
Whilst you can just run a query and order by date and such like, one tip
I like doing is to use the lineage variables. Turn them on, from on the
last tab of package properties. You can then use them as global
variables, and store the elsewhere, and or use them in queries to help
retrieve the exact run from the log tables.
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org
#5
| |||
| |||
|
|
In message <E3CDE22B-C4A9-4428-A880-5FB99757BF4C (AT) microsoft (DOT) com>, Dan D. DanD (AT) discussions (DOT) microsoft.com> writes Do you mean querying the sysdtspackagelog and sysdtssteplog tables? Thanks, Yep that's right. Whilst you can just run a query and order by date and such like, one tip I like doing is to use the lineage variables. Turn them on, from on the last tab of package properties. You can then use them as global variables, and store the elsewhere, and or use them in queries to help retrieve the exact run from the log tables. -- Darren Green (SQL Server MVP) DTS - http://www.sqldts.com PASS - the definitive, global community for SQL Server professionals http://www.sqlpass.org |
#6
| |||
| |||
|
|
How do I pick out only the most recent verson of the package in sysdtspackagelog? |
SELECT .................... FROM msdb.dbo.sysdtspackagelog T1 WHERE
LogDate = (SELECT MAX(LogDate) FROM msdb.dbo.sysdtspackagelog T2 WHERE T1.name =
T2.name
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
www.konesans.com - for all your consultancy needs
#7
| |||
| |||
|
|
Is a new lineagefull number generated every time the package runs? |
The variables are available as sources in a DataPump, and also as global
variables.
Allan's method my be simpler, but the lineage variables offer greater
precision and a degree of flexibility not available elsewhere. Choose
which works best for you.
For example my main ETL processes are controlled by a single master
package. The lineage variables are passed into all child packages and
used in explicit Exec SQL Task based logging calls to my own tables. I
can then combine the DTS logs with my own logs for example.
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org
#8
| |||
| |||
|
|
In message <846E4664-535E-447D-A48B-3371BC019C04 (AT) microsoft (DOT) com>, Dan D. DanD (AT) discussions (DOT) microsoft.com> writes Is a new lineagefull number generated every time the package runs? Yes. The variables are available as sources in a DataPump, and also as global variables. Allan's method my be simpler, but the lineage variables offer greater precision and a degree of flexibility not available elsewhere. Choose which works best for you. For example my main ETL processes are controlled by a single master package. The lineage variables are passed into all child packages and used in explicit Exec SQL Task based logging calls to my own tables. I can then combine the DTS logs with my own logs for example. -- Darren Green (SQL Server MVP) DTS - http://www.sqldts.com PASS - the definitive, global community for SQL Server professionals http://www.sqlpass.org |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.