We have an SSIS 2012 package that supports our internal analytics reporting database by extracting data from an external database server.
Last night, for the first time, the package failed due to a database connection timeout error when attempting to connect to the external database.
It seems to me that it would make sense to enhance our SSIS package with some means of making multiple attempts to connect before failing completely.
For example, we would want the SSIS package to try 3 times, perhaps sleeping for 5 minutes before attempting a retry. If there are 3 failures to connect, then return an error for the package.
I've searched for references on this, but I am not finding anything; it seems that it should be an obvious feature, and I must not be looking in the right places.
Here are some details:
Using SSIS 2012 packages in a local SQL Server 2012 server.
Extracting data from a remote Oracle database
Using Microsoft Connector for Oracle by Attunity
The SSIS package is called from a Scheduled Job using SQL Server Agent
The SSIS package has very simple components that consist of a truncate table step followed by a Data Flow task.
The Data Flow task uses the Oracle Data Source component connected to an OLE DB Destination (our local SQL server instance/database).
There is 1 intermediary Derived Column component, that adds some calculated data to the destination.
The package has successfully run many times in our production environment, so I already know it's not due to development/visual studio vs. SSIS catalog in SQL Server, etc.
I have looked at the Connection Manager for my external Oracle database, and I've looked at the Oracle Data Source component, but I don't see anything that looks like it would support a retry.
Should I be creating some type of retry loop using the on-error functionality?
It seems a bit like overkill, perhaps there is some property within the Oracle Source Component that I need to put in the configuration somewhere?