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?
Best Answer
There is a way to setup the number of times to retry and to set the interval between each try inside the SQL Agent Job. Simply open the job and edit the job step. Click on advanced link. Once there, you can set the retry attemps and retry interval see below. You can also setup the package to restart from the point of failure. I have not done that personally but I am sure there is a blog that talks about it. This one might help. http://www.mssqltips.com/sqlservertip/1408/integration-services-checkpoints-to-restart-package-from-failure/