Search code examples
ssisscriptingconnectionodbcoledb

Test if connection is up

I have a (rather large) list of connections. The list contains the name, type and connectionstring of each connection. The challenge is in the OLEDB connections and the ODBC connections. There are multiple kinds of OLEDB connections. (Sybase, Netezza, Oracle, MSSQL and a Net-Provider for Oledb for Microsoft Directory Services).

I want to check if these connections are up and send an email if one is not. Thats all. I found this code that helps me to check a connection. However, this uses a select * from emp to evoke a reaction of the tested OLEDEB server. Is there another command that is valid for every OLEDB that I can use? I was f.e. thinking of a select getdate() but that won't work for the Oracle and MDS. SO I am looking for a command that any OLEB will accept. And how to check if a ODBC connection is up?

Solution

  • Iterate through the list of connections by writing code or create a loop to iterate through each connection in your list. (use a container for example)

    SELECT 1

    This query doesn't depend on any specific table and should work with most OLEDB/ODBC providers.

    If a connection is not working, you can use the Send Mail Task in SSIS to send an email with the necessary details about the failed connection.