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

Highlighted
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.