I'm an experienced SQL Server & data warehouse guy but am working with TD for the first time.
I'm trying to extract rows from a SQL server DB using SSIS & insert them into a TD table & I'm getting absolutely miserable performance (<1000rows/min).
The data's not pretty (very widedata set) but moving between SQL Servers in same geographical locations I get a reasonable performance (90mins to move the same 50million records).
I've been confused by the different Teradata drivers (.Net, ODBC & the Attunity drivers) and have struggled to configure some of them inorder to try to compare performance.
I'm currently using the ".Net Data Provider for Teradata". when I tried to use the Attunity Teradata data destination I got a TPT err which after googling is becuase I don't Teradata Parallel Transporter installed on the server where the package is running even though when I configure the Attunity connection object and I can "test connection" ok.
On that note it is possible to download TPT? I have found & installed Teradata Studio & cxan see that TPT is installed on some ofther servers but no the one I need it on.
Realise that I've been v. sketchy on technical details/confguration but not sure what'#s pertanent so please ask for details & I'll oblige.
Thanks in advance.
The performance problem is down to ODBC being a single row Insert operation when used as a data loader, so each row will result in a seperate Insert statement i.e. 50million inserts . To achieve high performance you need to use the batch load utilities like TPT. You can download the utilities from Downloads section of Developer Exchange - look for TTU.
Thanks for you're response - I'm currently downloading TTU (which now seems to be referred to as Teradata Studio Express?) and will wait to see if this fixes my issues.
Studio Express is a different product. Try this link http://downloads.teradata.com/download/tools/teradata-tools-and-utilities-windows-installation-packa...
The .NET Data Provider for Teradata supports batch update (see ADO.NET Batch Update http://msdn.microsoft.com/en-us/library/aadf8fk2.aspx); but most likely the SSIS "ADO.NET Destintation" component does not support ADO.NET Batch Update. That said, TPT + Attunity is the right solution for Bulk Loads.