I'm trying to create DTS package that would update database_2 with data
from database_1.

I've added both connections.
I've added a Transform Data Task.

Database_1 Table "Trying"
invoiceid =
uniqueuserid =
invoice No =
InvoiceType

Database_2 Table "Completed"
invid =
psid =
InvNo =
InvType =

I only want to update invno and invtype where psid = uniqueuserid



1. I've added the data from the source database.
2. I've set the destination properties.
3. Transformations i selected a new activeX Script
selected my columns as needed. Viewed the properties.


'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()
DTSDestination("InvType") = DTSSource("Invoice Type")
DTSDestination("InvNo") = DTSSource("Invoice No")
Main = DTSTransformStat_OK
End Function

I guess i missed the plot somewhere.

My question is how/where do i specify that it should be an update and
not an insert.
I've been on it for 3 days so i'm now here asking for help.

Thank you

April

Are these on the same server?

If they are then you can can simply issue a series of T-SQL statements
to do the updating and make sure you qualify the name of the "other" db
in the syntax.

You could use the DDQ (Data Driven Query Task) to do this.

If this is a remote server then you could

Use the DDQ again

Use a Linked Server to make it easy to issue T-SQL commands against the
remote server

Pull the data from the remote server to the local server and issue T-SQL
statements


Using the datapump task as you are doing now you can still do this
although it may be painful. What you would basically do is have lookups
that would perform the action you needed based on something in your
data. You could fire a lookup which called a proc which took data from
the source and in the proc it worked out what type of action to perform.

You have a number of options as you can see.

Allan

"BestPractice" <aprilfool2005 (AT) gmail (DOT) com> wrote

Thanks for the feedback.
I got it to work which was quite simple in the end.
The databases are all on the same server.

But now i have a new question...
How do i add news items via DTS I've tried but I'm failing somewhere.

Database_1 Table "Trying"
invoiceid =
uniqueuserid =
invoice No =
InvoiceType

Database_2 Table "Completed"
invid =
psid =
InvNo =
InvType =


How do i distinguish what's new???

Thank you for your time.

April

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

You have to be able to identify a row in the source and a row in the
destination. If the identifier whatever that may be is in the source
but not in the destination then it is an insert. If it is already there
then it is eith to be left alone or an update. If it in the destination
and not in the source then it is either to be left alone or it is to be
deleted in the destination.

Allan

"Best Practice" <aprilfool2005 (AT) gmail (DOT) com> wrote

Thanks for that i can write that code in asp but when it comes to DTS
i'm a little off track.
So code examples would be helpful.

April