locked
Teradata destination ODBC connections failing in SSIS job RRS feed

  • Question

  • Windows Server 2012 R2
    SQL Server Integration Services 2012
    Teradata 13.10.00.14, Driver 14.10.00.06

    We have a situation where ODBC connections to Teradata destination database keep getting errors from time to time, not every night though.
    Some nights regular load jobs execute successfully and sometimes they get errors "There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server." - one or more component failed validation.

    The faulty component is usually different, not the same every time. Package may also differ. There are 4 packages starting parallelly at the same time, each having 1 or 2 sequences of dataflows and sql tasks (about 60 dataflows and 40 sql tasks in total in those packages). Packages have been migrated from DTS to SSIS and this kind of error-situation has never occurred before.
    So we checked what happens on the Teradata side - many simultaneous sessions, validation sessions at first then TPT Load and ODBC sessions, but the number of concurrent open sessions never exceeds something like 30-40. And there is a parameter in Teradata indicating that max allowed sessions is 120.

    Two different kinds of connection managers used - Teradata Connection Manager, used in Attunity Teradata Destination component for TPT Loading and ODBC Connection manager to execute SQL Tasks and also for loading to Teradata destination tables in case of low number of records being transfered.
    TPT Loads take 14 sessions each, ODBC destination and validation sessions apparently 1 per validation/component.

    What could be the reason for this error? Where to look, what to check?

    Thanks for any advice!
    Monday, March 16, 2015 2:20 PM

Answers

  • It is now safe to say that the situation has been fixed, the job has been running successfully for 20 days now.

    Modifications that I did:

    1. set 'Delay Validation' to TRUE on every SQL task and Dataflow and package, just to be sure to have maximum effect of this option.

    2. put some scripts with delays (15 sec, 30 sec, 45 sec) into the main package before different precedence constraints, so that there would be a time gap between starting child packages on different flows, making sure all the validation would not occur in the same short time period.

    Monday, April 13, 2015 8:38 AM

All replies

  • My suggestion would be to first have some error handling mechanism in your package.

    To start with, You can create error tables and redirect the error output to error tables for logging purpose. This way your package would not fail and you will get detailed error output logged.

    Tuesday, March 17, 2015 3:44 AM
  • Sorry the information given doesnt help much to understand the root cause. Anyways something you could do is to set delay validation to true so that actual error you will get at runtime rather than a validation failed message as above. One more thing you can try is running packages one at a time and see if error occurs. Also see if multiple packages running in parallel are using same object.

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, March 17, 2015 5:35 AM
  • Thanks for the input.

    The error does move between the packages  and components. When ran one by one every package succeeds. No validation errors. But when all the packages (4 in parallel) start then at some point this error might occur and it can rise from one package one night and from some other package the next night. Also it doesn't occur every night, so sometimes nightly job succeeds.

    The same amount of dataflows and SQL tasks used to work well with MS DTS against the same warehouse database (Teradata).

    So I have a feeling, it has something to do with connection or session handling..

    Those are "data landing" dataflows, loading data from source tables into different destination tables in the staging area, not using same objects.

    Don't know, how to localize the problem.

    • Edited by pleino Tuesday, March 17, 2015 12:52 PM Additional comment to my reply
    Tuesday, March 17, 2015 12:48 PM
  • It is now safe to say that the situation has been fixed, the job has been running successfully for 20 days now.

    Modifications that I did:

    1. set 'Delay Validation' to TRUE on every SQL task and Dataflow and package, just to be sure to have maximum effect of this option.

    2. put some scripts with delays (15 sec, 30 sec, 45 sec) into the main package before different precedence constraints, so that there would be a time gap between starting child packages on different flows, making sure all the validation would not occur in the same short time period.

    Monday, April 13, 2015 8:38 AM