Data from SQL Server to Teradata

[TDKID]

Hi,
I have my source data in SQL Server DB, I need to pump it to Teradata OLAP DB. First time I need to data for about 150 GB and my daily load will be 5GB a day.

Which loading utility can I use

-Extract data from SQL Server thru DTS into flat file and load using Teradata Utilities (Fastload,Mload)
-Extract and load data directly using DTS
-Go for some 3rd party like informatica


It would be more help if some one can explain me which one would be the most efficient process to go about.

-TIA

 

[BillDHS]

You can use a Teradata utility called OLE Access Module to go directly from SS to Teradata. It skips the export to flat file step and uses Fastload to load the table. The newer version also generates Multiload and BTEQ scripts.

I just ran an export job of a 17 mill row, 1.6 Gb table:
Fastexport to flatfile = 70 minutes.
OLE Access Mod, Teradata to SS = 120 minutes
DTS Teradata to SS = < 180 minutes.

 

[steverael]

OLE DB Access Module (aka OLELoad) in conjunction with FastLoad (for the initial load) will be the most performant. I have not tested in a long time, but I was able to load over 20GB per hour into Teradata from SQL Server. You should be able to at least get this on a dedicated fast ethernet connection.

For subsequent updates, either FastLoad to a staging table and perform an INSERT SELECT, or use MultiLoad.

Avoid DTS going into Teradata, as it inserts one row at a time.

 

[Smiles99]

TDKID,

As BillDHS and steverael previously pointed out, using the OleLoad utility found in the Teradata OLE DB Access Module is a very efficient way to load Teradata from Microsoft SQL Server. Since you mentioned DTS (Microsoft Data Transformation Services), I thought I'd point out that you can also perform a load with the OLE DB Access Module from DTS. This could be handy if you needed to perform some transformation/cleansing/etc. of the data prior to loading. In this case, the data flow will look something like:

Microsoft DTS
|
V
Teradata OLE DB Access Module
|
V
Teradata FastLoad
|
V
Teradata RDBMS

Here is a small experiment that you can use to verify that it is possible to load directly from DTS:

On a system having Microsoft SQL Server 2000 installed, click &quot;Start&quot;, then click &quot;Programs&quot;, then click &quot;Microsoft SQL Server&quot;, then click &quot;Import and Export Data&quot;. Follow the wizard steps to create a &quot;Microsoft OLE DB Provider for SQL Server&quot; to &quot;Microsoft OLE DB Provider for SQL Server&quot; package. On the &quot;Specify Table Copy or Query step&quot;, choose &quot;Copy table(s) and view(s) from the source database&quot;. On the &quot;Save, Schedule, and replicate package&quot; step, uncheck all of the check boxes in the &quot;When&quot; group box, check the &quot;Save DTS Package&quot; check box and the &quot;Structured Storage File&quot; radio button in the &quot;Save&quot; group box.
Start the &quot;SQL Server Enterprise Manager&quot; and find the &quot;Data Transformation Services&quot; icon. Right click it and choose &quot;Open package&quot; to open the package that you created with the &quot;Import and Export Data&quot; wizard. Right click the transform data task icon (it is an arrow) and click Workflow Properties. On the &quot;Options&quot; tab in the &quot;Execution&quot; group box set the &quot;DSO rowset provider&quot; check box. Also, copy down the ENTIRE &quot;Name&quot; of the transform data task. It is important the get the entire name. It may not all be displayed and you might have to click on it and then move the cursor all the way right and left over it to get the whole name.
Save the package.
Start OleLoad and for the &quot;Select a source&quot; group box, select the Microsoft OLE DB Provider for DTS Packages (&quot;DTSPackageDSO&quot entry. On the &quot;All&quot; tab of the &quot;Data Link Properties&quot; dialog box, set the value of the Provider String property to /Ffilename (where filename is the full pathname of your DTS package).
In the &quot;Enter the name of the desired table&quot; edit box, enter the name of the transform data task.

This is a very basic way of using this, but it at least shows that it works. Of course in many cases it is probably better to load the data to Teradata first and then perform the needed transformation/cleansing/etc.

Keep smiling!