problem of update data by using DTS

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

Import/Export (DTS) and Replication (2000)

problem of update data by using DTS

Author

Topic

davidiwu
Starting Member

2 Posts

Posted - 2010-05-21 : 03:46:08

I have a flat txt file that will be imported into a table. this txt file is very well formatted to fit the destination table except has an extra column to indicate this row is either insert, delete or update the destination table's record. I am not very good at DTS, Can this kind of transformation be done by DTS? I am looking at the Data Driven Query Task, but can't find a way other than insert data to the destination table. Can someone help me?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-21 : 04:21:53

My way:
Import the data 1:1 to a staging table.
After that you can use a sql script task to make your deletes, updates and insert to the destination table.

No, you're never too old to Yak'n'Roll if you're too young to die.

davidiwu
Starting Member

2 Posts

Posted - 2010-05-21 : 04:31:34

Thanks webfred.
I found this code block that maybe helpful:
Function Main()
Select Case UCase(Trim(DTSSource("Col001")))
Case "UPDATE"
DTSDestination("ShipperID") = DTSSource("Col002")
DTSDestination("CompanyName") = DTSSource("Col003")
DTSDestination("Phone") = DTSSource("Col004")
Main = DTSTransformstat_UpdateQuery
Case "INSERT"
DTSDestination("ShipperID") = DTSSource("Col002")
DTSDestination("CompanyName") = DTSSource("Col003")
DTSDestination("Phone") = DTSSource("Col004")
Main = DTSTransformstat_InsertQuery
Case "DELETE"
DTSDestination("ShipperID") = DTSSource("Col002")
Main = DTSTransformstat_DeleteQuery
Case ELSE
Main = DTSTransformstat_UserQuery
End Select
End Function

My question is can we perform "delete" first by using the above code? the best way to adopt the flat txt file is delete all marked records first, and then perform 'update' and then 'insert'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-21 : 04:50:09

Yes, it looks like you can try to move the delete to the top.

Function Main()
Select Case UCase(Trim(DTSSource("Col001")))
Case "DELETE"
DTSDestination("ShipperID") = DTSSource("Col002")
Main = DTSTransformstat_DeleteQuery
Case "UPDATE"
DTSDestination("ShipperID") = DTSSource("Col002")
DTSDestination("CompanyName") = DTSSource("Col003")
DTSDestination("Phone") = DTSSource("Col004")
Main = DTSTransformstat_UpdateQuery
Case "INSERT"
DTSDestination("ShipperID") = DTSSource("Col002")
DTSDestination("CompanyName") = DTSSource("Col003")
DTSDestination("Phone") = DTSSource("Col004")
Main = DTSTransformstat_InsertQuery
Case ELSE
Main = DTSTransformstat_UserQuery
End Select
End Function

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources