DTS problem updating a source column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DTS problem updating a source column

Author

Topic

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2001-02-07 : 09:10:16

I am using DTS transform task to move specific fields into a holding DB. The following code happily moves the reqd rows but the problem is i want to then UPDATE the Upload_Status_Code field in the source table from either an I or a U to a C for the rows that were moved.
I have tried putting DTSSource("Upload_Status_Code") = "C" in the InsertSuccess phase but although the DTS package runs the relevant source rows are unchanged. I have thought about using a Data Driven Query task to run an UPDATE query to do it but am not sure how to do this exactly. The source and destination DBs are on different servers.

Please help

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

' Copy each source column to the destination column
Function Main()

Dim booJustDoIt

booJustDoIt = False

If DTSSource("Upload_Status_Code") = "I" And
DTSSource("FMG_Address_Status") = 2 And DTSSource("Active") = True Then
booJustDoIt = True
End If

If DTSSource("Upload_Status_Code") = "U" And
DTSSource("FMG_Address_Status") = 2 And DTSSource("Active") = True Then
booJustDoIt = True
End If

If DTSSource("Upload_Status_Code") = "U" And
DTSSource("FMG_Address_Status") = 2 And DTSSource("Active") = False Then
booJustDoIt = True
End If

If booJustDoIt = True Then
DTSDestination("FMG_Address_Code") =
DTSSource("FMG_Address_Code")
DTSDestination("Proteus_FMG_Code") =
DTSSource("Proteus_FMG_Code")
DTSDestination("HeadOffice") = DTSSource("HeadOffice")
DTSDestination("Telephone_Number") =
DTSSource("Telephone_Number")
DTSDestination("Fax_Number") = DTSSource("Fax_Number")
DTSDestination("Website") = DTSSource("Website")
DTSDestination("Floor/Building") =
DTSSource("Floor/Building")
DTSDestination("Street") = DTSSource("Street")
DTSDestination("City") = DTSSource("City")
DTSDestination("State/Province") =
DTSSource("State/Province")
DTSDestination("Country") = DTSSource("Country")
DTSDestination("Postal_Code") = DTSSource("Postal_Code")
DTSDestination("FMG_Address_Status") =
DTSSource("FMG_Address_Status")
DTSDestination("Last_Modified_By") =
DTSSource("Last_Modified_By")
DTSDestination("Last_Modified_Date") =
DTSSource("Last_Modified_Date")
DTSDestination("Upload_Status_Code") =
DTSSource("Upload_Status_Code")
DTSDestination("Active") = DTSSource("Active")
Main = DTSTransformStat_OK
Else
Main = DTSTransformStat_SkipRow
End If
End Function

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources