Teradata destination ODBC connections failing in SSIS job
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?
Re: Teradata destination ODBC connections failing in SSIS job
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.