advicce needed, which task to choose in DTS package

Hi guys, I need to bits of advice.

I get data from several tables in database A and export it into database B. In this instance both databases are SQL I use a DDQ task to do this with VB script to provide the logic to insert the record if it dosnt exist and update the record if it does. What I would like to know is if there is a more effcient task that can perform this function, something that dosnt require the vb script

The 2nd piece of advice I'd like to know is concerning getting data from an oracle database and importing it into a sql database through the use of csv files. This process very slow (up to 8 hours). Is there way of using some kind of DTS package to speed things up? Or is that not possible.

many thanks

SQL2005 Integration Services would be great for this scenario. You can add a lookup task and handle your control of flow all in the one package. If you're using SQL2K, could you perhaps dump all the data into a holding table and then run a simple INSERT statement to put in the new values?

If you're taking data from Oracle, you could just create a DTS/SSIS package to query Oracle direct and pump the data into your SQL Server. I'd have thought this would be the most efficient way.

HTH!

|||

Thanks for that. In the case of using a datapump in SQL2k, do yuo have an example of some tsql I could use to insert new records and update existing ones?

many thanks

|||

I think you'd need a DTS package with the following flow:

SQLConn1.SrcTable -> DDQ -> SQLConn2.HoldingTable -> T-SQL Task

Your T-SQL Task could be an SQL stored procedure which did something like the following:

Code Snippet

CREATE PROC AddRecords
AS

UPDATE DstTable
SET Col1 = Hld.Col1, Col2 = Hld.Col2

FROM DstTable Dst

INNER JOIN HoldingTable Hld

ON Dst.Col4 = Hld.Col4

INSERT INTO DstTable
SELECT *
FROM HoldingTable Hld

WHERE NOT EXISTS (SELECT * FROM DstTable Dst WHERE Dst.Col4 = Hld.Col4)

DELETE
FROM HoldingTable

Of course, this is quite simplified. You'll need to change your JOIN and EXISTS clause to include all the columns which define a record as being unique.

You may also want to break this stored procedure up into 3 T-SQL tasks which you could run after the DDQ.

HTH!

|||

Thanks for that.