DTS - UPDATE Record

[rajkum]

Hi,

REQUIREMENT :
1. Regularly update DATA FROM Database (DB1) to Database (DB2)
say FROM TABLE1 to TABLE2

2. IF row in TABLE1 is new INSERT in TABLE2
3. IF row in TABLE2 says the row has to be overwritten (SQL UPDATED) THEN
UPDATE the row.
(TABLE2 has a flag which says whether the row has to be UPDATED)


I would appreciate if anyone could help me with Requirement 3 using DTS. ( OTHER THAN DELETE / INSERT)




Thanks in advance,
RK

 

[rajkum]

I figured it out!!
Thanks!

 

[nocares]

Hi, I am new to DTS, and am trying to do the same thing you mentioned.

Would you mind sharing your technique? I haven't been able to figure it out yet.


Thanks in advance.

Richard M.

 

[btturner]

You may want to read up on BOL -- DTSTransformStat_xxxxxx options. Here is a sample which uses DTSTransformStat_SkipInsert

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

' Copy each source column to the destination column
Function Main()
If IsNumeric(DTSSource("Col020")) Then
DTSDestination("log_date") = DTSSource("Col001")
DTSDestination("log_time") = DTSSource("Col002")
If IsNumeric(DTSSource("Col021")) Then
DTSDestination("lw_group_id") = DTSSource("Col021")
Else
DTSDestination("lw_group_id") = 0
End If
DTSDestination("IISLog_Source") = "USA"
Else
Main = DTSTransformStat_SkipInsert
Exit Function
End If
Main = DTSTransformStat_OK
End Function

 

[rajkum]

Hi Devils,

I used Data Driven Query Task in DTS and used the "Query Type- Update" in Queries.

If you are interested I could post an example.

Thanks,
Rajkum.

 

[nocares]

I would love to see an example.

Thank you for your response.

Richard M.

 

[rajkum]

sorry for the dealyed response...


Table 1 :
[person_names]
--------
some_id
last_name
first_name
mid_name
name_eff_dt
---------


Table 2:
[person_demographics]
---------
ID
last_name
first_name
mid_name
sex
dob
---------

Let us try to Update last_name,first_name,mid_name in [person_demographics] with the value in [person_names].

Every time any part of the name changes. A new record with an effective date is entered in [person_names]


To have the latest last_name in [person_demographics] we have to Update the last_name, first_name and mid_name.

Let us do that by using DTS.

STEP 1:
Create TWO new connections.

STEP 2: FROM the TASK menu Select "Data Driven Query Task"

STEP 3: Right-click on the selected task and SELECT properties.

STEP 4:
a) Select the appropraite Connection.(The connection to the Database where Table 1 [person_names] reside)
b) Select "SOURCE" Tab and select the "SQL Query" radio.
c) Enter the following SQL statement
"SELECT some_id,last_name,first_name,mid_name FROM [person_names] A INNER JOIN (SELECT some_ID,max(name_eff_dt) FROM [person_names] GROUP BY some_ID) B ON A.some_ID = B.some_ID AND A.name_eff_dt = B.name_eff_dt"

[NOTE : Please verify the SQL statement above.
We need to get a unique row for each unique SOME_ID with the latest name_eff_dt.Assumint the SQL above does that..lets proceed..]


STEP 5: SELECT "BINDINGS" Tab / Select appropriate Connections /select Table 2 [person_demographics]

STEP 6 : Select "Transformations" Tab. Delete any exising Transformations and Click New. Select "ActiveXScript" and hit OK.

STEP 7: In the Trasformations Option Window Select "Source Columns" and click ">>" to move all the columns to the right.

STEP 8: In the Trasformations Option Window Select "Binding Columns" and move only the following columns :

last_name
first_name
mid_name
some_ID

STEP 9 : Select "GENERAL" Tab and Click on Properties.

STEP 10 : Replace
Main = DTSTransformstat_InsertQuery
BY
Main = DTSTransformStat_UpdateQuery

STEP 11 : OK

STEP 12 : In the "Data Driven Task Properties Window" Select the QUERIES Tab and select "Query Type" as Update

STEP 13: Enter the following SQL :

UPDATE last_name = ?, first_name=?, mid_name=? WHERE some_ID = ?

STEP 14 : Click "Parse / Show parameters"

STEP 15 : Select the appropriate parameter in the appropriate ORDER in the SQL statement above.

STEP 16 : OK

STEP 17 : Right Click on the "Data Driven Query Task" and Execute.


Thanks,
RajKum