SSIS package - connectivity check

Hi all,

I'm playing around a little bit with Rodney Landrum's [repository solution][1].

The solution works with dynamic connections to different data sources, e.g. the package loops through a table with a list of servers. Afterwards the package builds up a connection to each server and gathers some system relevant information. Now my little problem: How can I build up some kind of error handling if one of the listed servers is not available? By now the whole package will fail if more than 3 errors occur. So I'd like to check if the source is available. If not, log the non-reachable server in a separate table and go on with the remaining servers.

If you have any ideas how to achieve this please let me know.

Regards Dirk

[1]: http://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository
more ▼

asked May 22, 2012 at 09:23 AM in Default

DirkHondong gravatar image

1.3k 15 17 19

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

After a few hours working on other topics I got an idea on how to solve my problem. It's probably not the best way but it'll work for me: Before getting the actual data out of the different servers I will just use another loop container with a task to determine the servername and load it into a new table called "AvailableServers". This will be my recent "connect to" information. If the tasks fails, then I can write those faulty servers into a table called "NonAvailableServers".

It's just a rough idea but I'll figure it out
more ▼

answered May 22, 2012 at 01:19 PM

DirkHondong gravatar image

1.3k 15 17 19

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 22, 2012 at 09:23 AM

Seen: 1354 times

Last Updated: May 22, 2012 at 01:19 PM