2

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?

Improve this question
1
  • Updated my SQL Server Agent Job step that runs the SSIS package to use Retry attempts set to 3, and Retry interval (minutes): to 5.
    – qxotk
    Apr 11, 2014 at 22:44

1 Answer 1

Reset to default
2

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/ enter image description here

Improve this answer
2
  • The Job Step Retry Attempts and Retry Interval are exactly what we need in this case, as the particular package run by the step is re-runnable regardless of which internal component fails.
    – qxotk
    Apr 11, 2014 at 22:36
  • 1
    Checkpoints are an interesting beast. If you use them, be certain to force a failure during execution to ensure they behave as expected when you resume.
    – billinkc
    Apr 14, 2014 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.