March 16, 2015 at 6:06 am
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.
Here is a picture of one example: http://goo.gl/Z1ofuQ
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!
March 16, 2015 at 5:18 pm
I would start by checking for locks in the destination. If you have multiple packages executing in parallel and writing to the same tables, you may be creating a deadlock situation.
Also set your "DelayValidation" properties to true. During the pre-validation phase, your package may throw an error which could be difficult to track down. Let it throw the error at the point of failure.
March 17, 2015 at 1:07 am
Hi, thanks for the reply!
Nope, those packages don't use the same destination tables, there are "data landing" dataflows which load data from source databases into different tables in staging area in Teradata.
I once tried DelayValidation on a test-dataflow to see how it then regulates validation connections, didn't see much of a difference (a portion of validation sessions are being done in the beginning and then by the sequence of dataflows), because actually only 1-2 steps execute parallelly in every package (so maximum of 8 steps for the whole job).
But I guess I should give this parameter another try, this time on the full load situation..
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply