SSIS Teradata data destination very slow

Connectivity
Fan

SSIS Teradata data destination very slow

Hi All

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.

Steve

4 REPLIES
Teradata Employee

Re: SSIS Teradata data destination very slow

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.

Fan

Re: SSIS Teradata data destination very slow

Hi Ibby

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.

Thanks again.

Steve

Teradata Employee

Re: SSIS Teradata data destination very slow

Teradata Employee

Re: SSIS Teradata data destination very slow

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.