Connecting to Teradata using DTS Package

Anil Golamari
Anil Golamari used Ask the Experts™
on
Hi,

I had to create a dts package which has to first delete data from a table in teradata server and then update that table with data in sql server. I am able to store the data from sql server to teradata. but I need to know is there a way to create a task like sql task where I can delete table data and then do on success to load teradata server.  Any sample code or some good example link will be very helpfull. Thanks in advance.

Thanks,
lucky.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
Hi lucky85

i am not sure i understood your question right, but any way see attached images, between every two tasks in DTS you can have on success or on failure.

and in your case you need to have an "Execute SQL task" and "Task to Copy from SQL to Teradata"

and from your Execute SQL task, you can have on success go to the other task.

you need to press Ctrl key and first click on the first task and then the second task and go to the menu of DTS and click workflow and chose your option


DTS-workflow.jpg
DTS-workflow-2-options.jpg

Author

Commented:
Hi Ammar,

It is possible to delete data from teradata server before I insert new data into it using Execute sql. Because my package has to do these steps:

1) Delete previous data from table on teradataserver
2) On success it has to transfer new data from sql to teradata.

I achived transferring data from sql to teradata but I am not able to figure out how can I delete previous data from teradata. Which task do I have to use to achieve this.

Thanks,
Lucky85
Personally, I would probably import the data into a staging table in the Teradata database before I deleted the data from the table.  (Of course, I'd start by truncating the staging table before importing the data into it. ;-)  That way, you can handle the TRUNCATE TABLE step and the data transfer (and, at the end, a TRUNCATE of the staging table) in a stored procedure in the Teradata database and do that within a transaction so that, worst case, you effectively don't change anything if there is a problem.  
In order to perform the TRUNCATE or DELETE statement, you will need to use an Execute SQL Task component.  
By the way, the reason for using TRUNCATE TABLE yourtablename rather than DELETE FROM yourtablename is that it is noticeably faster and has less impact on the transaction log.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial