SSIS can't catch exception of connection

Question

SSIS can't catch exception of connection

thang_ngo_2002

SSCrazy

Points: 2187
More actions
September 3, 2011 at 3:11 am

#148594

I have an alert system based on SQL Server
When I use SQL 2000, I monitor an Oracle system by SQL DTS, DTS contains 1 transform "select 1 from dual" to check Oracle connection, if transform fails then next step is drived to Alert task via "fail" condition
Now I upgrade to SQL 2008
I use same way in SSIS but when connection fails, SSIS can not move to next step (Alert task) with "fail" condition because SSIS validates connections first, then runs steps, if validate fails, it does not run steps
So can SSIS not catch exception of connection ?

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

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Why don't you use a Precedence Constraint to the alert task?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Post removed.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Martin Schoombee SSCoach Points: 19128 More actions · Answer 3

thang_ngo_2002 (9/3/2011)
I have an alert system based on SQL Server
When I use SQL 2000, I monitor an Oracle system by SQL DTS, DTS contains 1 transform "select 1 from dual" to check Oracle connection, if transform fails then next step is drived to Alert task via "fail" condition
Now I upgrade to SQL 2008
I use same way in SSIS but when connection fails, SSIS can not move to next step (Alert task) with "fail" condition because SSIS validates connections first, then runs steps, if validate fails, it does not run steps
So can SSIS not catch exception of connection ?

You can set the "DelayValidation" property of the connection. Then the connection will only be validated when the task that uses it executes. I think that will resolve your issue.

thang_ngo_2002 SSCrazy Points: 2187 More actions · Answer 4

Martin Schoombee (9/3/2011)
thang_ngo_2002 (9/3/2011)
I have an alert system based on SQL Server
When I use SQL 2000, I monitor an Oracle system by SQL DTS, DTS contains 1 transform "select 1 from dual" to check Oracle connection, if transform fails then next step is drived to Alert task via "fail" condition
Now I upgrade to SQL 2008
I use same way in SSIS but when connection fails, SSIS can not move to next step (Alert task) with "fail" condition because SSIS validates connections first, then runs steps, if validate fails, it does not run steps
So can SSIS not catch exception of connection ?
You can set the "DelayValidation" property of the connection. Then the connection will only be validated when the task that uses it executes. I think that will resolve your issue.

You are right, I've done. Thanks very much 🙂