October 26, 2011 at 4:44 am
Hi
I am writing a package that attempts to connect to various databases to establish if they are available or not. I am doing this by issuing a simple select against the database in a an Execute SQL Task and handling the success or failure of the task as appropriate.
This works ok but if a database is not available then there is a connection manager error like this:
[Connection manager "XXXXX"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
this is fine and what I would expect but is always propagates up to a package level error, is there any way to prevent behaviour this so that the package as a whole reports success?
I have changed the MaximumErrorCount property of the package from 1 to 0 but still the package fails.
Thanks in advance.
Ben
October 26, 2011 at 6:52 am
Are you connecting to multiple servers, or just to multiple databases on one server?
If multiple databases on one server, rather than what you're doing, why not just select from sys.databases?
That will give you a list of the databases on the server, and their "state".
SELECT name, user_access, is_read_only, state
FROM sys.databases;
Those will tell you the whole story about the state of the database, including accessibility, online status, et al.
Much faster than running a query against each one.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2011 at 8:02 am
That's a good idea but I am connecting to multiple servers (and SSAS DBs).
Also, if I queried the sys.databases when the server was down then I would still get the connection manager error I described in my post.
Ben
October 26, 2011 at 8:16 am
Ping each server first.
Then, if up, check the state of the SQL service. You can do this through WMI.
Then check database statuses.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2011 at 8:32 am
Still I would need a connection manager with which to query the database state and if that server is down the connection manager will throw an error which will fail the package.
I really need some way of suppressing this error at the package level...
October 26, 2011 at 9:06 am
Have you tried setting the max errors to some crazy number like 1000000? Not sur eif that will give you what you are looking for.
CEWII
October 26, 2011 at 9:42 am
I had that property set to 0 which is supposed to mean 'unlimited', I changed it to a really high number but same result.
October 27, 2011 at 6:02 am
benlatham (10/26/2011)
Still I would need a connection manager with which to query the database state and if that server is down the connection manager will throw an error which will fail the package.I really need some way of suppressing this error at the package level...
Set the connection for Delayed Validation. It won't throw an error till you try to use it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 27, 2011 at 6:56 am
I think I was wrong to say that just having a connection manager referencing a server that was down would throw an error. It seems that that is only true if you try to use it (regardless of the DelayValidation property it apparently).
To do things property I think I'll have to follow you approach of pinging the server then assessing SQL service availability before using the connection manager. Never done things like that before so I'll have to learn how!
I was hoping I could just run a query and use a precedence constraint to take action in the event of a db being unavailable. This works but does mean the package completes returning a failure.
Thanks for your help.
Ben
October 28, 2011 at 6:27 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply