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 "Start", then click "Programs", then click "Microsoft SQL Server", then click "Import and Export Data". Follow the wizard steps to create a "Microsoft OLE DB Provider for SQL Server" to "Microsoft OLE DB Provider for SQL Server" package. On the "Specify Table Copy or Query step", choose "Copy table(s) and view(s) from the source database". On the "Save, Schedule, and replicate package" step, uncheck all of the check boxes in the "When" group box, check the "Save DTS Package" check box and the "Structured Storage File" radio button in the "Save" group box.
Start the "SQL Server Enterprise Manager" and find the "Data Transformation Services" icon. Right click it and choose "Open package" to open the package that you created with the "Import and Export Data" wizard. Right click the transform data task icon (it is an arrow) and click Workflow Properties. On the "Options" tab in the "Execution" group box set the "DSO rowset provider" check box. Also, copy down the ENTIRE "Name" 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 "Select a source" group box, select the Microsoft OLE DB Provider for DTS Packages ("DTSPackageDSO"
entry. On the "All" tab of the "Data Link Properties" dialog box, set the value of the Provider String property to /Ffilename (where filename is the full pathname of your DTS package).
In the "Enter the name of the desired table" 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!
